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.
comments
2 Responses to "Programmatically Change the default path for SQL Server Database creation"didn't work dude........am using sql server 2008 and no 'mydatabases' folder created in 'd:'.. :-(
I think it's the wording of the poster's comments. The implication is that you need to create the folder, or you'll get an error.
Post a Comment