INF: Moving SQL Server 7.0 Databases to a New Location
ID: Q224071
|
The information in this article applies to:
-
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
The following example moves a database named mydb, which contains one data file, Mydb.mdf, and one log file, Mydblog.ldf. If the database you are moving has additional data or log files, specify all of them in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change no matter how many files the database contains because it does not list them.
- Detach the database as follows:
use master
go
sp_detach_db 'mydb'
go
- Next, copy the data and log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
- 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
Follow the same procedure for moving user databases. For MSDB, make sure the SQL Server Agent is not currently running. Otherwise, the sp_detach_db stored procedure fails with the following message:
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
- 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.
- Right-click the SQL Server in Enterprise Manager and click Properties on the shortcut menu.
- 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 log file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.
- Change these values as follows:
- Remove the current entries for the files Master.mdf and Mastlog.ldf.
- Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
- Stop SQL Server.
- Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
- Restart SQL Server.
Moving the Model Database
To move the model database, SQL Server must be started with trace flag 3608 so that it does not recover any database except the master.
NOTE: You will not be able to access any user databases at this time. You should not perform any operations other than the steps below while using this trace flag.
To add trace flag 3608 as a SQL Server startup parameter, perform the following steps:
- In SQL Server Enterprise Manager, right-click the server name and click Properties on the shortcut menu.
- On the General tab, click Startup Parameters.
- Add a new parameter as -T3608.
After adding trace flag 3608, perform the following steps:
- Stop and restart SQL Server.
- Detach the model database as follows:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf files from D:\Mssql7\Data to E:\Sqldata.
- Reattach the model database as follows:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf',E:\Sqldata\modellog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters box in the Enterprise Manager.
- Stop and restart SQL Server.
You can verify the change in file locations using sp_helpfile:
use model
go
sp_helpfile
go
Moving Tempdb
You can move tempdb files by using the ALTER DATABASE statement.
- 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.
- 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.
- Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
- 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
Last Reviewed: June 16, 1999