Beyond general concept of upgrading, I faced a challenge of downgrade. I needed to migrate a database in SQL Server 2005 Express edition to SQL Server 2000 standard edition. I thought of how to do the same. Time permitted was just few hours.
I scratched on my head and thought of possible ways:
1. Detach from 2005 and attach in SQL 2000: Can not be done. It is not allowed, even if you set the compatibility level of the database to SQL server 2000. This just sets the way how sql queries are parsed and processed.
2. Import from SQL Server 2000 Enterprise Manager: Not possible, the Enterprise Manager does not connectthe SQL Server 2005 Express edition instance.
3. Connect to SQL Server 2000 from SQL Server management Studio: But there is no option for export.
4. I tried creating script. It creates the database, not the data.
So, started a google search. Got a good link for Moving SQL Server 2005 Express databases to SQL Server 2000 and read about using Microsoft SQL Server Database Publishing Wizard. I used the steps in the post:
- Ran the Database Publishing Wizard against my SQL Server 2005 Express database.
- Created a SQL Server 2000-compliant SQL script that contained all
the SQL statements required to create the database. The SQL script also
created all the INSERT statements required to populate the tables in
- Created a new blank database in SQL Server 2000.
- Ran the SQL script from step 2 against the SQL Server 2000 database - using the Query Analyser.
But there were few issues. I tried running the script from the Enterprise Manager on the SQL Server 2000 instance. The Query Analyser did not allow me to open the script file, saying it has long lines. So I got worried. Then finally I could run it from SQL Server management Studio from which I connected to the 2000 server.