Restore of db fails

  • 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'

  • 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.

  • 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’! **
  • Can you do a restoreheaderonly from the backup file?

  • 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.

  • 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?

  • Understood.... Yes, the restore headeronly works fine and returns all of the data it should.

  • 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/

  • We have *.bak *.mdf *.ndf *.ldf excluded from scans.

  • Have you tried to restore it with replace?

    Sounds like the issue might be with the existing database you were trying to restore to.

  • Markus (7/25/2014)


    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....

    Did you run a CHECKDB on the database when you restored in to the NAS device?

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • I am going to do that next. Right now I am importing all of the data from the NAS based database into a new database on our storage. Once that is complete I will run a CHECKDB.

  • I am seeing a few of these making me think there is either corruption or just the internal pointers are off and running an UPDATE USEAGE is in order.

    A read of the file 'xxxxxxxxx.mdf' at offset 0x00008b44ff0000 succeeded after failing 1 time(s) with error: incorrect checksum (expected: 0xe3d07596; actual: 0x41d07796). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB).

  • arnipetursson (7/25/2014)


    Have you tried to restore it with replace?

    Sounds like the issue might be with the existing database you were trying to restore to.

    Yes, tried the REPLACE and also tried to restore it as a new database. Same error no matter what.

  • The database that they provided, was it enabled for encryption by chance? It is worth checking with the vendor if their database is TDE enabled.

    Also worth comparing with them if the database version (down to CU) is exactly the same as yours.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply