Hi,
I have been trying to set up an automated restore process from prod to
backup server. First, I schedule the full database backup nightly,
transfer the backup file and restore it to the database on the backup
server. Meanwhile, I leave the database ready to accept transaction
log from the transaction log backup at noon daily.
And I had used different restore options to test out the transaction
that was being applied. And I couldn't get the transaction I am
supposed to. It seemed that none of the transaction has been restored.
Can somebody explain what I did wrong?
The following is the process I tried.
Test 1:
Step 1) Restore database from full backup with replace, standby option
RESTORE DATABASE TEST
FROM DISK = 'c:\restore\backupdb.bak'
WITH REPLACE,
STANDBY = 'e:\sql2000\undo\undo.ldf',
MOVE 'TESTData' TO 'E:\SQL2000\Data\TESTData.MDF',
MOVE 'TESTLog' TO 'D:\SQL2000\Logs\TESTLog.LDF',
MOVE 'TESTLog2' TO 'D:\SQL2000\Logs\TESTLog2.LDF',
MOVE 'TESTLog3' TO 'D:\SQL2000\Logs\TESTLog3.LDF'
WAITFOR DELAY '00:00:05'
GO
Step 2) Restore log and undo.ldf with standby option
RESTORE LOG TEST
FROM DISK = 'e:\restore\backuplog.bak'
WITH STANDBY = 'e:\sql2000\undo.ldf'
GO
Step 3) Restore log with recovery option and open database
RESTORE LOG TEST
FROM DISK='E:\RESTORE\BACKUPLOG.BAK' (the same backuplog.bak as Step
2)
WITH RECOVERY
GO
Test 2:
Step 1) Restore database from full backup with replace, standby option
RESTORE DATABASE TEST
FROM DISK = 'c:\restore\backupdb.bak'
WITH REPLACE,
STANDBY = 'e:\sql2000\undo\undo.ldf',
MOVE 'TESTData' TO 'E:\SQL2000\Data\TESTData.MDF',
MOVE 'TESTLog' TO 'D:\SQL2000\Logs\TESTLog.LDF',
MOVE 'TESTLog2' TO 'D:\SQL2000\Logs\TESTLog2.LDF',
MOVE 'TESTLog3' TO 'D:\SQL2000\Logs\TESTLog3.LDF'
WAITFOR DELAY '00:00:05'
GO
Step 2) Restore log and open database
RESTORE LOG TEST
FROM DISK = 'e:\restore\backuplog.bak'
WAITFOR DELAY '00:00:05'
GO
Test 3:
Step 1) Restore database from full backup with replace, norecovery
option
RESTORE DATABASE TEST
FROM DISK='E:\RESTORE\BACKUPdb.BAK'
WITH REPLACE,
MOVE 'TESTDATA' TO 'E:\SQL2000\DATA\TESTDATA.MDF',
MOVE 'TESTLOG' TO 'D:\SQL2000\LOGS\TESTLOG.LDF',
MOVE 'TESTLOG2' TO 'D:\SQL2000\LOGS\TESTLOG2.LDF',
MOVE 'TESTLOG3' TO 'D:\SQL2000\LOGS\TESTLOG3.LDF',
NORECOVERY
Step 2) Restore log with recovery option and open database
RESTORE LOG TEST
FROM DISK='E:\RESTORE\BACKUPLOG.BAK'
WITH RECOVERY
None of the transaction I backup during the day seems to be restored
and applied onto the database???? Am I missing something?
Thanks for any help!