Browse All Articles > Moving Database files by Offline method in SQL 2005
Moving Database files by Offline method
-Prabhakaran
This document will show you how to move the database files without detaching the databases as we do in SQL 2000.
This method will be applicable for only SQL 2005\SQL 2008 databases
To move files, we have to specify the current logical name of the file and the new file path, which includes the new file name.
I would like to tell important point in this method we can move only once file at a time in this manner.
To move data or log files to new location, follow these steps
1. Take a sp_helpfile output of the database, which will provide the logical file name of the data and log files.
![sp_help]()
2. Set the database you want to work with offline by doing below
![offline]()
3. Move one file at a time to the new location
In Windows Explorer physically move the file to the new location, and then tell the SQL Server Database where that file now lives.
![modify file]()
You can move only one file at a time, So if you want to move the more than one file like data and log files, first move the file using the step3 described above then repeat the same step for other files of the database.
4. Set back the database to online
![online]()
Before bringing the database to offline there should be no user connections to database either you can do this by killing all the connections or by issueing the following statement
5. Verify the move by checking sp_helpfile output of the database again and you can observe the new path.
![verify]()
See the highlighted area in Red, It shows the current file path.
Hope this simple tip will help you guys.
Disclaimer - This is the way I used to move the files in our environment and it works perfect, But you should test this method before applying on the Production systems
-Prabhakaran
This document will show you how to move the database files without detaching the databases as we do in SQL 2000.
This method will be applicable for only SQL 2005\SQL 2008 databases
To move files, we have to specify the current logical name of the file and the new file path, which includes the new file name.
I would like to tell important point in this method we can move only once file at a time in this manner.
To move data or log files to new location, follow these steps
1. Take a sp_helpfile output of the database, which will provide the logical file name of the data and log files.
2. Set the database you want to work with offline by doing below
ALTER DATABASE dbadb SET OFFLINE 
3. Move one file at a time to the new location
In Windows Explorer physically move the file to the new location, and then tell the SQL Server Database where that file now lives.
ALTER DATABASE dbadb
MODIFY FILE(NAME=dbadb,FILENAME='E:\Data\MSSQL\dbadb.mdf')
GO 
You can move only one file at a time, So if you want to move the more than one file like data and log files, first move the file using the step3 described above then repeat the same step for other files of the database.
4. Set back the database to online
ALTER DATABASE dbadb SET ONLINE 
Before bringing the database to offline there should be no user connections to database either you can do this by killing all the connections or by issueing the following statement
ALTER DATABASE dbadb SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE dbadb SET MULTI_USER WITH ROLLBACK IMMEDIATE 5. Verify the move by checking sp_helpfile output of the database again and you can observe the new path.
See the highlighted area in Red, It shows the current file path.
There are significant advantages of this method which I feel is,
There is no possibility of dbid mismatch in this method; change in dbid will cause login failure for the logins which have default db as the db you have detached.
There is no possibility of dbid mismatch in this method; change in dbid will cause login failure for the logins which have default db as the db you have detached.
The database owner will not change but in detach and attach method you need to record db owner before detaching database to exactly match the db owner while attaching database.
Cross database ownership chaining property will be retained in this method where as in detach and attach method this property will be lost while you attach the database.
This method seems faster than detach and attach method
Hope this simple tip will help you guys.
Disclaimer - This is the way I used to move the files in our environment and it works perfect, But you should test this method before applying on the Production systems
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)