R S R KARAN SQL-ORACLE-DBA HANDS ON

Restore Database From SQL Server 2008 to SQL Server 2005 Part 1 – 3

PART 1:

Problem

When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or SQL Server 2000, you will see some error messages as the examples below.

Backup and Restore

You have backup a database from SQL Server 2008. If you try to restore the backup database file to SQL Server 2005, you will receive the error message:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Additional information:
-> The media family on device ‘the backup file‘ is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

Detach and Attach

You have detach a database from SQL Server 2008. If you try to attach the detached database file to SQL Server 2005, you will receive the error message:
Attach database failed for Server ‘SQL Server name’. (Microsoft.SqlServer.Smo)
Additional information:
-> An exception occurred while executing a Transact-SQL statement batch. (Microsoft.SqlServer.ConnectionInfo)
–> The database ‘database name’ cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
Could not open new database ‘database name’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

Solution

These problems occur because a backup or detach database file is not backward compatible. You cannot restore or attach a database which is created from a higher version of SQL Server to a lower version of SQL Server.
But there are some alternatives which can help you to restore a database to a lower version of SQL Server. I divide into separate parts.

Part 2: Generate SQL Server Scripts Wizard. The solution creates a SQL Server script file using a wizard. Then, you simply execute the script file on SQL Server 2005 or SQL Server 2000. So you will get everything as same as the source database on the destination. But there are some disadvantages:
- If the source database contains lots of data, you will have a large script file.
- The generated file is a plain text. Anyone who has access to the file can read it. So you should delete the script file after the restoration.
Part 3: Import and Export Wizard. This solution exports data to the targeted SQL Server using a wizard. It is more secure and effective than the first solution. But you can only export tables and views only.

————————————————————————————————————————————————————————————————————————————————————————————

PART 2: Generate SQL Server Scripts Wizard

Now let’s see a first solution to solve the problems.
On this post, you see how to backup ‘Northwind’ database by generate a SQL Server script on SQL Server 2008. Then, restore the ‘Northwind’ database by execute the SQL Server script on SQL Server 2005.

Step-by-step

On Microsoft SQL Server Management Studio, connects to the SQL Server 2008. Right-click on the database that you want to backup and select Tasks -> Generate Scripts.
On Welcome to the Generate SQL Server Scripts Wizard, click Next.
On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.
On Choose Script Options, set Script Database Create to False and Script for Server Version to SQL Server 2005.
Note: You can set Script Database Create to True if your source and destination for store database files are the same location.
Continue on Choose Script Options, scroll down and set Script Data to True. Click Next
Note: Set this option to true to include data on each table to a script.
On Output Option, select a destination for the output script. Select Script to file and browse to the location that you want. Click Next.
On Script Wizard Summary, you can review your selections. Then, click Finish.
On Generate Script Progress, the wizard is creating a SQL Server script.
When the script has been completed, you see the output file as similar the figure below.
Connect to SQL Server 2005, create a new database. Right-click Database -> New Database.
Note: If you have set Script Database Create to True on step 4, you don’t have to create a database manually.
Type ‘Northwind’ as database name. Click OK.
Execute the SQL Server script file that you have created.
Now the database ‘Northwind’ is restored on SQL Server 2005.

———————————————————————————————————————————————————————————-

PART 3: Export Data Wizard

On this post, you see how to export tables on ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 using export data wizard.

Step-by-step

On Microsoft SQL Server Management Studio, connects to SQL Server 2008. Right-click on the database that you want to export data -> select Tasks -> Export Data.
On Welcome to SQL Server Import and Export Wizard, click Next.
On Choose a Data Source, select the source from which to copy data. Set Data source to SQL Server Native Client 10.0. Verify that Server name is the source of SQL Server 2008 that you want and select Database as ‘Northwind’. Click Next.
On Choose a Destination, specify where to copy data to. Set Destination to SQL Server Native Client 10.0. Type the Server name to the destination of SQL Server 2005 that you want. You can also click Refresh to verify if you can connect to the specify server name. Currently, I don’t have ‘Northwind’ database on SQL Server 2005 so I will create a new one, click New.
On Create Database, type name as ‘Northwind’ and click OK.
Back to Choose a Destination, I have created ‘Northwind’ database so select it as Database. Click Next.
On Specify Table Copy or Query, select Copy data from one or more tables or views and click Next.
On Select Sources Tables and Views, select tables that you want to export. On this example, I select all tables on ‘Northwind’ database.
On Save and Run Package, click Next.
On Complete the Wizard, you can verify the choices made in the wizard. Then, click Finish.
Wait until the wizard finishes execution.
Now I have exported tables of ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 successfully.