restore master database: differentials

Showing posts with label differentials. Show all posts

Friday, March 30, 2012

Restored Full with NORECOVERY and db reads dbName (Restoring...)

Can anyone help me with this issue? I restored a full backup with NORECOVERY because I need to restore the differentials as well but I have not been able to access the db. No matter what I seem to try i receive the following error:

System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)

Does anyone know what I need to do?

Thank you in advance.

Julio

Yes I do I don't know what but the Restore process is not working like 2000 so you still need to run restore with recovery code before your restore will complete. Here was my situation I got a database from someone created on E drive SQL Server refused to restore it without dump devices in the Master. I did that and end up with your problem, so I started again and added restore with recovery code making about four sets of code and it was restored. The link below are some of the code you need, make sure you have the .bak delete the restoring and start fresh with the code in the link below. Hope this helps.

http://wvmitchell.blogspot.com/search?q=backup+restore

|||do you have anyother full backup other than what you restored with norecovery option.........may be you might have restored another set of full backup rather than the one which you took b4 differential backup was taken..........this differential wud only have the changes which you made after the most recent full backup was taken.........so restore the last full backup taken before the differential backup was taken....may be that mite be the problem........

Restored Full with NORECOVERY and db reads dbName (Restoring...)

System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)

Does anyone know what I need to do?

Thank you in advance.

Julio

http://wvmitchell.blogspot.com/search?q=backup+restore

Monday, March 26, 2012

Restore Timing Transaction Logs versus Differentials

Currently we backup out SQL Server 2005 databases with a combination of full, differential and transaction log backups and plan on continuing this practice. Currently all backups are written to physical servers other than the servers where the DBs are running and all backups are also written to tape. Our tape backups are costly and I am considering no longer writing the differential backups to tape to reduce costs. Before making this decision I would like to understand how much longer it would take to recover several days of transaction log backups, versus a single differential and only a few hours of transation log backups. Does anyone have any any information about the time difference? Any ratios or rules of thumb?

Thanks,
Julia

