Moving database files to a different location

Category : Tips User Rating : 4 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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): Move database file locations

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.
Link back to this article : http://www.sqlmatters.com/Articles/Moving database files to a different location.aspx

Keywords

TSQL,database,file,move


Comments
Post by Bull Fulbright on Wed 31 Aug 2016 09:01. Report Inappropriate Post

Great post! Very useful!

You should change the default database path for new databases. The default path is obtained from the Windows registry.

You can also change with T-SQL, for example, to set default destination to: D:\MSSQLData

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQLData'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\MSSQLData'

GO

Extracted from: http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html


Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered