Blog ala Vidar

What do you do if one of your customers calls and sais he’s just deleted something and he REALLY needs it back? We do this test about ones a month, to check that our backup-routines is working. For this example, we’ll say that it’s a Friday and the current time is 16:00. Your customer deleted what he needed 30 minutes ago (15:30). Here’s our setup:
We take full backup ones a week to C:\SQL\Backup\MyDatabase.bak
Differential is taken every night to C:\SQL\Backup\MyDatabase_Diff.bak
Transaction log backup is taken every day at 12:00 to C:\SQL\Backup\MyDatabase.trn

First thing to do is to take a backup of your transaction log:

BACKUP LOG [MyDatabase]
	 TO DISK = 'C:\SQL\Backup\MyDatabase_CustomLog.trn'
	 WITH NAME = N'MyDatabase-Transaction Log Backup'

When this is done, start restoring your last FULL backup of the database:

RESTORE DATABASE MyDatabase_BU
	 FROM DISK = 'C:\SQL\Backup\MyDatabase.bak'
	 WITH
	 MOVE 'MyDatabase_Data' TO 'C:\SQL\Backup\MyDatabase_BU.MDF',
	 MOVE 'MyDatabase_Log' TO 'C:\SQL\Backup\MyDatabase_BU.LDF',
	 NORECOVERY

We’re using “NORECOVERY” because we’re going to push more files on this database before making it available. In our environment we take full backup ones a week, with differentials every night, and transaction logs at 12:00. Here’s the code for adding the diff: NB! You only need the latest differential.

RESTORE DATABASE MyDatabase_BU
	 FROM DISK = 'C:\SQL\Backup\MyDatabase_Diff.bak'
	 WITH FILE = 1,
	 NORECOVERY

After adding the differential, we’ll add the transaction log from 12:00:

RESTORE LOG MyDatabase_BU
	 FROM DISK = 'C:\SQL\Backup\MyDatabase.trn'
	 WITH NORECOVERY

And when that’s done, we’ll add the transaction log we just created, but with a “point in time” attribute, STOPAT. Since the customer did the delete at 15:30, we’ll restore till 15:29. Notice that we’re using WITH RECOVERY on the last one!

RESTORE LOG MyDatabase_BU
	 FROM DISK = 'C:\SQL\Backup\MyDatabase_CustomLog.trn'
	 WITH RECOVERY, STOPAT = '2008-10-24 15:29'

After this is done, you can insert the records FROM MyDatabase_BU to MyDatabase, and your customer will be VERY happy 🙂