Archive for the ‘Database File Relocation’ Category

How to relocate the data and log files for the System databases to different hard disk drives?

Last week you were assigned to administer more than 20 instances of SQL Server after the company has acquired a new company for expansion. After your initial check of the instances, you have found that an important instance that is installed on a physical server has all the system databases located in the C:\ drive. Most likely, it was installed by the previous “Accidental SQL Server DBA” choosing all the default options during the installation process. For unknown reasons, he/she did not get a chance to relocate the system databases data and log files to a separate hard disk drive. All the System databases (master, msdb, model, and tempdb) will by default be located in the C:\ drive unless otherwise specified during installation. (We will discuss the resource, distribution, reportserver, and the reportservertempdb databases in a separate blog.)

Despite your effort in helping the System Administration team and management to understand not to install application software on the same hosts that have SQL Server installed, no avail, as budget, politics, and bureaucracies usually win at the end of the day. Two applications were installed yesterday. The application software installed or to be installed usually also have C:\ drive as the default installation location. After your in-depth research and implementation of negotiation skills and diplomacy, you were able to have the System Administration team and management agree to add extra hard disk drives on the physical server so that you can relocate the System databases and user databases off the C:\ drive. Now is your chance to correct the “Common Man” setup by the predecessors. Let’s do it right this time around.

Preparation steps:

1. You must know that the file relocation process for the master and tempdb databases are different from the msdb and model databases. You may think this is funny as it did not happen to you, I have seen very experienced SQL Server DBAs in emergency troubleshooting bridge calls copied the tempdb data and log files over to different hard disk drives even though they are the same ones who know very well that the tempdb database gets recreated every time SQL Server service restarts. The system databases data and log files relocation process will require a SQL Server service restart, therefore, no need to copy the tempdb data and log files over to separate hard disk drives. To relocate the data and log files for the master database, you will need to browse to the SQL Server Configuration Manager and edit the Startup Parameters values to point to the location for the data and log files.

2. Submit a change request, schedule a maintenance downtime window, and notify all parties for this planned task. Plan this according to your business rules. The time for the approval and the maintenance downtime window will vary.

Once ready, here are the implementation steps:

1. Backup all user and system databases prior to starting this maintenance task. There’s no need to backup the tempdb database, you can’t even if you tried. Please refer to http://www.handsonsqlserver.com/how-to-run-full-backups-for-ad-hoc-emergency-roll-outs/ on how to backup all databases.

2. Capture the current locations of the system databases and output to store in an Excel spreadsheet.

-- Identify the current physical paths of the system databases
select DB_NAME(mf.database_id) as DatabaseName
, type_desc as FileTypeDesc
, name as FileName
, physical_name
from sys.master_files mf
where DB_NAME(mf.database_id) IN ('master', 'msdb', 'model', 'tempdb')

3. Create the folders in the hard disk drives to store the data, log, error log, agent error log files.

4. With the exception of the master database, for each file to be moved, run the TSQL statement to modify the location of the database data and log files.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );

For example:

-- modifying the system databases data and log files locations

