There are lot of ways by which we can migrate SQL2000 to SQL2005 where we can easily copy SQL2000 databases to SQL2005. While migrating lower to higher version its possible to migrate easily, but in some cases if needed to revert back SQL2000 its a question in everybodys mind that is it possible to do this?. Microsoft has made it simple by developing “Microsoft SQL Server Database Publishing Wizard” tool. SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window.
Lets brief the steps to script SQL2005 database to SQL2000 using Database Publishing Wizard.
- Download and install Microsoft SQL Server Database Publishing Wizard — 2.1 MB
- Database Publishing wizard will be available @ the start menu itself.
- Start the Database Publishing wizard
* Here I’ve shown you example by migrating “Sagar” db from SQL2005 to SQL2000. Click on next to continue
* In the below window, provide SQL2005 servername and the authentication details.
* Once its done select the database which you need to script to SQL2000
* Select the path where you need to save the script file
* In the below window, specify your needs i(i.e whether you need schema or data or both). As marked with arrow specify the target as SQL server 2000 then click next.
* In the next window you will get the summary after that the scripted window as below.
* Before using the script in SQL2000 server, just create the database as below
* After successful creation, open the script in SQL2000 and execute your data, schema everything will be ready in SQL2000.