Linglom's blog

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.

Step-by-step

1. 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.
   
2. On Welcome to the Generate SQL Server Scripts Wizard, click Next.
   
3. On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.
   
4. 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.
   
5. 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.
   
6. On Output Option, select a destination for the output script. Select Script to file and browse to the location that you want. Click Next.
   
7. On Script Wizard Summary, you can review your selections. Then, click Finish.
   
8. On Generate Script Progress, the wizard is creating a SQL Server script.
   
9. When the script has been completed, you see the output file as similar the figure below.
   
10. 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.
    
11. Type ‘Northwind’ as database name. Click OK.
    
12. Execute the SQL Server script file that you have created.
    
13. Now the database ‘Northwind’ is restored on SQL Server 2005.