USE master
GO
-- msdb
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBData,
FILENAME ='C:\SECOND\MSDBData.mdf'
)
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBLog,
FILENAME ='C:\SECOND\MSDBLog.ldf'
)
-- model
ALTER DATABASE MODEL
MODIFY FILE (NAME = modeldev,
FILENAME ='C:\SECOND\modeldev.mdf'
)
ALTER DATABASE MODEL
MODIFY FILE (NAME = modellog,
FILENAME ='C:\SECOND\modellog.ldf'
)
-- tempdb, no need to copy the tempdb data and log files to the new locations
ALTER DATABASE TEMPDB
MODIFY FILE (NAME = tempdev ,
FILENAME ='C:\SECOND\tempdb.mdf'
)
ALTER DATABASE TEMPDB
MODIFY FILE (NAME = templog,
FILENAME ='C:\SECOND\templog.ldf'

5. To relocate the master database data and log file: Browse to SQL Server Configuration Manager, then click on the SQL Server Services node, right-click on the corresponding SQL Server instance, and then click Properties. Click on the Advanced tab then edit the Startup Parameters values to point to the location for the data, log, and error log files. The click the OK button. Note: Following –d is the data file location, -l is the log file location, and –e is the error log. For example: -dD:\Master\Data\master.mdf;-eE:\ErrorLog\ERRORLOG;-lL:\Master\Log\mastlog.ldf.

6. Stop the SQL Server service.

7. Move the master.mdf, mastlog.ldf, tempdb.mdf, templog.ldf, model.mdf, modellog.ldf, msdbdata.mdf, and msdblog.ldf files to the new locations.

8. Start the SQL Server service.

9. Verify the file locations changes.

-- Identify the current physical paths of the system databases
select DB_NAME(mf.database_id) as DatabaseName
, type_desc as FileTypeDesc
, name as FileName
, physical_name
from sys.master_files mf
where DB_NAME(mf.database_id) IN ('master', 'msdb', 'model', 'tempdb')

10. To change the SQL Server Agent log path: In SQL Server Management Studio, expand SQL Server Agent, right-click Error Logs and click Configure, in the Configure SQL Server Agent Error Logs dialog box, specify the new location for the SQLAGENT.OUT file. For example: D:\SQLServerAgentErrorLog\SQLAGENT.OUT.

11. To change the database default location: In SQL Server Management Studio, right-click the SQL Server server and click Properties. In the Server Properties dialog box, select Database Settings. In Database Default locations, key in or browse to the new location for the data and log files. The click the OK button.

12. Restart the SQL Server service to complete.

REFERENCE:

Moving System Databases
http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx

How to relocate the data and log files for user databases to different hard disk drives?

Your company has acquired a new company in South Florida. You have been requested to fly down to Miami to help with the transition and do a routine check on the SQL Servers in the office. On one of the SQL Servers that resides on a physical server, you have found that even though the server has multiple hard disk drives, the data and log files of the user databases are located in the D:\ drive. You have raised this concern and submitted a change request for a scheduled maintenance window over the weekend to relocate the data and log files to separate hard disk drives that are available in this physical server. The change request has been approved. You will have to work on and finish the data and log files relocation task this Saturday before hitting the beaches. The following steps can help you finish this change request quickly and correctly:

  1. Identify the database(s) you will need to relocate the data and log files and their current physical paths. Save the output to an Excel file.
  2. Take a backup of the database(s) to have the data and log files relocated in case a restore is needed.
  3. Create the corresponding folders for the data and log files in the available hard disk drives for the database(s) you plan to relocate the data and log files and make sure they have sufficient disk space to store the files.
  4. Run the TSQL statement(s) to set the database(s) offline.
  5. Move the data and log files to the new folders you had created.
  6. Run the TSQL statement(s) to modify the file locations for the database(s).
  7. Run the TSQL statement(s) to set the database(s) online.
  8. Verify the files locations changes.

Let’s do it. In this example, we will relocate the data, log, and filestream files for the AdventureWorks2008R2 database:

  1. Identify the database(s) you will need to relocate the data and log files and their current physical paths. Save the output to an Excel file.
  2. -- Identify the current physical paths of the database(s) data, log, and filestream files
    select DB_NAME(mf.database_id) as DatabaseName
    , type_desc as FileTypeDesc
    , name as FileName
    , physical_name
    from sys.master_files mf
    where DB_NAME(mf.database_id) = 'AdventureWorks2008R2' -- use IN for multiple databases
    
  3. Take a backup of the database(s) to have the data and log files relocated in case a restore is needed.
  4. -- take a backup of the database(s)
    BACKUP DATABASE AdventureWorks2008R2
    TO DISK = 'C:\SQLBackups\aw20008r2.bak'
    WITH COMPRESSION
    
  5. Create the corresponding folders for the data and log files in the available hard disk drives for the database(s) you plan to relocate the data and log files and make sure they have sufficient disk space to store the files.
  6. Run the TSQL statement(s) to set the database(s) offline.
  7. -- set the database(s) offline
    ALTER DATABASE AdventureWorks2008R2
    SET OFFLINE
    
  8. Move the data and log files to the new folders you had created.

  9. Run the TSQL statement(s) to modify the file locations for the database(s).
  10. -- modify the file locations for the database(s)
    ALTER DATABASE AdventureWorks2008R2
    MODIFY FILE ( NAME = AdventureWorks2008R2_Data
    , FILENAME = 'C:\AW\Data\AdventureWorks2008R2_Data.mdf'
    )
    ALTER DATABASE AdventureWorks2008R2
    MODIFY FILE ( NAME = AdventureWorks2008R2_Log
    , FILENAME = 'C:\AW\Log\AdventureWorks2008R2_Log.ldf'
    )
    ALTER DATABASE AdventureWorks2008R2
    MODIFY FILE ( NAME = FileStreamDocuments2008R2
    , FILENAME = 'C:\AW\FileStream\Documents2008R2'
    )
    
  11. Run the TSQL statement(s) to set the database(s) online.
  12. -- set the database online
    ALTER DATABASE AdventureWorks2008R2
    SET ONLINE
    
  13. Verify the files locations changes.
  14. -- verify the new physical paths
    select DB_NAME(mf.database_id) as DatabaseName
    , type_desc as FileTypeDesc
    , name as FileName
    , physical_name
    from sys.master_files mf
    where DB_NAME(mf.database_id) = 'AdventureWorks2008R2' -- use IN for multiple databases
    

Now, you can go enjoy your beers under the palm trees.

REFERENCES:

Moving User Databases
http://msdn.microsoft.com/en-us/library/ms345483(v=sql.105).aspx