There are several ways to move ‘User’ database file location.

1. Attach/Detach
2. Backup/Restore
3. ALTER DATABASE MODIFY FILE

1 (Attach/Detach) and 2 (Backup/Restore) are straight forward and are more popular that the 3rd option, which is what I cover in this post (Please note that there are other ways to do this too).

Following script allows to perform move on User databases databases to a different location.

Note: Please make sure you try it on a test server before you implement on a Production machine.

Step 1: First, identify the current location of the database files.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id = DB_ID('DBA')
GO


ALTER DATABASE [DBA]
MODIFY FILE
(
NAME = 'DBA_Primary_Data'
, FILENAME = 'E:\MSSQL_2K5\MDF\DBA_Primary.MDF'
)
GO
ALTER DATABASE [DBA]
MODIFY FILE
(
NAME = 'DBA_Log'
, FILENAME = 'E:\MSSQL_2K5\LDF\DBA_Log.LDF'
)
GO


After running the above script you will see the following message.

Step 3: Stop the Sql Server instance

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id = DB_ID('DBA')
GO


Moving system databases is also similar with a couple of extra steps. We will look at that in the next post.

Hope this helps,

_SqlTimes