How to copy data from backup or data files to new SQL installation?

Hi,

I have(had) an old Win2k Server server with about 30 web site databases
(SQL 2000) that just went under due to hardware problems. Thankfully, I
have backups of all the databases plus the MDF and LDF files from the
hard drive.

I want to move all of these sites and their data to a newer server
(Win2003) running SQL2000.

What's the best way to copy the database from the old server hard drive
(now mounted as an extrnal drive to a local machine; I'm currently
FTPing all of the web site directories from it to the new server)?

Just upload the original data to the new server and then mount the MDF
and LDF files within the new SQL server? Or do I restore the backup
files in the new SQL2000?

All of my previous data migrations have been DTS operations from one
live server to another, so no experience with either of the above
scenarios. I'll certainly have a lot more experience at one of them by
the time this weekend is through.

Thanks for any help you can offer.

Byron (spamagnet@dorrk.com) writes:
> I have(had) an old Win2k Server server with about 30 web site databases
> (SQL 2000) that just went under due to hardware problems. Thankfully, I
> have backups of all the databases plus the MDF and LDF files from the
> hard drive.
>
> I want to move all of these sites and their data to a newer server
> (Win2003) running SQL2000.
>
> What's the best way to copy the database from the old server hard drive
> (now mounted as an extrnal drive to a local machine; I'm currently
> FTPing all of the web site directories from it to the new server)?
>
> Just upload the original data to the new server and then mount the MDF
> and LDF files within the new SQL server? Or do I restore the backup
> files in the new SQL2000?

There are two ways to go:

1) Copy the MDF and LDF files to the local disk of the new server,
and use sp_attach_db to attch them. (You can also do this from
Enterprise Manager, but since you have about 30 databases, it
much better to do this from a query window, as you can write a
script, so that you can see exactly what you are about to do.)

2) Restore the backup files. Again, I recommend doing this from a
script. Note that you don't have to create the database in advance.
This is a little more laboursome, since you need to know the
logical name of the database files. These can be retrieved with
RESTORE FILELISTONLY. If all databases are created in the same
way, you may be able to guess the names.

Since you have had hardware problems, I would recommend that you are
prepared to go both ways. The MDF/LDF are likely to be fresher than
the backups (but you know when you took the backups). I'm a little
nervous, though, that if the server crashed the last thing it did,
that the file maybe damaged. But if you have both MDF and LDF, you
should be safe. And, oh, keep an eye for NDF files, that is secondary
data files, in case you have any.

Once you have the databases in place, you still have to sort out logins
and users. I assume that you readd logins to the new servers in some
way. In each database, there are a couple of users, and typically the
login "joe" mapped to the login "joe" on the old server. When you
restore the database on the new server, all this will be broken, so
that the login "joe" maps to the user "anne", and some users do not
map at all. The procedure sp_change_users_login can be used for this.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx