Search This Blog

How to Move User Databases(2005/2008/2012)

How to Move the System Databases(2005/2008/2012)

Contents

  • Introduction
  • Prerequisites
  • Method 1: Taking the Database Offline Method
  • Method 2: Using the Detach/Attach Method

Introduction

Moving a user database in SQL Server is not only a common DBA task, but a fairly simple and straightforward one as well. Just like any administration task, the goal for the DBA should be to use a method with the least interruption to the server and to the database being relocated. This guide presents two methods that can be employed to change the location of the data files and transaction log files for a user database within the same instance using Transact-SQL as part of a planned relocation or scheduled maintenance operation. Although the sample installation for this guide uses SQL Server 2008 R2, instructions will be provided to handle all versions of SQL Server.

  • Taking the Database Offline Method — (SQL Server 2005 and higher)
  • Using the Detach/Attach Method — (All Versions of SQL Server)

Note that this guide presents how to move user databases and not system databases. The steps necessary to move SQL Server system databases are more involved and will differ from the steps used to move user databases. Click here for step-by-step instructions on how to move SQL Server system databases.How to Move the System Databases(2005/2008/2012)

Prerequisites

The following list of prerequisites should be performed regardless of which method you decide on using to move the data files and log files of the user database.

  1. This cannot be stressed enough. Make a current backup of all databases being considered to move from their current location, especially the master database. Making a mistake or suffering a system failure when attempting to make critical changes of this nature can render your database as unusable. Let me say it again, make a current backup!
  2. You must be authenticated to the SQL Server instance with system administrator (sa) permissions.
  3. The procedures in this guide require you to know the logical name and the physical location of all data files and log files of the database being moved. There are two methods that can be used to obtain the file names and locations depending on which version of SQL Server you are using.
    For SQL Server 2005 and higher, query the system-wide view sys.master_files.
    USE [master]
Go

SELECT
    DB_NAME(database_id)  AS "Database Name"
  , type_desc             AS "File Type"
  , name                  AS "Logical File Name"
  , physical_name         AS "Physical File"
  , state_desc            AS "State"
FROM
    sys.master_files
WHERE
    database_id = DB_ID('<database_name>');
Go
    For all versions of SQL Server, you can use the sp_helpfile stored procedure.
    
USE <database_name>
Go

EXEC sp_helpfile
Go
  4. You should have exclusive access to the database that you are moving which simply means that all other users have to be logged out of the database.

Method 1: Taking the Database Offline Method

Out of all of the methods that can be used to move a user database, taking the database offline in order to move its physical files is the most simple, flexible, and least intrusive of them all. It does, however, require that are using SQL Server 2005 or higher. The reason why this method is favorable over the detach/attach method is that unlike taking a database offline, detaching a database will loose certain database properties that will need to be re-applied after re-attaching it (for example, cross-database ownership chaining).
The example demonstrated in this section moves one the AdventureWorks sample databases named AdventureWorksDW to a different directory location in the same SQL Server instance using the database offline method. TheAdventureWorksDW database contains one data file, AdventureWorksDW_Data.mdf, and one log file, AdventureWorksDW_Log.LDF, both of which are currently located in theF:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA directory path.

SELECT
    DB_NAME(database_id)  AS "Database Name"
  , type_desc             AS "File Type"
  , name                  AS "Logical File Name"
  , physical_name         AS "Physical File"
  , state_desc            AS "State"
FROM
    sys.master_files
WHERE
    database_id = DB_ID('AdventureWorksDW');
Go

Database Name     File Type  Logical File Name      Physical File                                                         State
----------------- ---------- ---------------------- --------------------------------------------------------------------- ------
AdventureWorksDW  ROWS    AdventureWorksDW_Data  
F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data.mdf  ONLINE
AdventureWorksDW  LOG        AdventureWorksDW_Log   
F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Log.LDF   ONLINE

The data file will be relocated to G:\MSSQL\Data while the log file will be moved to L:\MSSQL\Log.

  1. The first step is to take the database offline. Although not mandatory, this operation should be performed while connected to the master database (USE master) and not from the database being moved. Once the command finishes, it will try to set the connection back to the database you were connected to. If you are connected to the database being moved, you will receive a warning message similar to the following because you just took the database offline:
    Failed to restart the current database. The current database is switched to master.
    This is obviously a benign warning since SQL Server will simply switch you to the master database anyway.
    USE [master]
Go

ALTER DATABASE [AdventureWorksDW] SET OFFLINE;
Go
  2. Next, physically move the data files and the log files from the current location (F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA) to the new location (G:\MSSQL\Data for data files and L:\MSSQL\Log for log files).
    [VMWINDOWS1] C:\> 
move F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data.mdf G:\MSSQL\Data\
        1 file(s) moved.

[VMWINDOWS1] C:\> 
move F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Log.LDF L:\MSSQL\Log\
        1 file(s) moved.
  3. Next, update the SQL Server system catalog to modify the physical data file and log file mappings to their logical file name. The new path will be used when the database is restarted or brought back online.
    USE [master]
