SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore of db fails


Restore of db fails

Author
Message
Summer90
Summer90
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7041 Visits: 3831
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'
runaldo
runaldo
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 566
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.



HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8494 Visits: 3718
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’! **
Matt Crowley
Matt Crowley
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1091 Visits: 469
Can you do a restoreheaderonly from the backup file?
Summer90
Summer90
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7041 Visits: 3831
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.
Matt Crowley
Matt Crowley
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1091 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?
Summer90
Summer90
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7041 Visits: 3831
Understood.... Yes, the restore headeronly works fine and returns all of the data it should.
Michael L John
Michael L John
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5889 Visits: 8227
Markus, is there virus scanning or some other disk process in place on the disks you want to restore it to?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Summer90
Summer90
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7041 Visits: 3831
We have *.bak *.mdf *.ndf *.ldf excluded from scans.
arnipetursson
arnipetursson
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1019
Have you tried to restore it with replace?
Sounds like the issue might be with the existing database you were trying to restore to.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search