Feeds:
Posts
Comments

Posts Tagged ‘Move user databases’

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

Step 2: For each database file, run the following command to indicate to Sql Server that you are making meta data change to a database file.

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.

Confirmation message after running ALTER DATBASE script

Step 3: Stop the Sql Server instance

Step 4: Move the database files to the new location.

Step 5: Restart the Sql Server instance for the user database to start at the new location.

Step 6: Verify the success of the move using the following query.

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

Advertisements

Read Full Post »