Link to home
Start Free TrialLog in
Avatar of polarstar
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.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?
Avatar of dsacker
dsacker
Flag of United States of America image

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.
Avatar of polarstar
polarstar

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

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

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?
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
Avatar of polarstar
polarstar

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.