Programmatically Change the default path for SQL Server Database creation

The extended stored procedure "xp_instance_regwrite" helps you read/write from the registry. You can use this stored procedure to programmatically change the path where the data files for a new SQL Server database gets created by default. Here’s how to use this stored procedure

USE [master]
GO
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
REG_SZ,
N'D:\MyDatabases'
GO

EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
REG_SZ,
N'D:\MyDatabases'
GO 

Note: After running this query, a folder called ‘MyDatabases’ should exist in D drive before a new database is created, else you will get an error.

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: