How To Convert SQL Server 2008 Database To SQL Server 2005?

Requirements

You cannot restore database backup of SQL Server 2008 to SQL Server 2005 since the database backup of SQL Server 2008 is not compatible Follow the step-by-step guide to convert databases of SQL Server 2008 to 2005.

Step by Step Guide

1) Start the conversion wizard

Open SQL Server Management Studio2008. In Object Explorer, right click the database that you want to convert. Select Tasks > Generate Scripts....

Change Hyper-V Default Folders Step 1

2) Next

Click Next.

Change Hyper-V Default Folders Step 1

3) Select database and objects

Select the database that you want to convert, and check on Scripts all objects in the selected databases

Change Hyper-V Default Folders Step 2

4) Convert Options

Set options:

'Script for Server Version' = 'SQL Server 2005'
'Script Data' = 'True'
'Scirpt Database Create' = 'True'
    

 

Change Hyper-V Default Folders Step 2

5) Output Option

Select option Script to file, Single file and Unicode text.

Change Hyper-V Default Folders Step 1

6) 'Finish'

View summary and click Finish.

Change Hyper-V Default Folders Step 1

7) Result

Now you got a complete database creation script with data. It can be executed on target database server.

 

Change Hyper-V Default Folders Step 1

8) Amend Script

Open the generated script in SQL Server Management Studio 2005. Find the following section and amend the path to proper data folder
    CREATE DATABASE [StockTraderDB] ON  PRIMARY 
( NAME = N'StockTraderDB', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB.mdf ,
 SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'StockTraderDB_log', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB_log.LDF',
 SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

9) Execute the Script

When finish, you will have the converted database of SQL Server 2005.

Add Feedback