I have SQL Server 7.0 development databases installed in a directory called D:\mssql7. I want to move the user databases, Data Transformation Services (DTS) packages, database diagrams, maintenance plans, logins, and other relevant data to a SQL Server 2000 development box. I've had a hard time creating a SQL Server 2000 mirror image of our SQL Server 7.0 server. SQL Server 2000 tools such as the Database Copy Wizard offer only about 90 percent of the functionality I want. I decided to handle part of the problem—moving the databases—by manually moving data following these steps:
- I installed SQL Server 7.0 on the new server in a directory called D:\mssql7\data.
- I copied the .mdf and .ldf files from the old server into the new directory.
- I upgraded SQL Server 7.0 on the new server to SQL Server 2000.
The manual copy worked and I'm on SQL Server 2000, but the overall directory name is still D:\mssql7—the upgrade didn't rename my directories. How can I rename the directory to D:\mssql7\data without damaging the SQL Server installation?
SQL Server doesn't rename directories as part of the upgrade process because doing so might affect your organization's carefully thought-out corporate standards for directory placement and naming. If you want to rename the directory, you have to relocate the data manually. Microsoft provides two ways to move databases and their log files. Your choice depends on the amount of space available on your new server. If you're short of space, consider using the sp_detach_db and sp_attach_db stored procedures to detach and move the files, then re-attach them by calling sp_attach_db with arguments that specify the new file locations. This method will work for all your user databases but not for the system databases (i.e., master, model, and msdb). SQL Server Books Online (BOL) explains how to specify the new file locations and attach the files. And the Microsoft article "INF: Moving SQL Server Databases to a New Location with Detach/Attach" (http://support.microsoft.com/?kbid=224071) has great step-by-step instructions for completing the move.
If you have plenty of available space or an external storage device to store the backup, you might want to use Enterprise Manager's backup and restore UI. This method lets you back up your database to disk (local or to a network share) or to tape, detach the original database, and restore the database to the new server. BOL explains how to use RESTORE DATABASE and move the file locations. You need to use the backup and restore functionality that BOL describes to move the master (in single-user mode), model, and msdb system databases. If you're using replication, you also need to reset the replication snapshot share before you modify your publications. You can reset this share in the Configure Publishing and Distribution Wizard and in the Create Publication Wizard.