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
Glad to visit this blog, keep it going.
Thanks Toy, did you know you can subscribe by email to receive the latest articles? Just enter your mail on widget on the right and click the “subscribe” button and you are good to go?
thanks for sharing this tips,
can we disable windows authentication fffrom sqlserver200
Your welcome Rohit, there is no way to disable Windows Authentication on SQL Server because it supports:
1) Windows Authentication
2) SQL Server AND Windows Authentication
However, there is a workaround to prevent the users that are part of the Adminstrators group of Windows from connecting which can be done as follows:
1) Open the SQL Server Manager
2) Expand the SQL Server instance of interest
3) Expand the “Security” folder and click “Logins”
5) Find the BUITIN\Administrators entry and either press the Delete button on the keyboard or right-click-> Delete
6) Confirm by clicking “Yes”
Hope this helps