Go

ALTER DATABASE [AdventureWorksDW]
    MODIFY FILE (NAME = 'AdventureWorksDW_Data', FILENAME = 'G:\MSSQL\Data\AdventureWorksDW_Data.mdf');
Go

ALTER DATABASE [AdventureWorksDW]
    MODIFY FILE (NAME = 'AdventureWorksDW_Log', FILENAME = 'L:\MSSQL\Log\AdventureWorksDW_Log.LDF');
Go
  4. Open the database up for user connections again by bringing it back online.
    USE [master]
Go

ALTER DATABASE [AdventureWorksDW] SET ONLINE;
Go
  5. Verify the change in file locations by querying the sys.master_files system-wide view.
    SELECT
    DB_NAME(database_id)  AS "Database Name"
  , type_desc             AS "File Type"
  , name                  AS "Logical File Name"
  , physical_name         AS "Physical File"
  , state_desc            AS "State"
FROM
    sys.master_files
WHERE
    database_id = DB_ID('AdventureWorksDW');
Go

Database Name     File Type  Logical File Name      Physical File                            State
----------------- ---------- ---------------------- ---------------------------------------- ------
AdventureWorksDW  ROWS     AdventureWorksDW_Data  G:\MSSQL\Data\AdventureWorksDW_Data.mdf  ONLINE
AdventureWorksDW  LOG        AdventureWorksDW_Log   L:\MSSQL\Log\AdventureWorksDW_Log.LDF    ONLINE

 The steps described above explain how to rename the physical database files. While not required, it is also possible to change the logical name of the database file using the ALTER DATABASE command.

USE [master]
Go

ALTER DATABASE [AdventureWorksDW]
    MODIFY FILE (NAME = 'AdventureWorksDW_Data', NEWNAME = 'AdventureWorksDW_Test_Data');
Go

ALTER DATABASE [AdventureWorksDW]
    MODIFY FILE (NAME = 'AdventureWorksDW_Log', NEWNAME = 'AdventureWorksDW_Test_Log');
Go

Method 2: Using the Detach/Attach Method

This method is one of the holdovers from SQL Server 7.0 and 2000 and is commonly used because it works for any Microsoft SQL Server database version (SQL Server 2012, SQL Server 2008, SQL Server 2005, SQL Server 2000, or SQL Server 7.0). The major drawback with this method is that detaching a database in SQL Server loses certain database properties like cross database ownership chaining because these settings are not stored in the database itself, but rather in the meta data contained in the master database. Because of this, you will have to remember to manually re-apply those settings when re-attaching the database. You should also be aware that any users that have that database set as their default database will now be given master as their default database. This could have serious consequences for both applications and users that are expecting their database to be set when they log in.
Another reason why this method is used is that it's the only solution when moving files from one server to another. However, if you are moving files around in the same SQL Server instance and you are running SQL Server 2005 or above, you should consider using the Database Offline method in order to preserve your settings and accomplish the move. Using the Database Offline method merely changes the state value in the sys.databases table in the master database (state=6, state_desc=OFFLINE) while detaching a database actually removes the entry for that database.
Detaching a database is similar to dropping a database as far as SQL Server is concerned. After a database has been detached, no meta data for that database will exist within SQL Server with the only possible exception of recent backup and restore history contained in the msdb database. Although SQL Server does include the DROP DATABASE command, it should not be used to move a database since dropping a database removes the physical files from the operating system. Unless you have a backup, the database is essentially gone.
Finally, note the following restrictions before attempting to detach a database. You cannot detach a database if any of the following conditions exist:

  1. Database is a system database
  2. Database is being mirrored
  3. Database is being published through replication
  4. The database has a snapshot

The following example moves one the AdventureWorks sample databases named AdventureWorksLT to a different directory location in the same SQL Server instance using the Detach/Attach method. The AdventureWorksLTdatabase contains one data file, AdventureWorksLT_Data.mdf, and one log file, AdventureWorksLT_Log.ldf, both of which are currently located in the F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA directory path.

 
USE [AdventureWorksLT]
Go

EXEC sp_helpfile
Go

name                   fileid filename                                                              }
---------------------- ------ --------------------------------------------------------------------- } <SNIP>
AdventureWorksLT_Data  1      F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Data.mdf  } 
AdventureWorksLT_Log   2      F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Log.ldf   }

The data file will be relocated to G:\MSSQL\Data while the log file will be moved to L:\MSSQL\Log.


  1. Check for any database properties that may be enabled for the database being detached. For example, I will enable cross database ownership chaining on the AdventureWorksLT database to explain how this process works. Later in this example when the database is re-attached, these properties will need to be manually enabled.
    USE [master]
Go

ALTER DATABASE [AdventureWorksLT] SET DB_CHAINING ON;
Go
    Next, verify that cross database ownership chaining is enabled by querying sys.databases. The is_db_chaining_on column should be 1 which indicates that cross database ownership chaining is indeed enabled. A value of 0 would indicate that the property is not enabled.
    SELECT
    DB_NAME(database_id)  AS "Database Name"
  , is_db_chaining_on     AS "Database Chaining Enabled?"
