polarstar
asked on
SQL database restore issues
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.SqlE rror: 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==star tOffset
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?
Restore failed for Server “servername” (Microsoft.SqlServer.Smo)
Additional Information:
System.Data.SqlClient.SqlE
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==star
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?
ASKER
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.
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.
ASKER
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.
On the target machine, if the DB is in a state that has it frozen, you may need to delete it first.
ASKER
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...
Your help is much appreciated...
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.
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
ASKER
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.
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.
You may try the following (not guaranteed to work):
EXEC sp_resetstatus 'MyNewDatabaseOnNewServer'
GO
USE MyNewDatabaseOnNewServer
DBCC CHECKDB WITH NO_INFOMSGS
GO
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?
Not that, I'm doubtful, but some things should be double-checked: For instance, you DID restore on the NEW server, right?
ASKER
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?
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We found the underlying issue to be a hardware problem. There was no way to get data off in a consistent state.
Sounds like a tough problem. Good luck.
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):
Open in new window
Copy your backup file to the other server, and there do:
Open in new window
Again, change your names and locations to suit.