Saturday 13 May 2006

TSQL : Restoring a database to a point in time

Sql to use once you have identified which backup files need to be restored...
-- restore last full backup
-- use NORECOVERY to state you want to add further backup files

RESTORE DATABASE Sales
FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060423180002_Wednesday_MS.bak'
WITH NORECOVERY

-- add last differential backup
-- use NORECOVERY to state you want to add further backup files

RESTORE DATABASE Sales
FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425010002_Friday.diff'
WITH NORECOVERY
go

-- apply transaction log backups , up until the point you wish to restore to
-- use NORECOVERY to state you want to add further backup files
-- on the final restore, omit WITH NORECOVERY or specify WITH RECOVERY so the db is readable.

RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425060000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425070000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425080000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425090000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425100000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425110000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425120000_Friday_MS.trn'
go

No comments: