DBA

SQL Server 2012 : How to change default directory for Data, Log and Backup files.

by Ashish Upadhyay

Changing default location for Data directory (for MDF or NDF) and Log directory (for LDF) is a normal activity for any DBA who manage disk space. Below is the two ways to do this either by graphical ways to through SSMS or the script using system stored procedure “xp_instance_regwrite”. Changing the backup directory was not in the SQL Server 2008 or even SQL Server 2008 R2 but Microsoft has included this in SQL Server 2012 (code name Denali, now published as Release Candidate).

Steps to modify default settings for location of DATA, LOG and BACKUP files graphically:

1.     Open SQL Server Management Studio (SSMS)

2.     Right click on SSMS and then click on "Properties"

3.     Click on the "Database Settings" page of the Server Properties window

4.     Specify your new default locations for DATA, LOG and BACKUP files.
5.     Click OK to finish.

Script to modify default settings for location of DATA, LOG and BACKUP files by script:

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'S:\SQL_Data'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'L:\SQL_LOG'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'D:\SQL_BACKUP'

GO

Below script can be used to get DATA, LOG and BACKUP directories.
This script is applicable for SQL Server 2008, SQL Server 2008 R2, SQL Server code name "Denali" and SQL Server 2012 (Release Candidate).

USE [master]GO 

DECLARE @DataDirectory nvarchar(4000),        @LogDirectory nvarchar(4000),        @BackupDirectory nvarchar(4000)

EXEC master.dbo.xp_instance_regread  N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DataDirectory output

EXEC master.dbo.xp_instance_regread  N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @LogDirectory output

EXEC master.dbo.xp_instance_regread  N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDirectory output

SELECT @DataDirectory AS DataDirectory, @LogDirectory AS LogDirectory, @BackupDirectory AS BackupDirectory

GO