Read About the Problem of Restoring SQL Server 2008 to SQL Server 2005
Creating Scripts to Move SQL Server 2008 Database to SQL Server 2005
These steps show you how to generate the necessary scripts.
- Use the scripting wizard in SQL Server 2008 to script data as well as schemas into SQL Server 2005 compatibility mode.
- Run "Generate SQL Server Scripts" wizard in SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on database and selecting "Tasks –> Generate Scripts."
- Click "Script all objects in selected database" & then click "Next."
- Change script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True". (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 options.
- Finish the wizard.
After creating the file with 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.