INF: Moving SQL Server 7.0 Databases to a New Location

• Microsoft SQL Server version 7.0

SUMMARY

This article describes how to change the location of the data and log files for any SQL Server 7.0 database.

MORE INFORMATION

The steps involved in changing the location for some SQL Server system databases is not the same as for user databases. These special cases are described separately.

All of the examples in this article assume that SQL Server 7.0 is installed in the D:\Mssql7 directory with all database and log files located in the default directory D:\Mssql7\Data. The examples move the data and log files for all the databases to E:\Sqldata.

Prerequisites

• Make a current backup of all databases, especially master, from their current location.



• You must have system administrator (sa) privileges.



• You must know the name and current location of all data and log files for the database.
  
  NOTE: You can determine the name and current location of all files used by a database with the sp_helpfile stored procedure:




use <database_name>
go
sp_helpfile
go 

• You should have exclusive access to the database being moved.


If you encounter problems during the process and are unable to access a database you have moved, or are unable to start SQL Server, check the SQL Server error log and SQL Server 7.0 Books Online for details on the errors encountered.

Moving User Databases

1. Detach the database as follows:




   use master
   go
   sp_detach_db 'mydb'
   go 

2. Next, copy the data and log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).



3. Re-attach the database pointing to the files in the new location as follows:




  use master
  go
  sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
  go 

Verify the change in file locations using sp_helpfile:

   use mydb
   go
   sp_helpfile
   go 

The filename column values should reflect the new locations.

Moving MSDB, Pubs, and Northwind

Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'msdb' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Moving the Master Database

1. Change the path for the master data and log files in SQL Server Enterprise Manager.
   
   NOTE: You can optionally change the location of the error log here as well.



2. Right-click the SQL Server in Enterprise Manager and click Properties on the shortcut menu.



3. Click the Startup Parameters button and you will see the following entries:

   
      -dD:\MSSQL7\data\master.mdf
      -eD:\MSSQL7\log\ErrorLog
      -lD:\MSSQL7\data\mastlog.ldf 

   -d is the fully qualified path for the master database data file.
   
   -e is the fully qualified path for the error log file.
   
   -l is the fully qualified path for the master database log file.



4. Change these values as follows:



1. Remove the current entries for the files Master.mdf and Mastlog.ldf.



2. Add new entries specifying the new location:




      -dE:\SQLDATA\master.mdf
      -lE:\SQLDATA\mastlog.ldf 

5. Stop SQL Server.



6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).



7. Restart SQL Server.

Moving the Model Database

1. In SQL Server Enterprise Manager, right-click the server name and click Properties on the shortcut menu.



2. On the General tab, click Startup Parameters.



3. Add a new parameter as -T3608.

1. Stop and restart SQL Server.



2. Detach the model database as follows:




   use master
   go
   sp_detach_db 'model'
   go 

3. Move the Model.mdf and Modellog.ldf files from D:\Mssql7\Data to E:\Sqldata.



4. Reattach the model database as follows:




   use master
   go
   sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
   go 

5. Remove the -T3608 trace flag from the startup parameters box in the Enterprise Manager.



6. Stop and restart SQL Server.


You can verify the change in file locations using sp_helpfile:

   use model
   go
   sp_helpfile
   go 

Moving Tempdb

1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:




use tempdb
go
sp_helpfile
go 

The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.


2. Use the ALTER DATABASE statement, specifying the logical file name as follows:




use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\tempdb.ldf')
go 

You should receive the following messages confirming the change:

File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.

3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.



4. Stop and restart SQL Server.

Additional query words: Moving database files new location move place

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo