Restore Database From SQL Server 2008 to SQL Server 2005, Part 2: Generate SQL Server Scripts Wizard
MS SQL Server, Windows August 14th, 2009Generate SQL Server Scripts Wizard
On Part 1: Introduction, I mentioned about error messages when you try to restore a database from SQL Server 2008 to SQL Server 2005. Now let’s see a first solution to solve the problems.
On this post, you see how to backup ‘Northwind’ database by generate a SQL Server script on SQL Server 2008. Then, restore the ‘Northwind’ database by execute the SQL Server script on SQL Server 2005.
Step-by-step
- On Microsoft SQL Server Management Studio, connects to the SQL Server 2008. Right-click on the database that you want to backup and select Tasks -> Generate Scripts.

- On Welcome to the Generate SQL Server Scripts Wizard, click Next.

- On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.

- On Choose Script Options, set Script Database Create to False and Script for Server Version to SQL Server 2005.
Note: You can set Script Database Create to True if your source and destination for store database files are the same location.

- Continue on Choose Script Options, scroll down and set Script Data to True. Click Next
Note: Set this option to true to include data on each table to a script.

- On Output Option, select a destination for the output script. Select Script to file and browse to the location that you want. Click Next.

- On Script Wizard Summary, you can review your selections. Then, click Finish.

- On Generate Script Progress, the wizard is creating a SQL Server script.

- When the script has been completed, you see the output file as similar the figure below.

- Connect to SQL Server 2005, create a new database. Right-click Database -> New Database.
Note: If you have set Script Database Create to True on step 4, you don’t have to create a database manually.

- Type ‘Northwind’ as database name. Click OK.

- Execute the SQL Server script file that you have created.

- Now the database ‘Northwind’ is restored on SQL Server 2005.

Related post
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 3: Export Data Wizard Export Data Wizard On Part 1: Introduction, I mentioned about error messages when you try to restore a database from...
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 1: Introduction Problem When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or...
- How to backup and restore database on Microsoft SQL Server 2005 Introduction This post shows a step-by-step guide to backup and restore a database between two Microsoft SQL Server 2005 instances....
Related posts:




November 19th, 2009 at 9:17 am
I tried it and upon execution of the sql in the 2005 server I got this error:
“…Not enough storage is available to process this command”. I still have enough HD space, does the error mean the RAM? Thanks.
November 25th, 2009 at 2:49 pm
Hi, iami
Do one of the following, then retry the command: (1) reduce the number of running programs; (2) remove unwanted files from the disk the paging file is on and restart the system; (3) check the paging file disk for an I/O error; or (4) install additional memory in your system.
Reference: Microsoft Technet
April 22nd, 2010 at 7:53 pm
I followed this procedure but i keep getting this error message:
Column dDate in object table1 contains type Date, which is not supported in the target server version, SQL Server 2005. (Microsoft.SqlServer.Smo)
can someone help me about this error? what will i do?
thanks
May 1st, 2010 at 1:00 am
Mark, the error you are getting this as 2005 doesn’t have the data type “date” These need to be changed to datetime or another data type of 2005
June 17th, 2010 at 4:11 am
I got the same message as IAMI(message 1) got .So I just generated DDL Scripts and used export utility to export the data.