SQL database restore issues

polarstar
polarstar used Ask the Experts™
on
Having an odd SQL problem attempting to get a database moved from one machine to another.  Both machines are running SQL 2005 Standard 32 bit and are both Windows 2003 servers.  When I attempt to back the database up using the SQL tools and restore the .BAK file to the new SQL server, I get the following error:

Restore failed for Server “servername” (Microsoft.SqlServer.Smo)
Additional Information:
System.Data.SqlClient.SqlError: RESTORE detected an error on page (44:2097184) in database “RestoreDBName” as read from the backup set.

Likewise when I attempt to simply copy the .MDF and .LDF files to the new server and attach them, I get the following error:

Attach database failed for Server “servername”
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.
A system assertion check has failed.  Typically an assertion failure is caused by a software bug or data corruption.  To check for database corruption, consider running DBCC CHECKDB.  If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft.  An update might be a vailable from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Could not open new database ‘NewDatabaseName”  Create DATABASE is aborted.
Location: “logmgr.cpp”:3277
Expression: lfh->lfh_startOffset==startOffset
SPID:55
Process ID: 292876 (Microsoft SQL Server, Error: 3624)

From everything I’ve read, these errors generally indicate some sort of database corruption.  However when I run DBCC CHECKDB on the database on the source server, it comes up perfectly clean... no corruption detected.  In fact, this server hosts 8 different databases.  All DB’s check out okay on the source server, however none of them will restore to a new server using either of the above methods.  I’m having a hard time believing all of them could be corrupt when they show absolutely no indication of a problem.  

Anyone have any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
dsackerContract ERP Admin/Consultant

Commented:
Try doing the backup and restore via SQLCMD (or via a query window in SQL Server MS).

The backup would be as follows (obviously, change "MyDatabase" and "D:\BACKUPS" to whatever database and location you want to back up - just using for example):

BACKUP DATABASE MyDatabase
    TO DISK = 'D:\BACKUPS\MyDatabase.bak'
    WITH NOFORMAT, NOINIT,
    NAME = 'MyDatabase-Full Database Backup',
    NOREWIND, NOUNLOAD, STATS = 10, SKIP

Open in new window


Copy your backup file to the other server, and there do:

RESTORE DATABASE MyDatabase
FROM  DISK = 'E:\Backups\MyDatabase.bak' WITH  FILE = 1,
MOVE 'MyDatabase_Data' TO 'E:\SQLDATA\MyDatabase_Data.mdf',
MOVE 'MyDatabase_Log' TO 'E:\SQLLOG\MyDatabase_Data.ldf',
NOUNLOAD,  REPLACE,  STATS = 10

Open in new window


Again, change your names and locations to suit.

Author

Commented:
Well... this process seemed to get a lot farther before failing. The DB is now on the new server in Suspect mode.  A DBCC CHECKDB of the suspect database seems to find no issues.  The error I recieved during the restore is as follows:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 64016 pages for database 'BKD', file 'BKD_Data' on file 1.
Processed 2 pages for database 'BKD', file 'BKD_Log' on file 1.
Msg 824, Level 16, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x2aaaaaaa). It occurred during a read of page (1:11) in database ID 6 at offset 0x00000000016000 in file 'c:\tmp\BKD.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3167, Level 16, State 2, Line 1
RESTORE could not start database 'BKD'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'BKD' (database ID 6) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

Author

Commented:
Check that... I thought because I had the suspect database highlighted when I ran the DBCC CHECKDB that it was actually running on that database.  It turns out it will not allow me to run DBCC CHECKDB with the databse in its current state.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

dsackerContract ERP Admin/Consultant

Commented:
On the target machine, if the DB is in a state that has it frozen, you may need to delete it first.

Author

Commented:
Sorry, I'm not clear what you mean.  The database didn't exist prior to me restoring it (i.e. nothing frozen, nothing to delete... I essentially created a new database as part of the restore process).  Following the restore, it is now in a "suspect" state.

Your help is much appreciated...
dsackerContract ERP Admin/Consultant

Commented:
Glad to help. Sorry for any delay in answering. I'm at work. :)

Often I will change the owner to SA and set it to trusted. Try running this script after you restore.

USE MyNewDatabase
GO

ALTER DATABASE MyNewDatabase SET TRUSTWORTHY ON
GO

EXEC sp_configure 'clr enabled', 1
GO

RECONFIGURE
GO

EXEC sp_changedbowner 'sa'
GO

Open in new window

Author

Commented:
Unfortunately, it doesn't appear to want to let me do anything with the DB.  I get this error when trying to run your suggested command immediately following the restore.

Msg 926, Level 14, State 1, Line 1
Database 'BKD' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
dsackerContract ERP Admin/Consultant

Commented:
You may try the following (not guaranteed to work):

EXEC sp_resetstatus 'MyNewDatabaseOnNewServer'
GO

USE MyNewDatabaseOnNewServer
DBCC CHECKDB WITH NO_INFOMSGS
GO

Open in new window

dsackerContract ERP Admin/Consultant

Commented:
Make sure you're playing on the new server (not on your existing production).

Not that, I'm doubtful, but some things should be double-checked: For instance, you DID restore on the NEW server, right?

Author

Commented:
Yep... all of this is happening on the "new" server.  Backed up from the Production server.  Copied the .BAK files to the "new" server.  Performed the restore on the new server and DB came up in Suspect mode.  Do I need to specify the RECOVERY switch when restoring?
dsackerContract ERP Admin/Consultant

Commented:
Usually you don't have to, but I'm not sure but that perhaps your backup was taken before a complete recovery (or checkdb, etc) was done. Try the recovery option. At this point, you'll have to play with it a bit.
Well... we've basically tracked this down to being a hardware issue (no surprise there).  The trick for us is to figure out how to repair the databases we were able to pull of since we don't have "known good" backups and don't know at which point the data in the backups started becoming corrupt.  Sounds like some sort of data recovery is in order :-(

Author

Commented:
We found the underlying issue to be a hardware problem.  There was no way to get data off in a consistent state.
dsackerContract ERP Admin/Consultant

Commented:
Sounds like a tough problem. Good luck.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial