Restore a SQL Server 2008 Database into SQL Server 2005

Restore a SQL Server 2008 Database into SQL Server 2005

If you’ve tried to back up a database in SQL Server 2008 and then restore it into SQL Server 2005, you know that the database backups are not backward compatible. However, with SQL Server 2008 Management Studio, you can script data and schemas in SQL Server 2005 mode. This 10-Minute Solution takes you through the steps to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005.

Transferring databases from SQL Server 2008 to SQL Server 2005 is not possible with standard backup and restore facilities.

Leverage the scripting wizard in SQL Server 2008 to script data as well as schemas in SQL Server 2005 compatibility mode.

Using the “Generate SQL Server Scripts” Wizard

The Northwind database is no longer shipped as part of the SQL Server installation, but you can download it from The data is scripted as INSERT statements.

To create the scripts, you have to run the “Generate SQL Server Scripts” wizard, which you can run within SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on the database and selecting “Tasks ?> Generate Scripts.”

Click to enlarge

Figure 1. Initial Dialog to Generate SQL Server Scripts Wizard: To create your scripts, you have to run the “Generate scripts” wizard.
  1. Click “Script all objects in the selected database” (see Figure 2), and then click “Next.”

    Click to enlarge

    Figure 2. Select Database Dialog: Click the “Script all objects in the selected database” option.
  2. Amend the script options: Specifically, set “Script for Server Version” to “SQL Server 2005” and set “Script Data” to “True” (see Figure 3). (SQL Server 2000 is also supported.) If you are putting the database on a new instance for the first time, make sure the “Script Database Create” option is set to “True.” Click “Next” when you are happy with the options.

    Click to enlarge

    Figure 3. Choose Script Options Dialog: Set the “Script Data” option to “True.”
  3. Select “Script to file,” select the file name, and choose “Single file” (see Figure 4). Click “Next” for a summary (see Figure 5).

    Figure 4. Output Option Dialog: Select “Script to file,” select the file name, and choose “Single file.”
    Figure 5. Script Wizard Summary: Review your selections for the wizard.
  4. Now click on “Finish” to get progress messages while the script runs and completes (see Figure 6).

    Click to enlarge

    Figure 6. Generate Script Progress Dialog: Click “Finish” and you will get progress messages.

    If the generation process fails, then you can use the “Report” option to see why.

  5. When the scripting is completed, look for the following lines:
    (NAME = N'Northwind', FILENAME =
    N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATA orthwnd.mdf' ,
    LOG ON
    (NAME = N'Northwind_log', FILENAME =
    N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATA orthwnd.ldf' ,
    SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:

    --EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'--GO

Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment. You can now test data against SQL Server 2008 and SQL Server 2005.

The Inevitable Limitations

Of course, this technique is not without its limitations. Here are a few to bear in mind:

  • The data is insecure, as it is in clear readable text. So if you are using real data, you should delete the file created once you have loaded it into SQL Server 2005. You can regenerate the file from the SQL 2008 backup, if necessary.
  • If you have a database with a large amount of data, the script file, of course, will be huge.
  • SQL Server 2008 specifics in the source database will not be migrated.
Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes