Restoring database backup of sql server on a lower version of sql server

Suggest Edits

Restoring higher version database backup

When you try to restore higher version database backup of sql server on a lower version of sql server you get an error. Also you can get the same error with different versions where higher version is being restored on a lower version database server.

The reason to receive this error is that the database servers get changed with service packs and new releases. New object types get added and the lower versions cannot understand these object types.

In order to avoid such conflicts and problems – Higher end database restorations cannot be performed directly on lower end database servers. It is also not possible to Attach the higher version database to lower version sql server.

How to restore such databases?

The best way to do restoration is to upgrade the lower version SQL Server to the version greater than or equal to the higher version SQL Server. There is no direct way to restore the database without upgrading the server.

Here are the alternate ways to restore higher database versions on lower version sql servers:

Script the database Objects and restore them on the server

  1. To script the database objects you can use Generate Scripts Task.

  2. Generate Scripts Task can be found in SQL Server management studio.

  3. RightClick on the database -> Click On Tasks -> Then Click on Generate Scripts…

Run through the wizard.

In the set scripting options tab in the generate scripts wizard click on advanced and select the following options:

  1. For ‘types of data to script’ option Select ‘Schema and data’

  2. For ‘Script for version’ select the required target SQL Server version

  3. Generate the scripts and run the generated scripts on the destination server.

You may have to run the script multiple times when you face any error.

Create a new database every time you rerun the script.

Fix the errors that you may face due to order in which objects are created.

Use SQL Server Import Export Wizard to copy data from source server to destination server (only Data)

You can find Import Export Wizard in SQL Server management studio.

On the Destination Server (Lower database version server)

  1. Right Click on the database -> Click On Tasks -> Then Click on Import.

  2. Give the correct source and destination servers and select all tables. This creates an SSIS package and select the option to run immediately if you want to copy data immediately.

  3. Copy data to destination tables using BCP (only Data)

  4. You can also use BCP to copy data from source server to destination server. BCP out tables on source server and BCP in on the destination server.

Updated 4 months ago