Restore database from transaction log
-
Hello Folks,
I am trying to restore a database with data from most recent transaction logs.
Complete backup is done at night. Transaction log backup is done every hour.
Lets say I want to restore the database that was corrupted after 11:00 AM, how do I do that.
I can restore the database from the previous night backup and I have transactional log, can you guys point of some references on how to build the database like how it was at 11:00 AM.
Thank you for your help.
-
Restore your database from the full backup using the NO RECOVERY clause. Then restore EACH transaction log backup since the full backup up to the time in question (11:00am), also with the NO RECOVERY clause. Finally restore your transaction log backup that contains the 11:00am time frame using a WITH STOPAT '2005/03/07 11:00am' and RECOVERY. See RESTORE in Books Online for exact syntax.
Steve
-
Hi,
Here goes a sample code
RESTORE DATABASE Train_test
FROM Training_1 -- From Device file
WITH NORECOVERY
GO
-- Transaction log is backed up on a single device file Train_test_log
RESTORE LOG Train_test
FROM Train_test_log
WITH FILE = 1, NORECOVERY
GO
RESTORE LOG Train_test
FROM Train_test_log
WITH FILE = 2, NORECOVERY
RESTORE LOG Train_test
FROM Train_test_log
WITH FILE = 3, NORECOVERY
GO
RESTORE LOG Train_test
FROM Train_test_log
WITH FILE = 4 , STOPAT = '2005/03/07 11:00 am' , RECOVERY
GO
If you have copied to a disk file use the syntax
Disk ='D:\MSSQL\Backup\test.mdf' etc in the FROM clause (insted of device file)
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply