November 3, 2005 at 2:40 pm
Hi,
I want to restore a database (TestData) as it was at 10 AM. I have the full backup and transactional log backup. The database is in full recovery mode.
1.) TestData.BAK
2.) TestData_0900.TRN
3.) TestData_1000.TRN
4.) TestData_1100.TRN
5.) TestData_1200.TRN
I did the following
restore database TestData
from disk = 'C:\TestData.BAK'
with move 'TestData_Data' to 'D:\data\TestData_Data.mdf',
move 'TestData_Log' to 'D:\data\TestData_Log.ldf'
How do I go by after this point?
Thank you for your help.
November 3, 2005 at 2:53 pm
You have to use the NORECOVERY syntax while doing restore to a point in time.
restore database TestData
from disk = 'C:\TestData.BAK'
with move 'TestData_Data' to 'D:\data\TestData_Data.mdf',
move 'TestData_Log' to 'D:\data\TestData_Log.ldf', NORECOVERY
RESTORE LOG TestData
FROM DISK ='C:\TestData_0900.TRN'
WITH NORECOVERY
RESTORE LOG TestData
FROM DISK ='C:\TestData_1000.TRN'
WITH RECOVERY -- For the last transaction log
November 3, 2005 at 2:58 pm
You want to restore the full backup with NORECOVERY, then restore each of the log backups stopping at 10:00 AM.
Something like this:
restore database TestData
from disk = 'C:\TestData.BAK'
with NORECOVERY,
with move 'TestData_Data' to 'D:\data\TestData_Data.mdf',
move 'TestData_Log' to 'D:\data\TestData_Log.ldf'
restore log TestData
from disk = 'C:\TestData_0900.TRN'
with NORECOVERY
restore log TestData
from disk = 'C:\TestData_1000.TRN'
with RECOVERY, STOPAT 'Nov 3, 2005 10:00 AM'
Greg
November 3, 2005 at 3:00 pm
Thank you guys.
November 4, 2005 at 7:51 am
BTW- here is a neat trick to make sure you don't restore data past your stopat time.
Use the STOPAT option in ALL of the log restores. There is nothing that says you can't use it on each log restore. If the criteria isn't met, then it keeps going until the criteria is met.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply