Restore a SQL Server 2008 Database into SQL Server 2005 : Page 2
You likely will need to test data against SQL Server 2008 and SQL Server 2005, but ordinary backups are incompatible. Use SQL Server 2008's Generate SQL Server Scripts Wizard to push your SQL Server 2008 data back into SQL Server 2005.
by Chris Goddard
Jan 7, 2009
Page 2 of 2
Figure 1 shows the initial dialog to the wizard. Click "Next" and complete the following steps in the wizard to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005:
Figure 1. Initial Dialog to Generate SQL Server Scripts Wizard: To create your scripts, you have to run the "Generate scripts" wizard.
Click "Script all objects in the selected database" (see Figure 2), and then click "Next."
Figure 2. Select Database Dialog: Click the "Script all objects in the selected database" option.
Amend the script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True" (see Figure 3). (SQL Server 2000 is also supported.) If you are putting the database on a new instance for the first time, make sure the "Script Database Create" option is set to "True." Click "Next" when you are happy with the options.
Figure 3. Choose Script Options Dialog: Set the "Script Data" option to "True."
Select "Script to file," select the file name, and choose "Single file" (see Figure 4). Click "Next" for a summary (see Figure 5).
Figure 4. Output Option Dialog: Select "Script to file," select the file name, and choose "Single file."
Figure 5. Script Wizard Summary: Review your selections for the wizard.
Now click on "Finish" to get progress messages while the script runs and completes (see Figure 6).
Figure 6. Generate Script Progress Dialog: Click "Finish" and you will get progress messages.
If the generation process fails, then you can use the "Report" option to see why.
When the scripting is completed, look for the following lines:
Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment. You can now test data against SQL Server 2008 and SQL Server 2005.
The Inevitable Limitations
Of course, this technique is not without its limitations. Here are a few to bear in mind:
The data is insecure, as it is in clear readable text. So if you are using real data, you should delete the file created once you have loaded it into SQL Server 2005. You can regenerate the file from the SQL 2008 backup, if necessary.
If you have a database with a large amount of data, the script file, of course, will be huge.
SQL Server 2008 specifics in the source database will not be migrated.
Chris Goddard is Technology Manager at Exel Computer Systems plc, a leading independent author of ERP software based in the UK. Chris has been involved in software design and development using a range of languages and platforms for 14 years, including 10 years with SQL Server.