The problem with SQL after migrating the database to another machine

[silasperkins]

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?

 

[IRstuff]

I suggest you post this on Tek-Tips.com, which is more oriented to computer software issues.

TTFN
I can do absolutely anything. I'm an expert!
faq731-376 forum1529

 

[xwb]

Try

http://www.tek-tips.com/threadminder.cfm?pid=962

 

[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
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.
May be this article will give you more information.

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

 

[Ticklemetech]

Sounds like common sense but make sure everything is updated properly (Microsoft products are you know what when they are not updated).

 

[SkipVought]

Silas, you need to respond, please.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]