Migrate from SQL Server 2008 database to SQL Server 2005 or SQL Server 2000

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

Found this post useful?

Subscribe to our RSS feed, follow us on Twitter or help us grow by sharing our content using the buttons below

This entry was posted in Menus, Microsoft, SQL Server and tagged , , , , , . Bookmark the permalink.

4 Responses to Migrate from SQL Server 2008 database to SQL Server 2005 or SQL Server 2000

  1. Toy says:

    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?

  2. RohitRAnjan says:

    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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.