Planned Relocation Procedure
First, let’s get the information we will need to write the T-SQL script to alter the databases. Use the script below to retrieve the database name, logical name of the file and the physical filename. We will need these when writing the script:
|
1 2 3 4 5 6 7 8 |
SELECT [mf].[database_id] AS 'DatabaseID', DB_NAME(mf.[database_id]) AS 'DB Name', [mf].[name] AS 'Logical Name', [mf].[type_desc] AS 'Type Desc', [mf].[physical_name] AS 'Physical Filename', [mf].[state_desc] AS 'State', [mf].[size] AS 'Size in Bytes' FROM sys.[master_files] [mf] |
Now that we have this information, below is the steps needed to move (or rename) a database file (log or data). Replaces the various fields (database_name, logical_name and new_path\os_file_name) in the script segments below.
1. Make a backup of the database first. You should always do this before making ANY changes to a database.
2. Run the following statement to take the database offline. Adding “WITH ROLLBACK IMMEDIATE” makes it go much faster as all non-qualified transactions are rolled back:
|
1 |
ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE; |
3. Move (physically) the file or files to the new location (or drive).
4. Execute the following statement for each file moved. This will store the updated location in the system tables:
|
1 |
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' ); |
5. Execute the following statement to bring the database back online. If the path is wrong, you will receive an error and the database will remain offline:
|
1 |
ALTER DATABASE database_name SET ONLINE; |
6. Verify the file change by running the following query.
|
1 2 3 |
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>'); |
If steps 5 and 6 succeed, the database was successfully moved to the new location and is available for use.
Here is the full script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
USE master; GO SELECT [mf].[database_id] AS 'DatabaseID', DB_NAME(mf.[database_id]) AS 'DB Name', [mf].[name] AS 'Logical Name', [mf].[type_desc] AS 'Type Desc', [mf].[physical_name] AS 'Physical Filename', [mf].[state_desc] AS 'State', [mf].[size] AS 'Size in Bytes' FROM sys.[master_files] [mf]; -- Run this to set to single user and take offline ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE [MyDatabase] SET OFFLINE; GO --Next you must manually move the files from their current location to the new physical disk location -- (and remember to rename them manually if you changed them in the MODIFY FILE command) ALTER DATABASE [MyDatabase] MODIFY FILE ( NAME = MyDatabase, FILENAME = N'E:\SQLData\MyDatabase.MDF' ); GO -- You can now bring the database back online: ALTER DATABASE [MyDatabase] SET ONLINE; ALTER DATABASE [MyDatabase] SET MULTI_USER; -- Once the the database is back online, check the path for verification SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'MyDatabase'); |
Views – 1941