Stopping the tape copies of differentials seems like a reasonable plan IF you kept two or three of the DIFFERENTIAL BACKUP copies on disk. (I prefer to keep all differentials since the last FULL BACKUP, tossing them only after the next FULL BACKUP is verified.

Time to recover: Seems like the major time issue is the manual process of handling each individual restore. The fewer files to restore, the less 'manual' time. The greated the number of files involved, the greater the risk of a corrupt or damaged file.

|||
Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?
|||

There are so many variables on that, I wouldn't hazard a guess.

You could run a comparision test on another server, comparing the timings.

|||Has anyone run these types of timed tests comparing the restore times from 2 types of backups?|||

That is purely depends upon the number of transactions on your database to restore, see this blog http://sqlserver-qa.net/blogs/perftune/archive/2007/06/12/get-backup-and-restore-performance-stats-with-a-dmv.aspx on the performance stats for backup & restore tasks.

EgleK wrote:

Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?

Monday, March 12, 2012

Restore problem - it's beaten me :(

Hi, I just can't for the life of me get my dbases back.
I restore the Full database ok, but I can't apply the differentials at
all - on *any* database I've got.
All the details (quite a few pics) are here:
http://homepages.paradise.net.nz/duncanm4/sqlrestore/
please, if you can see the problem, I'd sure appreciate knowing about
it.
If further details are required, please reply to the NG.
TIA
--
DuncanThis is a known SQL2000 problem.
To Fix install Latest SQL SP
>--Original Message--
>Hi, I just can't for the life of me get my dbases back.
>I restore the Full database ok, but I can't apply the
differentials at
>all - on *any* database I've got.
>All the details (quite a few pics) are here:
>http://homepages.paradise.net.nz/duncanm4/sqlrestore/
>please, if you can see the problem, I'd sure appreciate
knowing about
>it.
>If further details are required, please reply to the NG.
>TIA
>--
>Duncan
>.
>|||In article <MPG.1a163f978c187ade989742@.msnews.microsoft.com>,
hard@.work.ok says...
> Hi, I just can't for the life of me get my dbases back.
> I restore the Full database ok, but I can't apply the differentials at
> all - on *any* database I've got.
> All the details (quite a few pics) are here:
> http://homepages.paradise.net.nz/duncanm4/sqlrestore/
> please, if you can see the problem, I'd sure appreciate knowing about
> it.
> If further details are required, please reply to the NG.
> TIA
OK, I'm goddamn blind - yes, 22nd <> 29th !@.!@.#!@.
It has already been sugested that I begin masterbating backwards,
thankyou!
--
Duncan|||In article <086501c3a51f$0b263b50$a501280a@.phx.gbl>,
anonymous@.discussions.microsoft.com says...
> This is a known SQL2000 problem.
> To Fix install Latest SQL SP
Thanks Brian, it was found to be a PEBKAC problem.
--
Duncan|||Does below work on your machine? I'm on SQL2K sp1.
CREATE TABLE pubs..test(id int identity)
insert pubs..test default values
BACKUP DATABASE pubs TO DISK = 'C:\p.bak' WITH INIT
insert pubs..test default values
BACKUP DATABASE pubs TO DISK = 'C:\p.bak' WITH DIFFERENTIAL
RESTORE DATABASE pubs FROM DISK = 'C:\p.bak' WITH FILE = 1, NORECOVERY
RESTORE DATABASE pubs FROM DISK = 'C:\p.bak' WITH FILE = 2, RECOVERY
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Duncan McC" <hard@.work.ok> wrote in message
news:MPG.1a163f978c187ade989742@.msnews.microsoft.com...
> Hi, I just can't for the life of me get my dbases back.
> I restore the Full database ok, but I can't apply the differentials at
> all - on *any* database I've got.
> All the details (quite a few pics) are here:
> http://homepages.paradise.net.nz/duncanm4/sqlrestore/
> please, if you can see the problem, I'd sure appreciate knowing about
> it.
> If further details are required, please reply to the NG.
> TIA
> --
> Duncan

Wednesday, March 7, 2012

Restore of backup fails, backup file corrupt?

We made a backup of one of our databases. The backup was done to disk. First a full backup and then differentials. When we try to restore this backup we run into problems... The restore of the full backup works without problems, the first differential (#2 in the backup set) can also be restored without problems. But when we try to restore one of the other sets (3 -> 31) we get the following errors:
"Server: Msg 3136, Level 16, State 1, Line
Cannot apply the backup on device 'e:\voyager.bak' to database 'Voyagerback'
Server: Msg 3013, Level 16, State 1, Line
RESTORE DATABASE is terminating abnormally.
I pasted the commands we use to restore the backup and their results below. Are we doing anything wrong? Is there any way to verify the integrity of a backup file besides VERIFYONLY (which tells us that the backup is ok)
Thanks
Stijn
RESTORE HEADERONL
FROM DISK='e:\voyager.bak
NULL NULL 1 NULL 0 1 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 5189120 49000000034700001 49000000035100001 49000000034700003 0 2002-09-25 11:16:01.000 2002-09-25 11:16:02.000 52 228 1033 196609 80 4608 8 0 194 W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_A
NULL NULL 5 NULL 0 2 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 3549184 56000000003700001 56000000003900001 56000000003700001 49000000034700003 2002-09-24 11:52:27.000 2002-09-24 11:52:29.000 52 228 1033 196609 80 4608 8 0 194 W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_A
NULL NULL 5 NULL 0 3 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 206848 115000000034600001 115000000034800001 115000000034600001 115000000033900003 2003-06-19 07:41:25.000 2003-06-19 07:41:25.000 52 228 1033 196609 80 4608 8 0 194 W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_A
NULL NULL 5 NULL 0 4 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 3942400 144000000031100001 144000000031300001 144000000031100001 115000000033900003 2002-09-24 01:22:22.000 2002-09-24 01:22:23.000 52 228 1033 196609 80 4608 8 0 194 W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_A
//cut some lines out to save spac
NULL NULL 5 NULL 0 29 2 SA W-UNION1 Voyager 539 2002-09-25 03:50:21.000 166209536 2868000000025300001 2868000000025500001 2868000000025300001 154000000051100003 2004-01-09 13:39:07.000 2004-01-09 13:39:53.000 52 228 1033 196609 80 4608 8 0 194 W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_A
NULL NULL 5 NULL 0 30 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 175319040 2984000000024100001 2984000000030800001 2984000000024100001 154000000051100003 2004-01-15 10:55:45.000 2004-01-15 10:56:09.000 52 228 1033 196609 80 4608 8 0 194 W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_A
NULL NULL 5 NULL 0 31 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 198846464 3358000000038400001 3358000000038600001 3358000000038400001 154000000051100003 2004-01-30 16:44:24.000 2004-01-30 16:44:49.000 52 228 1033 196609 80 4608 8 0 194 W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_A
--
RESTORE FILELISTONL
FROM DISK='e:\voyager.bak
Voyager_dat C:\MSDE\MSSQL\Data\Voyager.mdf D PRIMARY 5242880 3518437208064
Voyager_log C:\MSDE\MSSQL\Data\Voyager.ldf L NULL 1048576 3518437208064
--
RESTORE VERIFYONL
FROM DISK='e:\voyager.bak
WITH FILE=3
The backup set is valid
--
RESTORE DATABASE Voyagerbac
FROM DISK='e:\voyager.bak
WITH NORECOVERY
MOVE 'Voyager_dat' TO 'e:\voyager.mdf'
MOVE 'Voyager_log' TO 'e:\voyager.ldf
Processed 624 pages for database 'Voyagerback', file 'Voyager_dat' on file 1
Processed 1 pages for database 'Voyagerback', file 'Voyager_log' on file 1
RESTORE DATABASE successfully processed 625 pages in 1.051 seconds (4.865 MB/sec).
--
RESTORE DATABASE Voyagerback
FROM DISK='e:\voyager.bak'
WITH FILE=31,
MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
MOVE 'Voyager_log' TO 'e:\voyager.ldf'
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'e:\voyager.bak' to database 'Voyagerback'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Stijn
Try to specify
RESTORE DATABASE Voyagerback
FROM DISK='e:\voyager.bak'
WITH FILE=31,
RECOVERY,
MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
MOVE 'Voyager_log' TO 'e:\voyager.ldf'
"Stijn" <anonymous@.discussions.microsoft.com> wrote in message
news:B56E7D6D-49C3-4C50-B2D0-F43980DF3949@.microsoft.com...
> We made a backup of one of our databases. The backup was done to disk.
First a full backup and then differentials. When we try to restore this
backup we run into problems... The restore of the full backup works without
problems, the first differential (#2 in the backup set) can also be restored
without problems. But when we try to restore one of the other sets (3 ->
31) we get the following errors:
> "Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'e:\voyager.bak' to database
'Voyagerback'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally."
> I pasted the commands we use to restore the backup and their results
below. Are we doing anything wrong? Is there any way to verify the
integrity of a backup file besides VERIFYONLY (which tells us that the
backup is ok)?
> Thanks!
> Stijn.
>
> RESTORE HEADERONLY
> FROM DISK='e:\voyager.bak'
> NULL NULL 1 NULL 0 1 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
5189120 49000000034700001 49000000035100001 49000000034700003 0 2002-09-25
11:16:01.000 2002-09-25 11:16:02.000 52 228 1033 196609 80 4608 8 0 194
W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 2 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
3549184 56000000003700001 56000000003900001 56000000003700001
49000000034700003 2002-09-24 11:52:27.000 2002-09-24 11:52:29.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 3 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
206848 115000000034600001 115000000034800001 115000000034600001
115000000033900003 2003-06-19 07:41:25.000 2003-06-19 07:41:25.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 4 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
3942400 144000000031100001 144000000031300001 144000000031100001
115000000033900003 2002-09-24 01:22:22.000 2002-09-24 01:22:23.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> //cut some lines out to save space
> NULL NULL 5 NULL 0 29 2 SA W-UNION1 Voyager 539 2002-09-25 03:50:21.000
166209536 2868000000025300001 2868000000025500001 2868000000025300001
154000000051100003 2004-01-09 13:39:07.000 2004-01-09 13:39:53.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 30 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
175319040 2984000000024100001 2984000000030800001 2984000000024100001
154000000051100003 2004-01-15 10:55:45.000 2004-01-15 10:56:09.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 31 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
198846464 3358000000038400001 3358000000038600001 3358000000038400001
154000000051100003 2004-01-30 16:44:24.000 2004-01-30 16:44:49.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
>
> --
> RESTORE FILELISTONLY
> FROM DISK='e:\voyager.bak'
> Voyager_dat C:\MSDE\MSSQL\Data\Voyager.mdf D PRIMARY 5242880
35184372080640
> Voyager_log C:\MSDE\MSSQL\Data\Voyager.ldf L NULL 1048576 35184372080640
> --
> RESTORE VERIFYONLY
> FROM DISK='e:\voyager.bak'
> WITH FILE=31
> The backup set is valid.
> --
> RESTORE DATABASE Voyagerback
> FROM DISK='e:\voyager.bak'
> WITH NORECOVERY,
> MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
> MOVE 'Voyager_log' TO 'e:\voyager.ldf'
> Processed 624 pages for database 'Voyagerback', file 'Voyager_dat' on file
1.
> Processed 1 pages for database 'Voyagerback', file 'Voyager_log' on file
1.
> RESTORE DATABASE successfully processed 625 pages in 1.051 seconds (4.865
MB/sec).
> --
> RESTORE DATABASE Voyagerback
> FROM DISK='e:\voyager.bak'
> WITH FILE=31,
> MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
> MOVE 'Voyager_log' TO 'e:\voyager.ldf'
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'e:\voyager.bak' to database
'Voyagerback'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>|||Same problem with the RECOVERY option..
Anyway thanks for your reply!|||I would check the backup history tables in msdb, perhaps someone did a
database backup to some other backupdevice between number 2 and number 3 on
your backup device? Watch out for VERIFYONLY. It basically only verifies
that the file/tape is readable. Only way to do a proper verification is to
do an actual restore. Yukon will be better in this aspect.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stijn" <anonymous@.discussions.microsoft.com> wrote in message
news:B56E7D6D-49C3-4C50-B2D0-F43980DF3949@.microsoft.com...
> We made a backup of one of our databases. The backup was done to disk.
First a full backup and then differentials. When we try to restore this
backup we run into problems... The restore of the full backup works without
problems, the first differential (#2 in the backup set) can also be restored
without problems. But when we try to restore one of the other sets (3 ->
31) we get the following errors:
> "Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'e:\voyager.bak' to database
'Voyagerback'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally."
> I pasted the commands we use to restore the backup and their results
below. Are we doing anything wrong? Is there any way to verify the
integrity of a backup file besides VERIFYONLY (which tells us that the
backup is ok)?
> Thanks!
> Stijn.
>
> RESTORE HEADERONLY
> FROM DISK='e:\voyager.bak'
> NULL NULL 1 NULL 0 1 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
5189120 49000000034700001 49000000035100001 49000000034700003 0 2002-09-25
11:16:01.000 2002-09-25 11:16:02.000 52 228 1033 196609 80 4608 8 0 194
W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 2 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
3549184 56000000003700001 56000000003900001 56000000003700001
49000000034700003 2002-09-24 11:52:27.000 2002-09-24 11:52:29.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 3 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
206848 115000000034600001 115000000034800001 115000000034600001
115000000033900003 2003-06-19 07:41:25.000 2003-06-19 07:41:25.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 4 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
3942400 144000000031100001 144000000031300001 144000000031100001
115000000033900003 2002-09-24 01:22:22.000 2002-09-24 01:22:23.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> //cut some lines out to save space
> NULL NULL 5 NULL 0 29 2 SA W-UNION1 Voyager 539 2002-09-25 03:50:21.000
166209536 2868000000025300001 2868000000025500001 2868000000025300001
154000000051100003 2004-01-09 13:39:07.000 2004-01-09 13:39:53.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 30 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
175319040 2984000000024100001 2984000000030800001 2984000000024100001
154000000051100003 2004-01-15 10:55:45.000 2004-01-15 10:56:09.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 31 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
198846464 3358000000038400001 3358000000038600001 3358000000038400001
154000000051100003 2004-01-30 16:44:24.000 2004-01-30 16:44:49.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
>
> --
> RESTORE FILELISTONLY
> FROM DISK='e:\voyager.bak'
> Voyager_dat C:\MSDE\MSSQL\Data\Voyager.mdf D PRIMARY 5242880
35184372080640
> Voyager_log C:\MSDE\MSSQL\Data\Voyager.ldf L NULL 1048576 35184372080640
> --
> RESTORE VERIFYONLY
> FROM DISK='e:\voyager.bak'
> WITH FILE=31
> The backup set is valid.
> --
> RESTORE DATABASE Voyagerback
> FROM DISK='e:\voyager.bak'
> WITH NORECOVERY,
> MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
> MOVE 'Voyager_log' TO 'e:\voyager.ldf'
> Processed 624 pages for database 'Voyagerback', file 'Voyager_dat' on file
1.
> Processed 1 pages for database 'Voyagerback', file 'Voyager_log' on file
1.
> RESTORE DATABASE successfully processed 625 pages in 1.051 seconds (4.865
MB/sec).
> --
> RESTORE DATABASE Voyagerback
> FROM DISK='e:\voyager.bak'
> WITH FILE=31,
> MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
> MOVE 'Voyager_log' TO 'e:\voyager.ldf'
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'e:\voyager.bak' to database
'Voyagerback'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>

Restore of backup fails, backup file corrupt?

We made a backup of one of our databases. The backup was done to disk. Fir
st a full backup and then differentials. When we try to restore this backup
we run into problems... The restore of the full backup works without probl
ems, the first differential
(#2 in the backup set) can also be restored without problems. But when we t
ry to restore one of the other sets (3 -> 31) we get the following errors:
"Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'e:\voyager.bak' to database 'Voyagerback'
.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally."
I pasted the commands we use to restore the backup and their results below.
Are we doing anything wrong? Is there any way to verify the integrity of a
backup file besides VERIFYONLY (which tells us that the backup is ok)?
Thanks!
Stijn.
RESTORE HEADERONLY
FROM DISK='e:\voyager.bak'
NULL NULL 1 NULL 0 1 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 51891
20 49000000034700001 49000000035100001 4
9000000034700003 0 2002-09-25 11:16:
01.000 2002-09-25 11:16:02. 000 52 228 1033 196609 80 4608 8 0 194 W
-UNION1 0
{1362433B-7E4A-465F-A852-C
76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_C
I_AS
NULL NULL 5 NULL 0 2 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 35491
84 56000000003700001 56000000003900001 5
6000000003700001 49000000034700003 2
002-09-24 11:52:27.000 2002-09-24 11:52:29.000 52 228 1033 196609 80 4608 8
0 194 W-UNION1 0 {1362433B-
7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Lati
n1_General_CP1_CI_AS
NULL NULL 5 NULL 0 3 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 20684
8 115000000034600001 115000000034800001
115000000034600001 11500000003390000
3 2003-06-19 07:41:25.000 2003-06-19 07:41:25.000 52 228 1033 196609 80 4608
8 0 194 W-UNION1 0 {136243
3B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_L
atin1_General_CP1_CI_AS
NULL NULL 5 NULL 0 4 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 39424
00 144000000031100001 144000000031300001
144000000031100001 1150000000339000
03 2002-09-24 01:22:22.000 2002-09-24 01:22:23.000 52 228 1033 196609 80 460
8 8 0 194 W-UNION1 0 {13624
33B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_
Latin1_General_CP1_CI_AS
//cut some lines out to save space
NULL NULL 5 NULL 0 29 2 SA W-UNION1 Voyager 539 2002-09-25 03:50:21.000 1662
09536 2868000000025300001 28680000000255
00001 2868000000025300001 1540000000
51100003 2004-01-09 13:39:07.000 2004-01-09 13:39:53.000 52 228 1033 196609
80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF
} SQL_Latin1_General_CP1_CI_AS
NULL NULL 5 NULL 0 30 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 1753
19040 2984000000024100001 29840000000308
00001 2984000000024100001 1540000000
51100003 2004-01-15 10:55:45.000 2004-01-15 10:56:09.000 52 228 1033 196609
80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF
} SQL_Latin1_General_CP1_CI_AS
NULL NULL 5 NULL 0 31 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000 1988
46464 3358000000038400001 33580000000386
00001 3358000000038400001 1540000000
51100003 2004-01-30 16:44:24.000 2004-01-30 16:44:49.000 52 228 1033 196609
80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF} {1362433B-7E4A-465F-A852-C76DF919B2BF
} SQL_Latin1_General_CP1_CI_AS
RESTORE FILELISTONLY
FROM DISK='e:\voyager.bak'
Voyager_dat C:\MSDE\MSSQL\Data\Voyager.mdf D PRIMARY 5242880 35184372080640
Voyager_log C:\MSDE\MSSQL\Data\Voyager.ldf L NULL 1048576 35184372080640
RESTORE VERIFYONLY
FROM DISK='e:\voyager.bak'
WITH FILE=31
The backup set is valid.
RESTORE DATABASE Voyagerback
FROM DISK='e:\voyager.bak'
WITH NORECOVERY,
MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
MOVE 'Voyager_log' TO 'e:\voyager.ldf'
Processed 624 pages for database 'Voyagerback', file 'Voyager_dat' on file 1
.
Processed 1 pages for database 'Voyagerback', file 'Voyager_log' on file 1.
RESTORE DATABASE successfully processed 625 pages in 1.051 seconds (4.865 MB
/sec).
RESTORE DATABASE Voyagerback
FROM DISK='e:\voyager.bak'
WITH FILE=31,
MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
MOVE 'Voyager_log' TO 'e:\voyager.ldf'
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'e:\voyager.bak' to database 'Voyagerback'
.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Stijn
Try to specify
RESTORE DATABASE Voyagerback
FROM DISK='e:\voyager.bak'
WITH FILE=31,
RECOVERY,
MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
MOVE 'Voyager_log' TO 'e:\voyager.ldf'
"Stijn" <anonymous@.discussions.microsoft.com> wrote in message
news:B56E7D6D-49C3-4C50-B2D0-F43980DF3949@.microsoft.com...
> We made a backup of one of our databases. The backup was done to disk.
First a full backup and then differentials. When we try to restore this
backup we run into problems... The restore of the full backup works without
problems, the first differential (#2 in the backup set) can also be restored
without problems. But when we try to restore one of the other sets (3 ->
31) we get the following errors:
> "Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'e:\voyager.bak' to database
'Voyagerback'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally."
> I pasted the commands we use to restore the backup and their results
below. Are we doing anything wrong? Is there any way to verify the
integrity of a backup file besides VERIFYONLY (which tells us that the
backup is ok)?
> Thanks!
> Stijn.
>
> RESTORE HEADERONLY
> FROM DISK='e:\voyager.bak'
> NULL NULL 1 NULL 0 1 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
5189120 49000000034700001 49000000035100001 49000000034700003 0 2002-09-25
11:16:01.000 2002-09-25 11:16:02.000 52 228 1033 196609 80 4608 8 0 194
W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 2 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
3549184 56000000003700001 56000000003900001 56000000003700001
49000000034700003 2002-09-24 11:52:27.000 2002-09-24 11:52:29.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 3 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
206848 115000000034600001 115000000034800001 115000000034600001
115000000033900003 2003-06-19 07:41:25.000 2003-06-19 07:41:25.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 4 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
3942400 144000000031100001 144000000031300001 144000000031100001
115000000033900003 2002-09-24 01:22:22.000 2002-09-24 01:22:23.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> //cut some lines out to save space
> NULL NULL 5 NULL 0 29 2 SA W-UNION1 Voyager 539 2002-09-25 03:50:21.000
166209536 2868000000025300001 2868000000025500001 2868000000025300001
154000000051100003 2004-01-09 13:39:07.000 2004-01-09 13:39:53.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 30 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
175319040 2984000000024100001 2984000000030800001 2984000000024100001
154000000051100003 2004-01-15 10:55:45.000 2004-01-15 10:56:09.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 31 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
198846464 3358000000038400001 3358000000038600001 3358000000038400001
154000000051100003 2004-01-30 16:44:24.000 2004-01-30 16:44:49.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
>
> --
> RESTORE FILELISTONLY
> FROM DISK='e:\voyager.bak'
> Voyager_dat C:\MSDE\MSSQL\Data\Voyager.mdf D PRIMARY 5242880
35184372080640
> Voyager_log C:\MSDE\MSSQL\Data\Voyager.ldf L NULL 1048576 35184372080640
> --
> RESTORE VERIFYONLY
> FROM DISK='e:\voyager.bak'
> WITH FILE=31
> The backup set is valid.
> --
> RESTORE DATABASE Voyagerback
> FROM DISK='e:\voyager.bak'
> WITH NORECOVERY,
> MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
> MOVE 'Voyager_log' TO 'e:\voyager.ldf'
> Processed 624 pages for database 'Voyagerback', file 'Voyager_dat' on file
1.
> Processed 1 pages for database 'Voyagerback', file 'Voyager_log' on file
1.
> RESTORE DATABASE successfully processed 625 pages in 1.051 seconds (4.865
MB/sec).
> --
> RESTORE DATABASE Voyagerback
> FROM DISK='e:\voyager.bak'
> WITH FILE=31,
> MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
> MOVE 'Voyager_log' TO 'e:\voyager.ldf'
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'e:\voyager.bak' to database
'Voyagerback'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>|||Same problem with the RECOVERY option...
Anyway thanks for your reply!|||I would check the backup history tables in msdb, perhaps someone did a
database backup to some other backupdevice between number 2 and number 3 on
your backup device? Watch out for VERIFYONLY. It basically only verifies
that the file/tape is readable. Only way to do a proper verification is to
do an actual restore. Yukon will be better in this aspect.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Stijn" <anonymous@.discussions.microsoft.com> wrote in message
news:B56E7D6D-49C3-4C50-B2D0-F43980DF3949@.microsoft.com...
> We made a backup of one of our databases. The backup was done to disk.
First a full backup and then differentials. When we try to restore this
backup we run into problems... The restore of the full backup works without
problems, the first differential (#2 in the backup set) can also be restored
without problems. But when we try to restore one of the other sets (3 ->
31) we get the following errors:
> "Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'e:\voyager.bak' to database
'Voyagerback'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally."
> I pasted the commands we use to restore the backup and their results
below. Are we doing anything wrong? Is there any way to verify the
integrity of a backup file besides VERIFYONLY (which tells us that the
backup is ok)?
> Thanks!
> Stijn.
>
> RESTORE HEADERONLY
> FROM DISK='e:\voyager.bak'
> NULL NULL 1 NULL 0 1 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
5189120 49000000034700001 49000000035100001 49000000034700003 0 2002-09-25
11:16:01.000 2002-09-25 11:16:02.000 52 228 1033 196609 80 4608 8 0 194
W-UNION1 0 {1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 2 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
3549184 56000000003700001 56000000003900001 56000000003700001
49000000034700003 2002-09-24 11:52:27.000 2002-09-24 11:52:29.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 3 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
206848 115000000034600001 115000000034800001 115000000034600001
115000000033900003 2003-06-19 07:41:25.000 2003-06-19 07:41:25.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 4 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
3942400 144000000031100001 144000000031300001 144000000031100001
115000000033900003 2002-09-24 01:22:22.000 2002-09-24 01:22:23.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> //cut some lines out to save space
> NULL NULL 5 NULL 0 29 2 SA W-UNION1 Voyager 539 2002-09-25 03:50:21.000
166209536 2868000000025300001 2868000000025500001 2868000000025300001
154000000051100003 2004-01-09 13:39:07.000 2004-01-09 13:39:53.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 30 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
175319040 2984000000024100001 2984000000030800001 2984000000024100001
154000000051100003 2004-01-15 10:55:45.000 2004-01-15 10:56:09.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
> NULL NULL 5 NULL 0 31 2 sa W-UNION1 Voyager 539 2002-09-25 03:50:21.000
198846464 3358000000038400001 3358000000038600001 3358000000038400001
154000000051100003 2004-01-30 16:44:24.000 2004-01-30 16:44:49.000 52 228
1033 196609 80 4608 8 0 194 W-UNION1 0
{1362433B-7E4A-465F-A852-C76DF919B2BF}
{1362433B-7E4A-465F-A852-C76DF919B2BF} SQL_Latin1_General_CP1_CI_AS
>
> --
> RESTORE FILELISTONLY
> FROM DISK='e:\voyager.bak'
> Voyager_dat C:\MSDE\MSSQL\Data\Voyager.mdf D PRIMARY 5242880
35184372080640
> Voyager_log C:\MSDE\MSSQL\Data\Voyager.ldf L NULL 1048576 35184372080640
> --
> RESTORE VERIFYONLY
> FROM DISK='e:\voyager.bak'
> WITH FILE=31
> The backup set is valid.
> --
> RESTORE DATABASE Voyagerback
> FROM DISK='e:\voyager.bak'
> WITH NORECOVERY,
> MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
> MOVE 'Voyager_log' TO 'e:\voyager.ldf'
> Processed 624 pages for database 'Voyagerback', file 'Voyager_dat' on file
1.
> Processed 1 pages for database 'Voyagerback', file 'Voyager_log' on file
1.
> RESTORE DATABASE successfully processed 625 pages in 1.051 seconds (4.865
MB/sec).
> --
> RESTORE DATABASE Voyagerback
> FROM DISK='e:\voyager.bak'
> WITH FILE=31,
> MOVE 'Voyager_dat' TO 'e:\voyager.mdf',
> MOVE 'Voyager_log' TO 'e:\voyager.ldf'
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'e:\voyager.bak' to database
'Voyagerback'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>

Friday, March 30, 2012

Restored Full with NORECOVERY and db reads dbName (Restoring...)

Restored Full with NORECOVERY and db reads dbName (Restoring...)

Monday, March 26, 2012

Restore Timing Transaction Logs versus Differentials

Monday, March 12, 2012

Restore problem - it's beaten me :(

Wednesday, March 7, 2012

Restore of backup fails, backup file corrupt?

Restore of backup fails, backup file corrupt?

restore master database

Blog Archive

About Me