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\bac kupdb.bak'
WITH REPLACE,
STANDBY = 'e:\sql2000\und o\undo.ldf',
MOVE 'TESTData' TO 'E:\SQL2000\Dat a\TESTData.MDF' ,
MOVE 'TESTLog' TO 'D:\SQL2000\Log s\TESTLog.LDF',
MOVE 'TESTLog2' TO 'D:\SQL2000\Log s\TESTLog2.LDF' ,
MOVE 'TESTLog3' TO 'D:\SQL2000\Log s\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\bac kuplog.bak'
WITH STANDBY = 'e:\sql2000\und o.ldf'
GO
Step 3) Restore log with recovery option and open database
RESTORE LOG TEST
FROM DISK='E:\RESTOR E\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\bac kupdb.bak'
WITH REPLACE,
STANDBY = 'e:\sql2000\und o\undo.ldf',
MOVE 'TESTData' TO 'E:\SQL2000\Dat a\TESTData.MDF' ,
MOVE 'TESTLog' TO 'D:\SQL2000\Log s\TESTLog.LDF',
MOVE 'TESTLog2' TO 'D:\SQL2000\Log s\TESTLog2.LDF' ,
MOVE 'TESTLog3' TO 'D:\SQL2000\Log s\TESTLog3.LDF'
WAITFOR DELAY '00:00:05'
GO
Step 2) Restore log and open database
RESTORE LOG TEST
FROM DISK = 'e:\restore\bac kuplog.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:\RESTOR E\BACKUPdb.BAK'
WITH REPLACE,
MOVE 'TESTDATA' TO 'E:\SQL2000\DAT A\TESTDATA.MDF' ,
MOVE 'TESTLOG' TO 'D:\SQL2000\LOG S\TESTLOG.LDF',
MOVE 'TESTLOG2' TO 'D:\SQL2000\LOG S\TESTLOG2.LDF' ,
MOVE 'TESTLOG3' TO 'D:\SQL2000\LOG S\TESTLOG3.LDF' ,
NORECOVERY
Step 2) Restore log with recovery option and open database
RESTORE LOG TEST
FROM DISK='E:\RESTOR E\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!
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\bac kupdb.bak'
WITH REPLACE,
STANDBY = 'e:\sql2000\und o\undo.ldf',
MOVE 'TESTData' TO 'E:\SQL2000\Dat a\TESTData.MDF' ,
MOVE 'TESTLog' TO 'D:\SQL2000\Log s\TESTLog.LDF',
MOVE 'TESTLog2' TO 'D:\SQL2000\Log s\TESTLog2.LDF' ,
MOVE 'TESTLog3' TO 'D:\SQL2000\Log s\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\bac kuplog.bak'
WITH STANDBY = 'e:\sql2000\und o.ldf'
GO
Step 3) Restore log with recovery option and open database
RESTORE LOG TEST
FROM DISK='E:\RESTOR E\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\bac kupdb.bak'
WITH REPLACE,
STANDBY = 'e:\sql2000\und o\undo.ldf',
MOVE 'TESTData' TO 'E:\SQL2000\Dat a\TESTData.MDF' ,
MOVE 'TESTLog' TO 'D:\SQL2000\Log s\TESTLog.LDF',
MOVE 'TESTLog2' TO 'D:\SQL2000\Log s\TESTLog2.LDF' ,
MOVE 'TESTLog3' TO 'D:\SQL2000\Log s\TESTLog3.LDF'
WAITFOR DELAY '00:00:05'
GO
Step 2) Restore log and open database
RESTORE LOG TEST
FROM DISK = 'e:\restore\bac kuplog.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:\RESTOR E\BACKUPdb.BAK'
WITH REPLACE,
MOVE 'TESTDATA' TO 'E:\SQL2000\DAT A\TESTDATA.MDF' ,
MOVE 'TESTLOG' TO 'D:\SQL2000\LOG S\TESTLOG.LDF',
MOVE 'TESTLOG2' TO 'D:\SQL2000\LOG S\TESTLOG2.LDF' ,
MOVE 'TESTLOG3' TO 'D:\SQL2000\LOG S\TESTLOG3.LDF' ,
NORECOVERY
Step 2) Restore log with recovery option and open database
RESTORE LOG TEST
FROM DISK='E:\RESTOR E\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!