SQL Server 2005/2008 user database file default path and default backup path modification method
Last Update:2017-02-28
Source: Internet
Author: User
The following are only references, and may be slightly different if there are multiple instances:
This environment is SQL Server Standard version 64-bit and SQL Server 2008 Standard version 64-bit dual instances installed simultaneously in a
On Windows Server 2008 Standard Version 64-bit OS:
Code
Copy Code The code is as follows:
Windows Server 2008 Standard Version 64-bit
SQL Server Standard Version 64-bit
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\mssql.1\mssqlserver ',
@value_name = ' Defaultdata ',
@DefaultData = @DefaultData OUTPUT
SELECT @DefaultData
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\mssql.1\mssqlserver ',
@value_name = ' Defaultlog ',
@DefaultLog = @DefaultLog OUTPUT
SELECT @DefaultLog
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\mssql.1\mssqlserver ',
@value_name = ' BackupDirectory ',
@BackupDirectory = @BackupDirectory OUTPUT
SELECT @BackupDirectory
To modify the user library data file default path:
EXEC Master.. Xp_regwrite
@rootkey = ' HKEY_LOCAL_MACHINE ',
@key = ' Software\Microsoft\Microsoft SQL server\mssql.1\mssqlserver ',
@value_name = ' Defaultdata ',
@type = ' REG_SZ ',
@value = ' D:\SQL2005\Data '
Modify User library log file default path:
EXEC Master.. Xp_regwrite
@rootkey = ' HKEY_LOCAL_MACHINE ',
@key = ' Software\Microsoft\Microsoft SQL server\mssql.1\mssqlserver ',
@value_name = ' Defaultlog ',
@type = ' REG_SZ ',
@value = ' D:\SQL2005\Log '
To modify the default path for user library backup files:
EXEC Master.. Xp_regwrite
@rootkey = ' HKEY_LOCAL_MACHINE ',
@key = ' Software\Microsoft\Microsoft SQL server\mssql.1\mssqlserver ',
@value_name = ' BackupDirectory ',
@type = ' REG_SZ ',
@value = ' D:\SQL2005\DBBak '
Copy Code The code is as follows:
Windows Server 2008 Standard Version 64-bit
SQL Server 2008 Standard Version 64-bit
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. Sql2008\mssqlserver ',
@value_name = ' Defaultdata ',
@DefaultData = @DefaultData OUTPUT
SELECT @DefaultData
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. Sql2008\mssqlserver ',
@value_name = ' Defaultlog ',
@DefaultLog = @DefaultLog OUTPUT
SELECT @DefaultLog
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. Sql2008\mssqlserver ',
@value_name = ' BackupDirectory ',
@BackupDirectory = @BackupDirectory OUTPUT
SELECT @BackupDirectory
To modify the user library data file default path:
EXEC Master.. Xp_regwrite
@rootkey = ' HKEY_LOCAL_MACHINE ',
@key = ' Software\Microsoft\Microsoft SQL server\mssql10. Sql2008\mssqlserver ',
@value_name = ' Defaultdata ',
@type = ' REG_SZ ',
@value = ' D:\SQL2008\Data '
Modify User library log file default path:
EXEC Master.. Xp_regwrite
@rootkey = ' HKEY_LOCAL_MACHINE ',
@key = ' Software\Microsoft\Microsoft SQL server\mssql10. Sql2008\mssqlserver ',
@value_name = ' Defaultlog ',
@type = ' REG_SZ ',
@value = ' D:\SQL2008\Log '
To modify the default path for user library backup files:
EXEC Master.. Xp_regwrite
@rootkey = ' HKEY_LOCAL_MACHINE ',
@key = ' Software\Microsoft\Microsoft SQL server\mssql10. Sql2008\mssqlserver ',
@value_name = ' BackupDirectory ',
@type = ' REG_SZ ',
@value = ' D:\SQL2008\DBBak '