Restoring to a named log mark with Red Gate

The process of restoring a SQL database with Red Gate SQL Backup is usually straightforward. Open the interface, select the database and restore. The interface allows restored to a point in time, but there is no option to restore to a named mark in the log.

To restore a database to a named log mark, the database must be in full recovery. A valid full backup must exist. Transaction log backups must exist from the time of the full backup to the mark.

The restore to a named mark using Red Gate, follow the following steps.

Step 1 – if necessary, check the log mark. To do so, run the following SQL:

select * from msdb.dbo.logmarkhistory;

Step 2 – Generate TSQL script to restore to the appropriate mark. The easiest way to do this is to run the Red Gate SQL Backup interface as if you are going to restore to a point in time. You should know the time that the log mark was taken (see the mark_time column on the msdb table logmarkhistory). Run the restore wizard to the final step, and select the script tab. Select the script and copy it.

Step 3 – Now paste the script into SQL Server Management Studio. The script will look something like this:

EXECUTE master..sqlbackup ‘-SQL
“RESTORE DATABASE [FSDB]
FROM DISK = ”C:\\SQLServer\FULL_(local)_FSDB_20120905_122243.sqb”
WITH NORECOVERY, REPLACE”‘

EXECUTE master..sqlbackup ‘-SQL “RESTORE LOG [FSDB]
FROM DISK = ”C:\SQLServer\LOG_(local)_FSDB_20120905_122947.sqb”
WITH RECOVERY, STOPAT=”2010-08-26 11:50:00.000”, ORPHAN_CHECK”‘

All that you now need to do is edit the script to replace the ‘STOPAT=’ with STOPATMARK= in the second statement as follows:

EXECUTE master..sqlbackup ‘-SQL
“RESTORE DATABASE [FSDB]
FROM DISK = ”C:\\SQLServer\FULL_(local)_FSDB_20120905_122243.sqb”
WITH NORECOVERY, REPLACE”‘

EXECUTE master..sqlbackup ‘-SQL “RESTORE LOG [FSDB]
FROM DISK = ”C:\SQLServer\LOG_(local)_FSDB_20120905_122947.sqb”
WITH RECOVERY, STOPATMARK = ”Named Transaction T1”, ORPHAN_CHECK”‘

Not too difficult, but if this is a production environment make sure that you practice this restore and script it. Keep your script somewhere where it will be available if you are in a disaster.