GAPTHEGURU

Geek with special skills

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)

Restore Database Error Message

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)

Attach Database Error Message

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.

  1. 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.
  2. 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

  1. 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.
    Generate Scripts
  2. On Welcome to the Generate SQL Server Scripts Wizard, click Next.
    Welcome to the Generate SQL Server Scripts Wizard
  3. On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.
    Select Database
  4. 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.
    Script for Server Version to SQL Server 2005
  5. 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.
    Script Data to True
  6. On Output Option, select a destination for the output script. Select Script to file and browse to the location that you want. Click Next.
    Select Script Output Destination
  7. On Script Wizard Summary, you can review your selections. Then, click Finish.
    Script Wizard Summary
  8. On Generate Script Progress, the wizard is creating a SQL Server script.
    Generate Script Progress
  9. When the script has been completed, you see the output file as similar the figure below.
    The Generated Script
  10. 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.
    Create New Database
  11. Type ‘Northwind’ as database name. Click OK.
    Create Database Northwind
  12. Execute the SQL Server script file that you have created.
    Execute the script
  13. Now the database ‘Northwind’ is restored on SQL Server 2005.
    Northwind Database

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

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

  1. 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.
    Export Data
  2. On Welcome to SQL Server Import and Export Wizard, click Next.
    Welcome to SQL Server Import and Export Wizard
  3. 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.
    Choose a Data Source
  4. 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.
    Choose a Destination
  5. On Create Database, type name as ‘Northwind’ and click OK.
    Create Database
  6. Back to Choose a Destination, I have created ‘Northwind’ database so select it as Database. Click Next.
    Choose a Destination
  7. On Specify Table Copy or Query, select Copy data from one or more tables or views and click Next.
    Copy data from one or more tables or views
  8. On Select Sources Tables and Views, select tables that you want to export. On this example, I select all tables on ‘Northwind’ database.
    Select Sources Tables and Views
  9. On Save and Run Package, click Next.
    Save and Run Package
  10. On Complete the Wizard, you can verify the choices made in the wizard. Then, click Finish.
    Complete the Wizard
  11. Wait until the wizard finishes execution.
    Export Data Successful
  12. Now I have exported tables of ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 successfully.
    The Exported Tables
Advertisements

04/24/2012 - Posted by | Sql Server, T-SQL | , ,

2 Comments »

  1. Hello there, simply turned into aware of your blog thru Google, and found that it is really informative. I’m gonna watch out for brussels. I will appreciate if you continue this in future. A lot of other folks will be benefited from your writing. Cheers!

    Comment by deals uk | 04/25/2012 | Reply

    • Thank you for your feedback. I will continue this in the future. Next post would be on migration from SQL 2005 /2008 to SQL 2012. Cheers!!

      Comment by gaptheguru | 05/03/2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: