I need to take a server running MS SQL Server 7 on NT4 and clone all the databases, store procedures, views, etc to a new machine running MS SQL Server 7 on Win2k.

So, here's my question: What would be the most effective/efficient way to do this?

A few things to note:

1. The new machine has databases already on it that need to stay there.
2. The new machine will be renamed and given the IP of the old machine so all of our applications/reports will not blow-up.
3. The old machine is to be completely retired, so this has to be a complete move.
   

Key thing is, this needs to be seamless, it is our primary database server for sales reporting, etc. I can't screw it up.

I've moved databases before, but not entire collections of databases on one server like this.

Any help is greatly appreciated!

Whilst I can't say that I've ever had to move an entire server of databases before, the way I normally move a database, when I don't want to risk losing data, is to detach the database on the source, copy the .MDF and .LDF files to the new server, then attach them there.

The main problems I've found with this are that if you have any full-text catalogs then they'll need completely rebuilding (at times I've ended up having to edit the sysfulltextcatalogs and sysobjects tables (the field ftcatid in the latter), and you'd need to recreate scheduled tasks, DTS packages, and so on. Copying the files only transfers the databases themselves, and not anything that's living on the server itself.

I'm sure there's some very neat and clever way you can do it all with replication, but I've always spent so long trying to get it to work that I've given up and gone back to the tried-and-tested ways of copying files!

When you've successfully done it, I'd be interested to know how it all went and how you ended up doing it - always worth keeping in mind for when I next have to do something similar!

Cheers,

That is what I was thinking of doing, but there has to be a better way to move EVERYTHING over and not have to fuss with it too much.

If nobody has a better idea I'm going to end up doing it that way.

move EVERYTHING over

In the past, I've always used the Copy Database Wizard. On SQL Server 2000, go into Enterprise Manager and it's under Action|All Tasks|Copy Database Wizard.

Note that I've only used this to move single databases, rather than a whole set. Also, I'm not sure if it copies everything, because the databases (while live, production databases) were quite basic, and so moving the tables/queries/stored procedures/etc. was good enough.

Frankly, it's been a while since I used SQL server in anger (database for a call center in Germany). I last used it for a fantasy football league (with web front end, auctions and the rest) at my previous job.

I've found in the past that the Copy Database Wizard can be a little touchy if you have a lot of foreign-key constraints in the database. If the tables aren't created in exactly the right order, some fail to be transferred. It's particularly a problem if you've ended up with vaguely circular references. Detaching and copying the files circumvents that kind of problem entirely.

Fair enough. That's always useful to know.

I've always used the Copy Database Wizard.

I've used this as well, in 2000, it doesn't exist in 7. :/

Frankly, it's been a while since I used SQL server...

Same here. I last used it about 2 years ago working with Cisco Collaboration Server and other Cisco stuff.

The only reason I'm doing this in the first place is because I volunteered (mistake), everyone else was too scared to even touch it (especially the network manager), and we don't have a DBA. Out of all the people here, I have the most SQL Server experience, which is very scary.

used this as well, in 2000, it doesn't exist in 7

Odd, because the last time I used it was version 6.5. I wonder where it went?