SQL Server 2005/2008 用户数据库文件默认路径和默认备份路径修改方法

以下仅为参照,如果有多个实例,可能会有些许不同:

本环境是sql server 2005 standard version 64-bit 和 sql server 2008 standard version 64-bit 双实例同时安装在一个

windows server 2008 standard version 64-bit os上:

代码


复制代码 代码如下:

windows server 2008 standard version 64-bit

sql server 2005 standard version 64-bit

查询用户库data文件默认路径:

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

查询用户库log文件默认路径:

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

查询用户库备份文件默认路径:

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

修改用户库data文件默认路径:

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’

修改用户库log文件默认路径:

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’

修改用户库备份文件默认路径:

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’


复制代码 代码如下:

windows server 2008 standard version 64-bit

sql server 2008 standard version 64-bit

查询用户库data文件默认路径:

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

查询用户库log文件默认路径:

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

查询用户库备份文件默认路径:

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

修改用户库data文件默认路径:

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’

修改用户库log文件默认路径:

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’

修改用户库备份文件默认路径:

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’

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