Restore to different database from differential

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 2005 Forums

SQL Server Administration (2005)

Restore to different database from differential

Author

Topic

Ben Taylor
Starting Member

6 Posts

Posted - 2009-09-02 : 10:02:39

Hello,
I am having trouble restoring from a differential backup to a different database.
I am running:

1> restore database MyDatabase from disk='c:\temp\MyDatabase.bak' with replace, norecovery, file=1, move 'MyDatabase' to 'c:\MyDatabases\MyDatabase.mdf', move 'MyDatabase_log' to 'c:\MyDatabases\MyDatabaselog.ldf'

to which I get:

Processed 584 pages for database 'MyDatabase', file 'MyDatabase' on file 1.
Processed 2 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
RESTORE DATABASE successfully processed 586 pages in 0.345 seconds (13.899 MB/se
c).

but then I run:


1> restore database MyDatabase from disk='c:\temp\MyDatabase.bak' with recovery, file=46

(there are 46 backup sets in c:\temp\MyDatabase.bak, the first is a full and all the others are differential)

anyway it then gives me:
Msg 3154, Level 16, State 4, Server BEN, Line 1
The backup set holds a backup of a database other than the existing 'MyDatabase'
database.
Msg 3013, Level 16, State 1, Server BEN, Line 1
RESTORE DATABASE is terminating abnormally.

Can anyone possibly identify how to get it to work please?

I'm not sure whether I should be using REPLACE and MOVE options when I'm recovering the differential backup on top of the full or not - but I've tried it with and without and every combination seems to produce the same error.

The backup was made on a different machine (but with the same version of SQL Server - i.e. 2005), is that a problem?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-09-02 : 10:31:33

Which is your differential backup file? Restore full backup in norecovery and latest differential backup with recovery. you don't need to restore all differentials.

Ben Taylor
Starting Member

6 Posts

Posted - 2009-09-02 : 10:47:48

quote:
Originally posted by sodeep

Which is your differential backup file? Restore full backup in norecovery and latest differential backup with recovery. you don't need to restore all differentials.

MyDatabase.bak holds the full backup and the differentials.
I am not trying to restore all the differentials, I am only trying to restore the full backup (which works) and the latest differential (which doesn't.

scuzymoto
Starting Member

17 Posts

Posted - 2009-09-02 : 17:54:59

I personally have not worked with backup sets from a single file but these are the thoughts that came to mind.

Have you verified the placement of your backups within the file? You can do a headeronly restore to see the header file and make sure you are pointing to the right place for the most recent diff.

RESTORE HEADERONLY FROM DISK='c:\temp\MyDatabase.bak'

While looking at the header make sure the header doesn't contain any full backup's that you aren't aware of.

If that doesn't work you may have a bad diff meaning you'd have to start working backwards to find a good one. There may have been a manual backup without the 'copy only' setting that broke your chain.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-02 : 19:00:31

You need to specify the WITH MOVE options in your second RESTORE command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Ben Taylor
Starting Member

6 Posts

Posted - 2009-09-03 : 05:05:55

quote:
Originally posted by tkizer

You need to specify the WITH MOVE options in your second RESTORE command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

It still does it with that:

I run:

restore database MyDatabase_backup from disk='c:\temp\MyDatabase.bak' with file=1, norecovery, move 'MyDatabase' to 'c:\MyDatabases\MyDatabase.mdf', move 'MyDatabase_log' to 'c:\MyDatabases\MyDatabase_log.ldf'
restore database MyDatabase_backup from disk='c:\temp\MyDatabase.bak' with file=46, recovery, move 'MyDatabase' to 'c:\MyDatabases\MyDatabase.mdf', move 'MyDatabase_log' to 'c:\MyDatabases\MyDatabase_log.ldf'

and it gives me

Processed 584 pages for database 'MyDatabase_backup', file 'MyDatabase' on file 1.
Processed 2 pages for database 'MyDatabase_backup', file 'MyDatabase_log' on file
1.
RESTORE DATABASE successfully processed 586 pages in 0.340 seconds (14.104 MB/se
c).
Msg 3154, Level 16, State 4, Server BEN, Line 2
The backup set holds a backup of a database other than the existing 'MyDatabase_backup' database.
Msg 3013, Level 16, State 1, Server BEN, Line 2
RESTORE DATABASE is terminating abnormally.

Ben Taylor
Starting Member

6 Posts

Posted - 2009-09-03 : 06:13:04

ah - I've identified what the problem is with this, but I would still appreciate advice on how to solve it...

What is happening is that the differential backup is backing up all changes to the database since the LAST full backup, which may or may NOT be in the file which my scheduled backups are being backed up to.

For instance, my scheduled backup procedure does a full backup on Monday, and differentials on tuesday, wednesday and thursday. Fine.
However, on thursday, someone wants to copy the database to another server, so they do this by taking a full backup to a temporary file (which may then get deleted or lost) which they then restore to their new server.
However, my friday's, saturday's and sunday's differential backups to my scheduled backup file are only backing up the changes since the database was copied on thursday, so I can only get back to the start of thursday - unless I can find the backup file that the person used to copy the database on thursday - which I may very well not be able to.
Looking at the BackupSetGUID and DifferentialBaseGUID columns in the data retrieved from restore headeronly has confirmed this to be the cause of the problem I am experiencing, and I can reproduce it with this in mind.
Is there any way of saying, when I do a differential backup, "i want this differential backup to represent the changes since THIS full backup in THIS file", rather than the LAST one that was taken, wherever that may be?

Thanks
Ben

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-03 : 06:27:09

You can use the COPY_ONLY option in the BACKUP statement, to create a backup that is outside of your normal backup chain (i.e. temporary backups).
Read about it here: http://msdn.microsoft.com/en-us/library/ms191495(SQL.90).aspx

There is no way of specifing a FULL backup as the starting point for a differential backup.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources