By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

I can't restore the transaction I needed! Help!

P: n/a
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!
Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.