Archive

Posts Tagged ‘default log path’

SQL Server – How to find Default data and log path for SQL Server 2012

06.05.2013 No comments

To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.

It can be used as below:

SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')

SELECT [Default Log Path]  = SERVERPROPERTY('InstanceDefaultLogPath')

GO

Result Set:

Default Data Path

C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATA

 

(1 row(s) affected)

 

Default Log Path

C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATA

 

(1 row(s) affected)

You can also get this information from Server Properties dialog box in SQL Server Management Studio.

To identify this information for earlier version of SQL Server you need to use xp_instance_regread extended stored procedure which returns data from registry.

 

Earlier I posted on identifying default data directory for multiple instances through registry which returns this information for all instances installed on server.

Hope This Helps!

Vishal

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