From SQL Server 2005 to 2000

Newt.Net


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.

Madhu K Nair

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

Madhu

Arnie Rowland

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.

jcesare

It would be advisable to change the compatability level of the 9.0 database to 8.0.

Newt.Net

Thank you all for replying.

One question, how to change the compatibility level

Arnie Rowland

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 ¨CScript Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901

Newt.Net

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

Madhu K Nair

use system sp sp_dbcmptlevel

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

Madhu

Newt.Net

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.

Madhu K Nair

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

Newt.Net

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

Newt.Net

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.

Madhu K Nair

how many tables are there in this database

Madhu

Newt.Net

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.

Madhu K Nair

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