How to restore a SQL Server 2008 R2 database backup to SQL Server 2008

Recently I got a backup file of a SQL Server 2008 R2 user database, and wanted to make a duplicate on to a SQL Server 2008 server instance.

When I used the normal way to restore, the server complained about it:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.2796. That version is incompatible with this server, which is running version 10.00.5768. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)

According to Microsoft, SQL Server is designed only to be forward compatible, so you can backup an early version of sql server database and restore it to new version, not vice versa. 2008 R2 is considered a major upgrade of 2008, so it is not compatible.

My solution is straight forward, however, not sure if any better way: 1. script out the whole database, 2. transfer all the data.

1. This is easy, but you need to access the source database:
   1. Right click on the database, Tasks -> Generate Scripts…,
   2. In the pop up window, click Next,
   3. On Choose Objects page, check Script entire database and all database objects, Next,
   4. On Set Scripting Option page, click Advanced, change Script for Server Version to target server, in my case, SQL Server 2008. Change other options in your needs,
   5. Click Next twice to start generation,
   6. Open the script and make changes for your target database, for example the db file/log location,
   7. Run the script on the target server to create the database.
   8. There are ways to transfer data, command line tool bcp is quite easy to use:
      1. Bcp can export each table to a file, we can use this sql to help, replace db name, server address and file location below:

use <DB Name>

GO

SELECT ‘bcp <DB Name>.’+TABLE_SCHEMA + ‘.’ + TABLE_NAME+’ out <Source Export Location>\<DB Name>.’+TABLE_SCHEMA + ‘.’ + TABLE_NAME+’ -T -n -S <Source Sever Address>’ FROM Information_Schema.TABLES

WHERE TABLE_TYPE=’BASE TABLE’

order by 1

SELECT ‘bcp <DB Name>.’+TABLE_SCHEMA + ‘.’ + TABLE_NAME+’ in <Target Import Location>\<DB Name>.’+TABLE_SCHEMA + ‘.’ + TABLE_NAME+’ -T -n -S <Target Sever Address>’ FROM Information_Schema.TABLES

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

WHERE TABLE_TYPE=’BASE TABLE’

order by 1

1. Save the result as a .bat file for each select statement,
2. Run 1^st on the source to export data, run 2^nd on target to import data

DONE!

There might be one tricky issue that if any table has Identity column, you should disable it before importing, otherwise, the column value will be regenerated by system instead of getting from the export. We can run this SQL to disable and enable it:

SET IDENTITY_INSERT tablename ON

SET IDENTITY_INSERT tablename OFF

Be aware, turn ON means let your own value be inserted, OFF will generated by system.

Again a script to help you find the identity columns and generate the command:

select distinct

‘SET IDENTITY_INSERT ‘+TABLES.TABLE_SCHEMA + ‘.’ + TABLES.TABLE_NAME+’ ON’ as ON_Script,

‘SET IDENTITY_INSERT ‘+TABLES.TABLE_SCHEMA + ‘.’ + TABLES.TABLE_NAME+’ OFF’ as OFF_Script

from

INFORMATION_SCHEMA.TABLES join INFORMATION_SCHEMA.COLUMNS

on (TABLES.TABLE_CATALOG = COLUMNS.TABLE_CATALOG and

TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA and

TABLES.TABLE_NAME = COLUMNS.TABLE_NAME)

WHERE TABLE_TYPE=’BASE TABLE’

and COLUMNPROPERTY(object_id(COLUMNS.TABLE_NAME), COLUMN_NAME, ‘IsIdentity’) = 1

Run the ON_Script before importing data, and run the OFF_Script to change back after importing.