The problem with SQL after migrating the database to another machine

[silas.perkins]

Having an odd SQL problem attempting to get a database moved from one machine to another. Both machines are running SQL 2008 32 bit and are both Windows 2008 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?

 

[OrnSveinsson]

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

 

[OrnSveinsson]

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

Again, change your names and locations to suit.

 

[OrnSveinsson]

If that doesn't work, try to restore a database by third-party tools. You can find them a lot in Google. If you don't want to spend the time looking, you can try a proven tool SQL Server Recovery Toolbox. All the details about this program can be found at the link. http://www.oemailrecovery.com/sql_repair.html

 

[OrnSveinsson]

May be this article will give you more information.

http://www.techrepublic.com/forums/questions/it-seems-our-sql-database-is-corrupted/

 

[DanielNetrval]

Thank you buddy for your help and time. your recommendations are very useful. the issue is resolved.