DBA Tips Archive for SQL Server

  


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Jeff Hunter, Sr. Database Administrator

Contents

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.

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.

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. The AdventureWorksDW database contains one data file, AdventureWorksDW_Data.mdf, and one log file, AdventureWorksDW_Log.LDF, both of which are currently located in the F:\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 AdventureWorksLT database 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 the AdventureWorksLT 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

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 23-Mar-2013 18:44:58 EDT
Page Count: 11235