<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Moving Database files by Offline method in SQL 2005

Published on
9,607 Points
3,607 Views
Last Modified:
Approved
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
 ALTER DATABASE dbadb SET OFFLINE 

Open in new window

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 

Open in new window

modify fileYou 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   

Open in new window


onlineBefore 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 

Open in new window



 
  ALTER DATABASE dbadb SET MULTI_USER WITH ROLLBACK IMMEDIATE 

Open in new window


   
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.
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.
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
0
Comment
0 Comments

Featured Post

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month