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 go.microsoft.com. 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."
Figure 1 shows the initial dialog to the wizard. Click "Next" and complete the following steps in the wizard to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005:
Figure 1. Initial Dialog to Generate SQL Server Scripts Wizard: To create your scripts, you have to run the "Generate scripts" wizard. |
- Click "Script all objects in the selected database" (see Figure 2), and then click "Next."
Figure 2. Select Database Dialog:Click the "Script all objects in the selected database" option. - 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.
Figure 3. Choose Script Options Dialog:Set the "Script Data" option to "True." - Select "Script to file," select the file name, and choose "Single file" (see Figure 4). Click "Next" for a summary (see Figure 5).
- Now click on "Finish" to get progress messages while the script runs and completes (see Figure 6).
If the generation process fails, then you can use the "Report" option to see why.Figure 6. Generate Script Progress Dialog: Click "Finish" and you will get progress messages. - When the scripting is completed, look for the following lines:
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:
CREATE DATABASE [Northwind] ON PRIMARY
(NAME = N'Northwind', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.mdf' ,
SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
(NAME = N'Northwind_log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
--EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'
--GO
0 Comments: