Change the location of MS SQL Server database files

When you start playing with serious DB, one of the most important parts is to place (if possible) the files in different drives (physical, not only logical partitions!!!). But what if you already have your DB in production with tons of data on the same location? 
This script helps in solving the issue.
It should be executed in three steps: 
1) put the DB offline and change the location reference of the files
2) manually move the files into the new locations
3) put the DB online

Make sure the 'NETWORK SERVICE' user has the permissions to read in the target destinations.

For further assistance, check also:
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
-- 1) Execute this first chunk to put the DB offline ALTER DATABASE MyDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE MyDBName SET OFFLINE; ALTER DATABASE MyDBName MODIFY FILE ( Name = MyDBName_Data, Filename = 'D:\DB\MyDBName.mdf' ); ALTER DATABASE MyDBName MODIFY FILE ( Name = MyDBName_Log, Filename = 'D:\DBLog\MyDBName_log.ldf' ); ALTER DATABASE MyDBName MODIFY FILE ( Name = MyDBName_Idx, Filename = 'E:\DBIndex\MyDBName_log.ndf' ); -- 2) Manually move the files in the right location -- 3) Execute this second chunk to put the DB online ALTER DATABASE my SET ONLINE; ALTER DATABASE my SET MULTI_USER;
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus