Modify the SQL Server default path

/*

Note: If the value returned by the query path is NULL, the default installation path is

C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data

*/

--Query User library data file default path:

DECLARE @DefaultData VARCHAR (100)

EXEC Master.. Xp_regread @rootkey = ' HKEY_LOCAL_MACHINE ',

@key = ' Software\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\mssqlserver ',

@value_name = ' Defaultdata ',

@DefaultData = @DefaultData OUTPUT

SELECT @DefaultData

Go

--Query User library log file default path:

DECLARE @DefaultLog VARCHAR (100)

EXEC Master.. Xp_regread @rootkey = ' HKEY_LOCAL_MACHINE ',

@key = ' Software\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\mssqlserver ',

@value_name = ' Defaultlog ',

@DefaultLog = @DefaultLog OUTPUT

SELECT @DefaultLog

Go

--Query User library backup file default path:

DECLARE @BackupDirectory VARCHAR (100)

EXEC Master.. Xp_regread @rootkey = ' HKEY_LOCAL_MACHINE ',

@key = ' Software\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\mssqlserver ',

@value_name = ' BackupDirectory ',

@BackupDirectory = @BackupDirectory OUTPUT

SELECT @BackupDirectory

Go

--Modify the user library data file default path:

EXEC Master.. Xp_regwrite

@rootkey = ' HKEY_LOCAL_MACHINE ',

@key = ' Software\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\mssqlserver ',

@value_name = ' Defaultdata ',

@type = ' REG_SZ ',

@value = ' C:\test\data '

Go

--Modify User library log file default path:

EXEC Master.. Xp_regwrite

@rootkey = ' HKEY_LOCAL_MACHINE ',

@key = ' Software\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\mssqlserver ',

@value_name = ' Defaultlog ',

@type = ' REG_SZ ',

@value = ' C:\test\log '

--Modify User library backup file default path:

EXEC Master.. Xp_regwrite

@rootkey = ' HKEY_LOCAL_MACHINE ',

@key = ' Software\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\mssqlserver ',

@value_name = ' BackupDirectory ',

@type = ' REG_SZ ',

@value = ' D:\test\backup '

Go

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/