- Restore Database From SQL Server 2008 to SQL Server 2005, Part 1: Introduction
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 2: Generate SQL Server Scripts Wizard
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 3: Export Data Wizard
Generate 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.
- 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.