Sometimes it is necessary to relocate the physical files that comprise a SQL Server database.
The recommended approach to moving the location of database files is using ALTER DATABASE commands.
Here we list the steps required to do this, using the AdventureWorks database as an example.
You might want to take a backup of
the database before moving, just in case !
To determine the current location of the files run the following :
SELECT
name,physical_name
FROM AdventureWorks.sys.database_files
This lists all the data and log files associated with the database, together with their logical
names (in this case there is one data and one log file):
Now that we know the logical names of the files we can set up the new locations of each file, using
ALTER DATABASE :
ALTER DATABASE AdventureWorks
MODIFY
FILE (NAME = AdventureWorks_Data,
FILENAME = 'D:\MSSQL\Data\AdventureWorks_Data.mdf');
ALTER DATABASE AdventureWorks
MODIFY
FILE (NAME = AdventureWorks_Log,
FILENAME = 'D:\MSSQL\Log\AdventureWorks_Log.ldf');
This tells SQL Server where to find each file once they have been moved. Note that we haven’t
actually moved the file at this stage, though the folders do need to exist or there will SQL Server
will generate an error. You then need to take the database offline. To do this there must be no connections
to it, you can check this using sp_who2 :
exec sp_who2
Once all connections have been removed you can then set the database to offline, using the following
command (or right click on the database in SSMS and select ‘Take Offline’).
ALTER DATABASE
AdventureWorks SET OFFLINE
You can then physically move the file using Windows Explorer or similar. If you didn’t set the
database to offline you will get an error message that the files are in use.
Once the files have been moved set the database back online, using the following SQL
(or using SSMS, as before) :
ALTER DATABASE
AdventureWorks SET ONLINE
The database should now be up and running again, with the files in the new location !
Postscript
As an alternative to using the ALTER DATABASE command many DBAs utilise an older method
using stored procedures sp_detach_db and sp_attach_db, however Microsoft have announced that this
will be withdrawn in a future version of SQL Server, so this method is not recommended.