How to migrate a SQL Server database to a newer version of SQL Server

DBAs are well aware that downgrading a SQL Server database cannot be done out of the box. Even when the compatibility level of the database you want to migrate to an older SQL Server version matches that version, you can’t simply restore the backup. What you probably didn’t expect is that upgrading can also be a problem

Restoring a database backup created on a SQL Server 2008 R2 to a SQL Server 2012 instance goes smoothly. But, if you try to restore a SQL Server 2000 backup to a SQL Server 2012, you’ll get the following error:

One of the solutions is to:

  1. Restore the SQL Server 2000 backup on SQL Server 2008
  2. Set the compatibility level to 100
  3. Create a database backup on SQL Server 2008
  4. Restore the SQL Server 2008 backup on SQL Server 2012
  5. Set compatibility level to 110

The downside of this solution is that there has to be 3 versions of SQL Server installed and the discontinued or deprecated T-SQL features would have to be manually removed.

If there is no access to the live database from which the backup was taken from:

  1. Start SQL Server Management Studio and connect to the instance that contains the database
  2. In the database context menu, navigate to Tasks | Generate Scripts…. This will invoke the Generate and Publish Scripts wizard
  3. In the Introduction step of the wizard click Next
  4. In the Choose Objects step of the wizard make sure that the Script entire database and all database objects option is selected and click Next
  5. In the Set Scripting options step of the wizard:
    1. Select the Save scripts to a specific location option
    2. Specify whether database objects and data will be scripted to a single or multiple files, as well as the path and encoding of the generated scripts
    3. Click Advanced
    4. In the Advanced Scripting Options dialog that will appear:
      • In the Script for Server Version option, specify Schema and data
      • Set the following options to True:
        • Script Logins
        • Script Full-Text Indexes
        • Script Triggers
      • Click OK
  • Click Next
  • In the Summary step of the wizard click Next
  • In the Save or Publish Scripts step of the wizard click Finish
  • Execute the generated SQL script(s) against the older SQL instance

Although the procedure listed above should work:

  1. The discontinued or deprecated T-SQL features would have to be manually removed
  2. It might fail if a database contains dependencies which SQL Server fails to recognize (e.g. dependencies to databases located on linked servers)

If there is no access to the original database for any reason (e.g. migrating a database to an off-site location with no network connectivity), the procedures above are not applicable

This is where ApexSQL Diff and ApexSQL Data Diff can help

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects and resolves them without errors. It generates comprehensive reports on the found differences and can automate the synchronization process between live and versioned databases, backups, snapshots and script folders

ApexSQL Data Diff is a SQL Server data comparison and synchronization tool which detects data differences and resolves them without errors. It can compare and synchronize live databases and native or natively compressed database backups and generate comprehensive reports on the detected differences

To restore the backup to a newer SQL Server version

  1. On the target SQL instance, create an empty database to hold the data and objects restored from the backup
  2. Start ApexSQL Diff
  3. Click New in the Project management dialog

    Project management dialog

  4. In the Source pane:
    • Select Backup from the Type drop-down box
    • Click Add file(s) and navigate to the folder where the backup file is located
    • Select the backup and click Open
  5. In the Destination pane:
    • Select Database from the Type drop-down box
    • Specify the SQL instance where the database, in which objects that need to be recovered, is located, from the Server drop-down box
    • Specify the authentication method for that SQL instance (and a valid set of credentials if the SQL Server authentication is chosen)
    • Specify the name of the database where the objects will be recovered to, in the Database drop-down box
  6. Click Compare
  7. Select all the objects listed in the Main grid

    ApexSQL Diff - Main grid

  8. Click Synchronize in the Actions region of the ribbon’s Home tab
  9. Click Next in the Synchronization direction step of the Synchronization wizard
  10. Click Next in the Dependencies step of the Synchronization wizard

    Synchronization wizard - Dependencies

  11. Review the synchronization summary and warnings, if there are any, and click Next

    Synchronization wizard - Summary

  12. In the Output options step of the Synchronization wizard, select Synchronize a database
  13. Click Finish
  14. Start ApexSQL Data Diff
  15. Click New in the Project management dialog

    ApexSQL Diff - Project management dialog

  16. In the Source pane:
    • Select Backup from the Type drop-down box
    • Click Add file(s) and navigate to the folder where the backup file is located
    • Select the backup and click Open
  17. In the Destination pane:
    • Select Database from the Type drop-down box
    • Specify the SQL instance where the database from which the data that is going to be recovered from is located, from the Server drop-down box
    • Specify the authentication method for that SQL instance (and a valid set of credentials if the SQL Server authentication is chosen)
    • Specify the name of the database to which the data will be recovered to in the Database drop-down box
  18. Click Compare

    Click Compare

  19. In the Main grid, select the database tables the data is to be recovered to
  20. In the Data difference pane, select the rows to be recovered
  21. Click Synchronize in the Actions region of the ribbon’s Home tab
  22. Click Next in the Synchronization direction step of the Synchronization wizard
  23. In the Output options step of the Synchronization wizard, select Synchronize a database
  24. Click Next

    Preview the impact of the synchronization script

  25. Review the synchronization summary and warnings, if there are any, and click Finish

This way, migrating an old version of SQL Server database to a newer version is done via the schema synchronization of the original database backup and a blank live database first, and data synchronization afterwards. ApexSQL Diff and ApexSQL Data Diff will take care of all depreciated and discontinued features for you

April 4, 2013