Blog ala Vidar

SQL, AppFrame and other cool technologies

Restore to point-in-time

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 🙂

Comments are closed.

<span>%d</span> bloggers like this: