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

Requirements

If you are trying to restore database backup of SQL Server 2008 to SQL Server 2005, you are bound to fail. Database backup of SQL Server 2008 is not compatible backward, you cannot restore it to SQL Server 2005. The following is a solution to convert databases of SQL Server 2008 to 2005

Step by Step Guide

1) Start convert 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 finished, You should get converted database of SQL Server 2005

Related Resources

Restore a SQL Server 2008 Database into SQL Server 2005

Navigator

Other Knowledgebase Articles

Basic MSSQL Hosting

# of Domains: 4
# of SQL Server Databases: 4
Disk Space: 50GB
Bandwidth: Unmetered
SQL Server 2014
Monthly: $4.99
More Information About Basic Shared SQL Server Hosting Plan

Reseller MSSQL Hosting

# of Domains: 40
# of SQL Server Databases: 40
Disk Space: 200GB
Bandwidth: Unmetered
SQL Server 2014
Monthly: $14.99
More Information About Unlimited SQL Server Hosting Plan

Express Hyper-V Server

Dedicated Memory:2GB
Disk Space: 50GB Diskspace
Bandwidth: Unmetered
Windows 2012/2008: Free
Monthly: $11.99
More Information About Express Windows Hyper-V Server

Dedicated SQL Server

CPU: Quad-Core Xeon X3440
RAM: 16GB RAM
Disk: 2x120GB SSD + 300GB SATA
RAID: RAID 1
Bandwidth: Unmetered
Windows 2012/2008: Free
Monthly: $79.00
More Information About Unlimited Dedicated SQL Server
ASP.NET Hosting | DotNetNuke Hosting | Windows Hyper-V Hosting | Dedicated SQL Server Hosting | Dedicated Windows Server Hosting