Restore full database and transaction dumps

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore full database and transaction dumps

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-21 : 06:35:58

Jerry Robles de Medina writes "Hi,
First of all great site and information you guys have.
I have surely already learned a lot about SQL 2000.Still I have been struggling with a problem the past couple of days and I hope you guys can help me out here.

I have a MS SQL 2000 server sp1 running on windows 2000 sp2 server.
Every day a full backup is being made creating a .dat file of about 1 gig (I know it is small for you guys) and every hour, from 07:00 till 19:00, a transaction dump is being done creating small .dat files.
I am trying to create a script to do a restore of the full backup file and the transaction dumps in case something bad happens and I am trying it out on another SQL server we have, with the same specs and config as the live server.
The method I am using is this, i try to restore the full backup from last nights overnight and then, if it is 10:00 Am, the transaction dumps from 07:00 till 10:00am.Then everything shoiuld be ok.The way it goes now, is that it restores the full backup but not the transaction dumps .But i am getting an error with the script and it is killing me.PLease help.Script and results included.
----------------------------------------------------------------
Script:

use master

print 'Database Reloading started at:'
select GETDATE()

load database pdgodo from aftov with recovery

print 'Transaction Reloading started at:'
select GETDATE()

WAITFOR DELAY '00:00:50'

restore LOG pdgodo from trandump01

restore LOG pdgodo from trandump02

restore LOG pdgodo from trandump03

print 'Database Reload completed at:'
select GETDATE()

RESULT:

Database Reloading started at:

(1 row(s) affected)

Processed 134440 pages for database 'pdgodo', file 'GodoProdData' on file 1.
Processed 1 pages for database 'pdgodo', file 'GodoProdLog' on file 1.
RESTORE DATABASE successfully processed 134441 pages in 139.948 seconds (7.869 MB/sec).
Transaction Reloading started at:

(1 row(s) affected)

Server: Msg 4306, Level 16, State 1, Line 16
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 16
RESTORE LOG is terminating abnormally.
Server: Msg 4306, Level 16, State 1, Line 18
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 18
RESTORE LOG is terminating abnormally.
Server: Msg 4306, Level 16, State 1, Line 20
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 20
RESTORE LOG is terminating abnormally.
Database Reload completed at:

(1 row(s) affected)
-----------------------------------------------------------------
If I do with norecovery i get another error, that says that it is not the right LNS or something

Thanks for the time,

Jerry"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-21 : 13:10:44

You say that you are using SQL 2000 but your restore commands are the old 6.5 way and the default extension for backups is now .bak. Anyway, the problem is that when you do a restore and want to load additional transaction logs you have to specify WITH NORECOVERY each time except for the last transaction log which you will need WITH RECOVERY.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources