locked
The problem with SQL after migrating the database to another machine RRS feed

  • Question

  • User-289539425 posted

    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?

    Tuesday, June 14, 2016 4:31 PM

Answers

  • User-362624244 posted

    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/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 22, 2016 3:38 PM

All replies

  • User1559292362 posted

    Hi donaldlyons86,

    System.Data.SqlClient.SqlError: RESTORE detected an error on page (44:2097184) in database “RestoreDBName” as read from the backup set.

    According to your error message, it seems the backup file has been corrupted and couldn't restore, please check if your SQL Database is healthy and clean and execute DBCC CHECKDB on that database. After the successful completion dbcc checks, Initiate one more backup with the current database and verify that backup file before restoring.

    Recommended Practice: when you completed your SQL Backup it is recommended to verify that backup file immediately

    Please execute the below command and check If it is able to restore database

    RESTORE verifyonly FROM DISK='D:\DB.bak';

    Best regards,

    Cole Wu

    Wednesday, June 15, 2016 3:11 AM
  • User768703680 posted

    Hi,

    Please use the below mentioned codes

    Restore a full backup

    This will restore the database using the specified file. If the database already exists it will overwrite the files. If the database does not exist it will create the database and restore the files to same location specified in the backup. The original location can be checked by using RESTORE FILELISTONLY.

     

    RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
    GO

     

    Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)

    The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off.

     

    RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
    GO

     

    Restore a differential backup

    To restore a differential backup, the options are exactly the same. The first thing that has to happen is to do a full restore using the NORECOVERY option. Then the differential can be restored.

    RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
    GO
    RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
    GO

     

    Restore using a backup file that has multiple backups

    Let's say we use the same backup file, AdventureWorks.BAK, to write our full backup and our differential backup. We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let's say that the restore headeronly tells us that in position 1 we have a full backup and in position 2 we have a differential backup. The restore commands would be.

     

    RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY, FILE = 1
    GO
    RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 2
    GO
    Wednesday, June 15, 2016 9:32 AM
  • User-362624244 posted

    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/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 22, 2016 3:38 PM
  • User-289539425 posted

    Thank you all for your advice! The issue has been resolved.

    Saturday, June 25, 2016 9:14 PM