Debug problematic query from transaction log

SQL Server Administration
1

Hi everyone -

I'm trying to debug a problem that occurred in our application that involved a query that ran causing some unexpected updates.

The event occurred about a week ago. We create full backups (.bak) daily along with hourly backups of transactions (.trn).

My idea was to do a RESTORE to a temp db of the *.bak and *.trn up until the hour the event occurred and then somehow view all updates or inserts in the next *.trn file somehow.

Is there a way to do this? Is there any way to see that? Any suggestions would be really appreciated!

2

I would restore, as you say, to a new, temp, database. I would use STANDBY - which will make the TEMP database available for query AND allow you to "restore some more" (without having to start again).

I would use STOPAT on the final TLog restore to stop at a suitable time before the problem occurred, and I would then repeat the final Restore, with a slightly later STOPAT date (e.g. in one minute increments) to find when the problem occurred.

RESTORE DATABASE MyTempDB
	FROM DISK = 'G:\path\MyOriginalDB_yyyymmdd_hhmmss_Full.BAK'
	WITH
		REPLACE,
		STANDBY = 'E:\MSSQL\Data\MyTempDB.STB',
		STATS = 10,	-- Show progress (every 10%)
--NOTE: ***** Need to deduce the actual original database's logical names *****
-- e.g. RESTORE FILELISTONLY FROM DISK = 'G:\path\MyOriginalDB_yyyymmdd_hhmmss_Full.BAK'
	MOVE 'MyOriginalDB_data' TO 'E:\MSSQL\Data\MyTempDB.mdf', 
	MOVE 'MyOriginalDB_log' TO 'F:\MSSQL\Log\MyTempDB.ldf'
GO

-- Optionally restore a, later, DIFF backup

-- Then repeat this restore for each TLog backup, in chronological order:
RESTORE LOG MyTempDB
	FROM DISK = 'G:\path\MyOriginalDB_yyyymmdd_hhmmss_Trans.BAK'
	WITH
		STATS = 10,	-- Show progress (every 10%)
		STANDBY = 'E:\MSSQL\Data\MyTempDB.STB'
-- On the LAST TLog restore use:
--		, STOPAT = '19991231 23:59:59.999'
GO

You cannot restore to an earlier STOPAT time, so you might want to take a FULL backup after the first / earliest "useful" :slight_smile: STOPAT time so you can restore to that, and roll forwards, if you need to try-again. (Although I've never tried that to know if it is possible ... )