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.
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).
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
No comments:
Post a Comment