restore failed - 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

General SQL Server Forums

New to SQL Server Administration

restore failed

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-21 : 23:03:15

I want to test my backup, but failed.


alter database mtsnew set recovery  FULL
--back up
backup database mtsnew to disk  = 'C:\mssql2008\backup\mts10-21-2011.bak'
backup database mtsnew to disk  = 'C:\mssql2008\backup\mts10-21-2011.df1'  with init,differential
backup log mtsnew to disk ='C:\mssql2008\backup\mts10-21-2011.log'

-- restore
restore database mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.bak' with NORECOVERY
restore database mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.df1'with NORECOVERY
restore database mtsnew with RECOVERY

error message is


Msg 3136, Level 16, State 1, Line 9
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.

and how to restore using log file ?

Thanks

Kristen
Test

22859 Posts

Posted - 2011-10-22 : 06:45:49

Does RESTORE command for FULL and DIFF need to be in a separate batch perhaps?


RESTORE HEADERONLY FROM DISK = 'x:\Mypath\MyBackupFilename.BAK'

may tell us something about each of your backup files that is helpful in seeing why the DIFF cannot be restored after the FULL (because your Backup and Restore commands look fine to me)

"how to restore using log file ?"


restore database mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.bak' with NORECOVERY
restore database mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.df1' with NORECOVERY
restore LOG mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.log' with NORECOVERY
restore database mtsnew with RECOVERY

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-22 : 11:53:13

Looks like the backup has problem, if I change to this , it will work.


backup database mtsnew to disk  = 'C:\mssql2008\backup\mts10-21-2011.bak' with INIT

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-22 : 12:41:56

No, probably what you have is multiple backups appended in the same file. When you restore, if you don't specify FILE= and there are multiple backups in the file, SQL will restore the first one, and that was not the one the diff backup belonged to.

p.s. That's why Kristen was asking for RESTORE HEADERONLY to see exactly what backups were within the file.

--
Gail Shaw
SQL Server MVP

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-22 : 14:50:08

if I change to this, use 'HEADERONLY', it will work. why ? maybe the full backup file has multiple append ?

Thanks.


alter database mtsnew set recovery  FULL
--back up
backup database mtsnew to disk  = 'C:\mssql2008\backup\mts10-21-2011.bak'
backup database mtsnew to disk  = 'C:\mssql2008\backup\mts10-21-2011.df1'  with init,differential
backup log mtsnew to disk ='C:\mssql2008\backup\mts10-21-2011.log'

-- restore
restore database mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.bak' with NORECOVERY
--restore database mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.df1' with NORECOVERY
RESTORE HEADERONLY FROM DISK = 'C:\mssql2008\backup\mts10-21-2011.df1'
restore database mtsnew with RECOVERY

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-22 : 15:18:59

RESTORE HEADERONLY lists the backup headers that are in the file. Nothing more. It's used BEFORE starting restores so that you can tell what is in the backup file and which of the backups you want to restore.

--
Gail Shaw
SQL Server MVP

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-22 : 16:53:14

RESTORE HEADERONLY FROM DISK = 'C:\mssql2008\backup\mts10-21-2011.df1'
run the above statement and see the results. If there are multiple backups present in the same file, you will have to use Restore clause WITH FILE=n
Run restore headeronly for full backup file as well. Restore the backup you want to (if it contains multiple backups).

Kristen
Test

22859 Posts

Posted - 2011-10-22 : 17:14:10

quote:
Originally posted by java148

if I change to this, use 'HEADERONLY', it will work. why ?

Because its just outputting information about the file, its not actually restoring it.

Post the output from the HEADERONLY command here and folk will advise you what to do.

Kristen
Test

22859 Posts

Posted - 2011-10-22 : 17:16:31

quote:
Originally posted by GilaMonster

No, probably what you have is multiple backups appended in the same file.

Gail: If the O/P added "WITH INIT" to the first Full Backup command that would fix that, presumably?

i.e.



alter database mtsnew set recovery  FULL
--back up
backup database mtsnew to disk  = 'C:\mssql2008\backup\mts10-21-2011.bak' WITH INIT
backup database mtsnew to disk  = 'C:\mssql2008\backup\mts10-21-2011.df1'  with init,differential
backup log mtsnew to disk ='C:\mssql2008\backup\mts10-21-2011.log' WITH INIT

-- restore
restore database mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.bak' with NORECOVERY
restore database mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.df1' with NORECOVERY
restore LOG mtsnew from disk  = 'C:\mssql2008\backup\mts10-21-2011.log' with NORECOVERY
restore database mtsnew with RECOVERY

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-22 : 18:10:04

Sure it'll fix it. I'm trying to him why that change fixes it.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources