From SQL Server 2005 to 2000

Hi all.

I'm doing some testing database on my computer that I've installed SQL server 2005. But the server at the company I'm working with has SQL Server 2000.

What do I need to do to convert my database file in 2005 into 2000 so that I can add it in the company's server?

TIA.

you can not "convert" SQL Server 2005 database to SQL Server 2000 database. The otherway round is possible.

Madhu

|||

While you cannot 'convert' the SQL 2005 database to a SQL 2000 format, you can script out the tables, views, functions, stored procedures, etc., then after checking those scripts for compatibility, you can run the scripts on SQL 2000 and re-create the database.

|||It would be advisable to change the compatability level of the 9.0 database to 8.0.|||Thank you all for replying.

One question, how to change the compatibility level?
|||

It would be nice if it were so simple as changing the compatibility level.

The physical files are different between SQL 2000 and SQL 2005, and to my knowledge, there is no current mechanism to 'downgrade' a database.

I think that you are left with my earlier suggestion about scripting out the database objects, and running those scripts in SQL 2000 to recreate a SQL 2000 database. Perhaps these links will help with scripting:

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901

|||Thanks for all the links, most helpful!

However, the last link didn't point to a website. Was it this:
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en
that you want to link to?
|||

use system sp sp_dbcmptlevel

Eg: EXEC sp_dbcmptlevel 'AdventureWorks', '80';

Madhu

|||Hi all,

Taken into considerations of all your suggestions, I'm compiling my step-by-step of 'downgrading' my database from version 2005 to 2000:

1) Set the compatibility of database to SQL server 2000:
EXEC sp_dbcmptlevel 'myDatabase', 80

2) Next, I will do this:
Open SQL Server Management Studio, and connect.
Right-click on myDatabase, choose 'Script Database As > CREATE To > New query editor window.
Save as .sql

Would it be advisable to script my tables one by one? But then I'll have to run the script in order of which table is created first based on relationships. Would scripting the whole database in one go takes care of the order of creating tables?
Any suggestions on what else I need to consider before/after doing this?

TIA.
|||

in this case there is no need to degrade the copatibility level. You can script all the objects into a single file and the run the script in 2000. by this step your schema will be ready. Now you need to improt the data using import/export or ssis or simple insert into statment after configuring linked server.

Madhu

|||Hello Madhu,

I've tried right-click on my database and Script Database to a new query window but there are only 60-70 lines and I didn't see any CREATE TABLE anywhere. So I would assume that I need to right-click on each table and Script table to a different .sql file for each table?

Eera
|||I probably script my database the wrong way. Sorry for giving you the wrong description earlier. Now, when I right-click my database, point to Tasks and chose 'Generate Scripts' and the scripts generated looks more promising.

Just started using SQL server this week.
|||

how many tables are there in this database ?

Madhu

|||There are (only) 8 tables in my database.

I have yet to research and find out the process of importing data using the method that u mentioned.
|||

if its only 8 table then the best method will be

(a) Configure LinkedServer in 2000 for SQL Server 2005

(b) use your own script like Insert into tablename select *from <LinkedserverName>.databasename.owner.tablename

Madhu