Find missing records in SQL

I recently had a client that lost data from some tables in SQL.  Since it was accidental, they knew some of the tables that were affected, but did not know for sure all of the tables affected.

They did, however, have a fairly recent backup.  All we needed to do was determine which records were present in the backup that were no longer present in the current database.

First I restored the backup to a new database.

Then I wrote this handy script to determine the differences.  Since it was a bit much to look at in SQL Management Studio, I used BCP to write the results to a text file that I could review (and keep as a backup of the changes made).

--set this to the production database
USE TWO

--To get xp_cmdshell to work:
sp_configure 'xp_cmdshell',1

go
reconfigure
go

--we need al these to generate the sql statements
declare @PROD varchar(5),
@TEST varchar(5),
@TABLE varchar(128),
@select varchar(2000),
@from varchar(2000),
@join varchar(2000),
@where varchar(2000),
@statement varchar(2000),
@col varchar(255),
@colnum int,
@order varchar(2000)

--this controls which two databases we are checking
select @PROD='TWO', @TEST='TEST'
exec master..xp_cmdshell 'del C:\temp.txt'
exec master..xp_cmdshell 'del C:\results.txt'

--this controls which tables we compare.  The following compares all tables that start with UPR
--not advisable to remove the like restriction unless you have a LOT of memory
declare c_tables cursor for
select name from sysobjects where name like 'UPR%' and type='U'

open c_tables
fetch next from c_tables into @TABLE
while @@FETCH_STATUS=0
begin

--here is how we get all the primary key columns for a table
declare c_col cursor for
select COLUMN_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
ON a. CONSTRAINT_NAME=b.CONSTRAINT_NAME
where b.CONSTRAINT_TYPE='PRIMARY KEY'
AND a.TABLE_NAME =@TABLE

--start building the statements
select @select='select ''' + @TABLE + ''', '
select @from=' FROM ' + @TEST + '..' + @TABLE + ' A ' + ' FULL OUTER JOIN ' + @PROD + '..' + @TABLE + ' B '
select @join=' ON '
select @where=' WHERE '
select @order=' ORDER BY '

open c_col
fetch next from c_col into @col
select @colnum=1
while @@FETCH_STATUS=0
begin
IF @colnum=1
BEGIN

--put the column names in the statements
select @select=@select + 'ISNULL(CONVERT(VARCHAR(2000),A.' + @col + '),' + '''MISSING''' + ') AS TEST,ISNULL(CONVERT(VARCHAR(2000),B.' + @col + '),' + '''MISSING''' + ') AS PROD'
select @join=@join + ' A.' + @col + '=' + 'B.' + @col
select @where=@where + 'A.' + @col + ' IS NULL OR B.' + @col + ' IS NULL '
select @order=@order + 'A.' + @col + ',B.' + @col

END
ELSE
BEGIN

--if there is more than one column in the primary key, handle that
select @join=@join + 'AND A.' + @col + '=' + 'B.' + @col
select @where=@where + 'OR A.' + @col + ' IS NULL OR B.' + @col + ' IS NULL '

END

select @colnum=@colnum+1
fetch next from c_col into @col

end

close c_col
deallocate c_col

--now put the statement together and execute in memory to see if we get any records back
select @statement=@select + @from + @join + @where
exec( @statement)
if @@ERROR=0
BEGIN

--since we know we have records, add the bcp commands to the statement to write the results to temp file
select @statement='bcp "' + @select + @from + @join + @where + @order + '" queryout "C:\temp.txt" -c -T'
exec master..xp_cmdshell @statement

--now append the temp file to the results file
exec master..xp_cmdshell 'type C:\temp.txt >> C:\results.txt'

--append a blank line for readability

exec master..xp_cmdshell 'echo. >> C:\results.txt'

--get rid of the temp file
exec master..xp_cmdshell 'del C:\temp.txt'

END
ELSE
BEGIN

--we have no records that are different, write a line to the results file anyway so we know we checked the table
select @statement='echo 0 differences found for table: ' + @TABLE + ' >> C:\results.txt'
exec master..xp_cmdshell @statement
exec master..xp_cmdshell 'echo. >> C:\results.txt'

END

fetch next from c_tables into @TABLE

end

close c_tables
deallocate c_tables
go


Did this help you?  If so, please leave a comment!