FROM
    sys.databases
WHERE
    database_id = DB_ID('AdventureWorksLT');
Go

Database Name      Database Chaining Enabled?
------------------ --------------------------
AdventureWorksLT   1
  2. Detach the database.
    USE [master]
Go

EXEC sp_detach_db @dbname = 'AdventureWorksLT', @skipchecks = 'true'
Go
    The sp_detach_db stored procedure above was executed with two parameters:
    @dbname – is the name of the database you are detaching. Although not required, I included this as a named parameter for the sake of clarity.
    @skipchecks – tells SQL Server whether or not you want to skip or run UPDATE STATISTIC for the database being detached. Valid values are 'true' or 'false'. The default value is NULL which means UPDATE STATISTICS is performed to update information about the data in the tables and indexes in the SQL Server 2005 Database Engine and later versions. To skip UPDATE STATISTICS, specify 'true'. To explicitly run UPDATE STATISTICS, specify 'false'. In most cases, you can set this to 'true' because most often, there is no need to update statistics when detaching a database. Updating statistics can take a significant time to complete (depending on the size of the database) and can be performed at a later time after re-attaching the database. On the other hand, explicitly performing UPDATE STATISTICS is useful for databases that will be moved to read-only media.
  3. Next, move the data files and the log files from the current location (F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA) to the new location (G:\MSSQL\Data for data files and L:\MSSQL\Log for log files).
    [VMWINDOWS1] C:\> 
    move F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Data.mdf G:\MSSQL\Data\
        1 file(s) moved.

[VMWINDOWS1] C:\> 
    move F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Log.ldf L:\MSSQL\Log\
        1 file(s) moved.
  4. Re-attach the database and point to the files in the new location using the CREATE DATABASE command with the FOR ATTACH option.
    USE [master]
Go

CREATE DATABASE [AdventureWorksLT]
    ON (NAME = 'AdventureWorksLT_Data', FILENAME = 'G:\MSSQL\Data\AdventureWorksLT_Data.mdf'),
       (NAME = 'AdventureWorksLT_Log',  FILENAME = 'L:\MSSQL\Log\AdventureWorksLT_Log.ldf')
FOR ATTACH
Go
    If the database that you are moving has multiple data files and/or log files being relocated, specify them in a comma-delimited list to the CREATE DATABASE command as shown above. When re-attaching the database, you only need to specify the primary file to the database (.mdf) and any transaction log files (.ldf) or additional data files (.ndf) that have changed location. The primary file is the first data file created and contains information about the location of all other files for the database. All data files for the database must be available whether or not they are specified in the CREATE DATABASE command.
     SQL Server still includes the sp_attach_db stored procedure to re-attach a database, however, this is deprecated and not recommended in SQL Server 2005 and higher. One of the main reasons it's being deprecated is because you can only specify up to 16 files, unlike the CREATE DATABASE command which allows you to specify up to 32,767 files and 32,767 file groups for each database.
    The following sp_attach_db example performs the same actions as the CREATE DATABASE command above and is only included here for the sake of completeness.

    EXEC sp_attach_db
    @dbname = 'AdventureWorksLT'
  , @filename1 = 'G:\MSSQL\Data\AdventureWorksLT_Data.mdf'
  , @filename2 = 'L:\MSSQL\Log\AdventureWorksLT_Log.ldf'
  5. After re-attaching the database, verify the change in file locations by using the sp_helpfile stored procedure.
    USE [AdventureWorksLT]
Go

EXEC sp_helpfile
Go

name                   fileid filename                                  }
---------------------- ------ ----------------------------------------- } <SNIP>
AdventureWorksLT_Data  1      G:\MSSQL\Data\AdventureWorksLT_Data.mdf   }
AdventureWorksLT_Log   2      L:\MSSQL\Log\AdventureWorksLT_Log.ldf     }
  6. Finally, re-enable any database properties that were set before the database was detached. Remember at the beginning of this section that we enabled the cross database ownership chaining property on theAdventureWorksLT database. Because we detached the database, this setting was lost.
    SELECT
    DB_NAME(database_id)  AS "Database Name"
  , is_db_chaining_on     AS "Database Chaining Enabled?"
FROM
    sys.databases
WHERE
    database_id = DB_ID('AdventureWorksLT');
Go

Database Name      Database Chaining Enabled?
------------------ --------------------------
AdventureWorksLT   0
    Run the following ALTER DATABASE command to re-enable the cross database ownership chaining property for the AdventureWorksLT database and then verify the results.
    USE [master]
Go

ALTER DATABASE [AdventureWorksLT] SET DB_CHAINING ON;
Go

SELECT
    DB_NAME(database_id)  AS "Database Name"
  , is_db_chaining_on     AS "Database Chaining Enabled?"
FROM
    sys.databases
WHERE
    database_id = DB_ID('AdventureWorksLT');
Go

Database Name      Database Chaining Enabled?
------------------ --------------------------
AdventureWorksLT   1