Restoring SQL Server 2008 DB onto SQL Server 2005

The following steps give you complete details of Restoring a Northwind database on SQL Server 2008 to SQL Server 2005:

1. Select Northwind Database go to "Tasks" and select "Generate Scripts..". Generate Scripts wizard will open.

2. Click "Script all objects in the selected database" (see Figure 2), and then click "Next."

3. 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 "Type of data to Script"      option is set to "Schema and data." Click "Next" when you are happy with the options.

4.Select "Script to file," select the file name, and choose "Single file" (see Figure 4). Click "Next" for a summary (see Figure 5).

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.


6. When the scripting is completed, look for the following lines:

 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

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.