Restore Database From SQL Server 2008 to SQL Server 2005, Part 2: Generate SQL Server Scripts Wizard

This entry is part 2 of 3 in the series Restore Database From SQL Server 2008 to SQL Server 2005

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.
    Generate Scripts
  2. On Welcome to the Generate SQL Server Scripts Wizard, click Next.
    Welcome to the Generate SQL Server Scripts Wizard
  3. On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.
    Select Database
  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.
    Script for Server Version to SQL Server 2005
  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.
    Script Data to True
  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.
    Select Script Output Destination
  7. On Script Wizard Summary, you can review your selections. Then, click Finish.
    Script Wizard Summary
  8. On Generate Script Progress, the wizard is creating a SQL Server script.
    Generate Script Progress
  9. When the script has been completed, you see the output file as similar the figure below.
    The Generated Script
  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.
    Create New Database
  11. Type ‘Northwind’ as database name. Click OK.
    Create Database Northwind
  12. Execute the SQL Server script file that you have created.
    Execute the script
  13. Now the database ‘Northwind’ is restored on SQL Server 2005.
    Northwind Database
Series Navigation<< Restore Database From SQL Server 2008 to SQL Server 2005, Part 1: IntroductionRestore Database From SQL Server 2008 to SQL Server 2005, Part 3: Export Data Wizard >>

19 Comments

  1. iami November 19, 2009
  2. linglom November 25, 2009
  3. mark April 22, 2010
  4. ClumsyHamster May 1, 2010
  5. Rajesh June 17, 2010
  6. Ali August 11, 2010
  7. linglom August 19, 2010
  8. Guillermo September 20, 2010
  9. Prievo December 4, 2010
  10. Sandeep February 5, 2011
  11. Chris April 16, 2011
  12. Caipigott August 26, 2011
  13. Sheenam Jindal January 5, 2012
  14. ramu August 7, 2012
  15. Noel Massey December 8, 2012
  16. Noel Massey December 8, 2012
  17. Aaron December 26, 2012
  18. JoeXH September 27, 2013

Leave a Reply