How to transfer a MS SQL 7 database to MS SQL 2000?

Goh, Yong Kwang

unread,

Apr 1, 2003, 4:00:46 AM4/1/03

to

Hi.

I'm currently using a MS SQL 7 database (Desktop Edition) on my
developer PC/Workstation for local testing/debugging purposes after
which it has to be transferred (imported) to another MS SQL 2000
server for live operation.

Trouble is: My PC is an offline machine and is not connected to the MS
SQL 2000 server in any way.

What is the easiest way to transfer my current MS SQL 7 database as-is
(preserving the table design, relationships, primary keys, data type
and data hopefully etc.) to a MS SQL 2000 Server, given the above
constraints? Saving it to a .MDB file may cause the database to lose
it's relationships, and constraints and even mess up the data type.

Is there a way like MS Access where I can export my SQL 7 database to
a disk file (like a .MDB file) which I can then copy onto a Zip disk
or CD and then import into the MS SQL 2000 server w/o using a network
ODBC connection?

Thanks.

---
Goh, Yong Kwang
gohyon...@hotmail.com

BP Margolin

unread,

Apr 1, 2003, 6:28:19 AM4/1/03

to

Goh, Yong Kwang,

Check out the documentation on the sp_detach_db and sp_attach_db system stored procedures in the SQL Server 7.0 and SQL Server 2000 Books Online.

You can detach your SQL Server 7.0 database files (both the .mdf and the .ldf), make an operating system copy of them, and then attach them to the SQL Server 2000 instance. SQL Server 2000 will accept detached SQL Server 7.0 database files, and automatically convert them into SQL Server 2000 format.

Note that this moves all the database specific information ... tables, views, stored procedures, database users, etc. ... to the SQL Server 2000 instance, but does not move server specific information such as SQL Server logins. Thus you might have to execute the sp_change_users_login system stored procedure on SQL Server 2000 to "re-synchronize" server logins and database users. Documentation on the sp_change_users_login system stored procedure can be found in the SQL Server Books Online as well.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Goh, Yong Kwang" <gohyon...@hotmail.com> wrote in message news:354933d6.03040...@posting.google.com...

Anith Sen

unread,

Apr 1, 2003, 12:30:28 PM4/1/03

to

Check these links:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304692
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274463

--
- Anith
(Please respond only to newsgroups)

Dave Woodthorpe

unread,

Apr 1, 2003, 3:21:34 PM4/1/03

to

As an alternative you can :-
1. Backup and verify the database through Enterprise Manager and copy the
file onto CD
2. Create a new database on the SQL 2000 server
3. Restore the newly created database using Enterprise Manager and selecting
the "From Device" option which allows you to select a file not known to SQL
server.

BP Margolin <bpm...@attglobal.net> wrote in message
news:3e897...@news1.prserv.net...