If you ever wanted to backup the schema and data of a database from SQL Server 2008 to SQL Server 2005, it is MUCH easier than you may think. The 2008 DBs may not work with 2005 meaning you can’t do a normal backup (right click on the DB, select Tasks -> Backup Up) because SQL Server 2005 will not be able to restore.
However, i am glad to share with you a hidden feature in SQL Server 2008 that makes it possible. You can create scripts of the whole database in one go by doing the following:
Select generate under Tasks
You will be greeted with the following Introduction
Clicking next will allow you to specify the scope of the backup
After that you can select the method of backup (clipboard VS single sql file VS sql file per object)
Here is where it gets interesting when you enter the Advanced settings, you can fine tune the backup to the last detail. As the figure below indicates, you can make it compatible with 2000, 2005, 2008, or 2008 R2
Moreover, you don’t need the full SQL Server package because this option is available in the express one too.
FInally you are all ready to export the script with or without the data (depending on your choice in the previous steps)
And you are done.
If you found this useful, please share through twitter, facebook, google+, and others. Also consider subscribing to the blog because I will constantly post useful tips about software or hardware issues and interesting finds