Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Restore of db fails Expand / Collapse
Author
Message
Posted Thursday, July 24, 2014 2:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 1,326, Visits: 2,950
We have a SQL2008R2 db backup given to us by a vendor that is 127Gig in size which is using SQL2008R2 compression. The database is about 800gig. They have given us the db backup on two different USB drives and one NAS device. When we attempt to restore it into our SQL2008R2 we get the error below. The verify fails too. If we restore it TO the NAS device the restore works. Any idea what page (0:0) is trying to say? I saw a post on this forum from 2012 but it didn't really have an answer.

RESTORE DATABASE [DBTEST3] FROM DISK = N'\\dd690b.global.wiie.com\backup\sqlserver\wpsdbv02_backups\SumTest_db_201407240708.BAK' WITH FILE = 1, MOVE N'COMPANY_82_Live' TO N'E:\DATAB\DBTEST3.mdf', MOVE N'COMPANY_82_Live_log' TO N'E:\DATAB\DBTEST3_1.ldf', MOVE N'COMPANY_82_Live_audit' TO N'E:\DATAB\DBTEST3_2.ndf', MOVE N'COMPANY_sumtotal_audit_1' TO N'E:\DATAB\DBTEST3_3.ndf', MOVE N'ftrow_AspenSearch_Core' TO N'E:\DATAB\DBTEST3_4.ndf', MOVE N'ftrow_AspenSearch_User' TO N'E:\DATAB\DBTEST3_5.ndf', MOVE N'ftrow_AspenSearch_ACP' TO N'E:\DATAB\DBTEST3_6.ndf', MOVE N'ftrow_AspenSearch_ACC' TO N'E:\DATAB\DBTEST3_7.ndf', MOVE N'ftrow_AspenSearch_CDS' TO N'E:\DATAB\DBTEST3_8.ndf', MOVE N'ftrow_AspenSearch_Content' TO N'E:\DATAB\DBTEST3_9.ndf', MOVE N'ftrow_AspenSearch_Notif' TO N'E:\DATAB\DBTEST3_10.ndf', MOVE N'ftrow_FTC_Activity' TO N'E:\DATAB\DBTEST3_11.ndf', MOVE N'ftrow_FTC_Emp' TO N'E:\DATAB\DBTEST3_12.ndf', MOVE N'ftrow_FTC_Org' TO N'E:\DATAB\DBTEST3_13.ndf', MOVE N'ftrow_FTC_Eval' TO N'E:\DATAB\DBTEST3_14.ndf', MOVE N'ftrow_FTC_Usr' TO N'E:\DATAB\DBTEST3_15.ndf', MOVE N'ftrow_FTC_Job' TO N'E:\DATAB\DBTEST3_16.ndf', MOVE N'ftrow_FTC_QA' TO N'E:\DATAB\DBTEST3_17.ndf', MOVE N'ftrow_FTC_Social' TO N'E:\DATAB\DBTEST3_18.ndf', NOUNLOAD, STATS = 10
GO


Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database "DBTEST3" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.



Msg 3203, Level 16, State 1, Line 2
Read on "\\xxxxxx\backup\sqlserver\backups\DBTEST3_db_201407240708.BAK" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.



restore verifyonly from disk = '\\xxxxxxx\backup\sqlserver\backups\DBTEST3_db_201407240708.BAK'




Post #1596016
Posted Friday, July 25, 2014 1:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 19, 2014 4:28 AM
Points: 84, Visits: 519
Hi - interesting situation.

Can you check this thread? http://blogs.msdn.com/b/venkn/archive/2010/03/16/failing-to-restore-sql-server-database-backup-with-internal-consistency-error.aspx

It's a db that is corrupt. Did you ask your client to run dbcc checkdb and mail you the results?

You could try to restore it with the CONTINUE_AFTER_ERROR. See Books online for details.




Post #1596102
Posted Friday, July 25, 2014 3:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 6:08 AM
Points: 2,482, Visits: 3,028
Markus (7/24/2014)
...If we restore it TO the NAS device the restore works...

In addition to Runaldo's post:
Reading your post it looks like you can succesfully restore the database to the NAS. Next you could create a new backup from this restored database (on NAS) and restore this backup to the correct location... Of course after running CHECKDB on the restored database and include the verify-option on creating the new backup.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1596136
Posted Friday, July 25, 2014 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, Visits: 469
Can you do a restoreheaderonly from the backup file?
Post #1596252
Posted Friday, July 25, 2014 9:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 1,326, Visits: 2,950
We restored the db from the NAS backup to the NAS device just fine. I then tried to backup the db to local disk then restore it. The restore failed.

As far as doing the restore continue after failure it leaves the db in recovery status... not sure how I'd fix it after that.

Right now I am using Import/Export wizard to pull in all of the data into a new database.

I haven't tried the headeronly restore function. Will try that.



Post #1596268
Posted Friday, July 25, 2014 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, Visits: 469
To be fair, it will only tell you some of the contents of the file, and will not update anything. I am just curious to see if a) It will work and B) What does it show?
Post #1596270
Posted Friday, July 25, 2014 9:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 1,326, Visits: 2,950
Understood.... Yes, the restore headeronly works fine and returns all of the data it should.


Post #1596277
Posted Friday, July 25, 2014 9:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 11:34 AM
Points: 1,087, Visits: 3,359
Markus, is there virus scanning or some other disk process in place on the disks you want to restore it to?



Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1596279
Posted Friday, July 25, 2014 9:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 1,326, Visits: 2,950
We have *.bak *.mdf *.ndf *.ldf excluded from scans.


Post #1596282
Posted Friday, July 25, 2014 4:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:30 AM
Points: 322, Visits: 983
Have you tried to restore it with replace?
Sounds like the issue might be with the existing database you were trying to restore to.
Post #1596431
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse