Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 4th, 2005, 11:40 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default Restore SQL 2000 .bak file into SQL 7.0 ??

Should this be possible


TYIA

Wind is your friend
Matt
__________________
Wind is your friend
Matt
 
Old April 5th, 2005, 01:23 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I don't think so. There are many backwards incompatibilities between 7 and 2000.

However, you could restore the database to a 2000 server, and then use DTS to transfer (selected) data from 2000 to 7.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old April 5th, 2005, 02:01 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Good day Imar

My 'issue' in a bit more detail - your opinion would be great.

Localy we run 2000. We have built a system that is to be used in a network where Enterprize Manager is not allowed (A new defence security issue - not even on servers). I have been asked to create a step by step doc including scripts that allows them to create, restore (from my 2000 .bak file), create login, user, issue read, write etc using query analyzer. They are telling me my scripts dont work. We were not told they were running version 7.0 until after i wrote and sent the doc.

Are you aware if the following scripts will run in version 7.0's Query Analyzer?

2..CREATE DATABASE NavSysCom;

3..RESTORE DATABASE navSysCom
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\backUp\navSysCom.bak' WITH
   move 'navSysCom_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\navSysCom_Data.mdf',
   move 'navSysCom_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\navSysCom_log.ldf';

4..EXEC sp_addlogin 'NavSysComUser', 'password', 'NavSysCom', 'english';

5..EXEC sp_adduser 'NavSysComUser';
   sp_addRoleMember 'db_datareader','NavSysComUser';
   sp_addRoleMember 'db_datawriter','NavSysComUser';

Appreciate your time

Wind is your friend
Matt
 
Old April 5th, 2005, 04:31 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I think the scripts will run fine. Stuff like CREATE DATABASE NavSysCom works on Sql 7 as well.

However, restoring a 2000 database to Sql 7 won't work.

Are you just interested in the meta data (structure) or also in the actual data in the database?

If all you need is the structure, you can script the entire database and then run that script on the server.

If you need the database as well, I guess you'll need to install Sql 7 on your machine, transfer data using DTS from 2000 to 7 and them create a backup of the Sql 7 installation.....

Cheers,

Imar
 
Old April 5th, 2005, 06:20 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Sorry for the delayed response, we are a world appart here in Australia. I wasnt so keen to instal 7.0 on my machine, would this be trouble free running both versions?

I have another machine for the day and plan to instal 7.0 - hopefully I can do this between machines. Spent most of yesturday just finding the 7.0 disk.

;;;Are you just interested in the meta data (structure) or also in the actual data in the database?
The lot - Tables, data and if possible the diagrams - whats my chances of getting the diagrams? The code police within defence get upset when relationships are not enforced and diagrams present.

Thanking you Imar

Wind is your friend
Matt
 
Old April 6th, 2005, 11:49 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I *think* you can install 2000 after 7 because 2000 works with instances, so you could install it as (local)\InstanceName

However, I don't think you can install 7 after 2000, so you might need to uninstall 7 first, or use a clean machine and then transfer the data.

You can transfer diagrams with DTS. However, diagrams are nothing more than a view on your database. Relations between tables *are* transferred with DTS, so after the transfer you can create a new diagram, select a tables and then you'll end up with 1 diagram of your complete database with all the relations....


Cheers,

Imar
 
Old April 6th, 2005, 06:10 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I ended up installing 7.0 on a clean machine and DTS'ed the data. An interesting problem aroze. I would have thought you could DTS from the 2000 Enterprize Manager (export) to the 7.0 instance - no joy. It worked the other way round however none of my primary keys came through, datatypes yes - Thank you for your time.

Wind is your friend
Matt
 
Old April 7th, 2005, 04:32 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Matt, did you try the standard Import / Export? That's supposed to work between 7 and 2000.....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old April 7th, 2005, 06:51 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

No I didnt - I figured DTS was the way to go - I replaced all the primary keys and made it work - Cheers for your input.

Wind is your friend
Matt
 
Old April 8th, 2005, 04:00 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

You're welcome. Sorry if I put you on the wrong track with DTS.....

Imar




Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
Restore/copy database from SQL 2005 to 2000 shillr SQL Server 2005 2 April 1st, 2008 11:14 AM
looking for access 2000 to sql server 2000 sql/que method SQL Server 2000 0 July 7th, 2005 12:46 PM
SQL 2000 database / text file / binary file pallone Javascript How-To 3 January 28th, 2005 01:26 PM
login as 'sa' failure - need to restore BAK bful SQL Server 2000 2 November 16th, 2003 03:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.