Archive

Posts Tagged ‘default location for database’

SQL Server – Changing Default Database Location for Server

18.03.2013 2 comments

When you create a new database in SQL Server without explicitly specifying database file locations, SQL Server created files in default location. This default location is configured when installing SQL Server.

If you need to change this default location once SQL Server is installed, you can change this in server properties.

Method 1: Change default database location via SQL Server Management Studio:

Step 1. Right Click on Server and Select "Properties".

image

Step 2. in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. You can also change default backup location here.

image

Step 3. Click on "OK" to apply changes.

All new databases will be created to new location unless specified explicitly.

 

Method 2: Change default database location using TSQL Code:

You can also change default database location using TSQL code, use below code to change data, log and backup location:

USE [master]

GO

 

— Change default location for data files

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'DefaultData',

       REG_SZ,

       N'C:MSSQLData'

GO

 

— Change default location for log files

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'DefaultLog',

       REG_SZ,

       N'C:MSSQLLogs'

GO

 

— Change default location for backups

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'BackupDirectory',

       REG_SZ,

       N'C:MSSQLBackups'

GO

Hope This Helps!

Vishal