RESTORE - SQL Server Forums

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

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-28 : 07:56:43

Cool_DBA writes "I'm trying to use restore differential backup, I already did the full backup restore then trying to run the code below:
got an error msg: "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. RESTORE DATABASE is terminating abnormally. IN SQLSERVER 2000 IN WINDOWS 2003 SERVER.

RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff'
WITH MOVE 'LEON_Data' TO 'C:\DB\LEON_Data.MDF',
MOVE 'LEON_Log' TO 'C:\DB\LEON_Log.LDF'
NORECOVERY

------------------------------------------------------------------------
RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff' WITH FILE = 1,
NORECOVERY

------------------------------------------------------------------------

RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff'
WITH
REPLACE,
-- NORECOVERY, -- Use if more T/Logs to recover
RECOVERY, -- Use if no more T/Logs to recover
STATS = 10, --Show progress (every 10%)
MOVE 'LEON_Data' TO 'C:\DB\LEON_Data.MDF',
MOVE 'LEON_Log' TO 'C:\DB\LEON_Log.LDF'
GO"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-28 : 13:02:46

Well, did you specify WITH NORECOVERY when you restored the full backup?

Tara

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 07:31:14

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41804

Kristen

noelson
Starting Member

31 Posts

Posted - 2004-11-02 : 13:46:23

I did, tduggan. check my codes below:

RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_db_200410290115.BAK'
WITH NORECOVERY,
MOVE 'LEON_Data' TO 'C:\DB\LEON_Data.MDF',
MOVE 'LEON_Log' TO 'C:\DB\LEON_Log.LDF'

RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff'
WITH FILE = 2,
RECOVERY

still no luck...
error msg: Server: Msg 4038, Level 16, State 1, Line 7
Cannot find file ID 2 on device 'C:\DB\LEON_diff_backup.diff'.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.

Maybe this is help:
RESTORE HEADERONLY
FROM DISK = 'C:\DB\LEON_diff_backup.diff'

CheckpointLsn
1742000000841600009

DifferentialBaseLsn
1742000000258100003

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-02 : 13:49:20

You specified FILE=2, so does that mean you have more than one backup inside the diff file? Run RESTORE FILELISTONLY on your diff to see which files are in the backup set.

FILE=2 does not mean that you are on the second file now restoring. It means you want the second backup set inside the diff file to be used.

Also, you'll need the WITH MOVE option in the diff one too.

Tara

noelson
Starting Member

31 Posts

Posted - 2004-11-02 : 14:53:45

Thanks tduggan! I figure out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources