June 21, 2012 at 9:23 am
Hi all,
Any way to get the default backup path using t-sql? I'd like to use it in a SP.
cheers
June 21, 2012 at 9:25 am
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2012 at 9:36 am
SQLRNNR (6/21/2012)
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'
Thanks.
I'm aware of this but.. What I'm trying to achieve here is to avoid hard coded paths so that SP would be more generic. For instance say I have 5-10 instances on the same server, I need to go and check the path for each and specify the @key parameter? Isn't it possible to pull this from the system catalog?
June 21, 2012 at 9:48 am
It is only stored in the registry. You could record those default paths into a "control" table and use that instead. It may add extra admin overhead, but could be well worth it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2012 at 10:42 am
clayman (6/21/2012)
SQLRNNR (6/21/2012)
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'Thanks.
I'm aware of this but.. What I'm trying to achieve here is to avoid hard coded paths so that SP would be more generic. For instance say I have 5-10 instances on the same server, I need to go and check the path for each and specify the @key parameter? Isn't it possible to pull this from the system catalog?
In case it was not clear in Jason's post, the @key parameter value remain consistent across all of your instances. The proc he is calling does proper registry resolution for us depending on the instance we run it from. That is the beauty of master.sys.xp_instance_regread, as opposed to master.sys.xp_regread which requires the literal path to the registry location you want. If you were using master.sys.xp_regread your calls would need to be different across all of your 5-10 instances.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2012 at 8:14 am
Makes sense.. thanks very much for your replies.
June 22, 2012 at 9:07 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 22, 2012 at 9:30 pm
You should be aware that the registry xp's are undocumented and subject to change at any time as are the paths of the values int eh registry. You can get it via a supported method via SMO. The defualt paths are properties of the Server Class.
June 24, 2012 at 6:46 pm
Thank you all, This is what I was looking for too..
Below is the codes I came up with...
Declare @datadir nvarchar(4000)
,@logdir nvarchar(4000)
,@backupdir nvarchar(4000);
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultData'
, @datadir output;
IF @datadir IS NULL
BEGIN
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\Setup'
, N'SQLDataRoot'
, @datadir output;
END
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultLog'
, @logdir output;
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'BackupDirectory'
, @backupdir output;
SELECT @datadir as Data_directory, ISNULL(@logdir,@datadir) as Log_directory, @backupdir as Backup_directory;
June 24, 2012 at 6:51 pm
Not very relevant any more, and I'm only posting it as a bit of trivia, but there is also a default path for full-text catalogs there.
I wrote a script years ago to run against all of my sQL Servers to make sure the default paths were set according to the standards. I also include calls to xp_create_subdir to make sure the paths I set actually exists.
Declare @DataDir nvarchar(4000),
@LogDir nvarchar(4000),
@BakDir nvarchar(4000),
@FTDir nvarchar(4000),
@DefDataDir nvarchar(4000),
@DefLogDir nvarchar(4000),
@DefBakDir nvarchar(4000),
@DefFTDir nvarchar(4000),
@Instance sysname
Set @Instance = IsNull('\' + Cast(ServerProperty('InstanceName') as sysname), '')
Set @DefDataDir = 'H:\mssql\data' + @Instance
Set @DefLogDir = 'O:\mssql\data' + @Instance
Set @DefBakDir = 'E:\mssql\bak' + @Instance
Set @DefFTDir = 'H:\mssql\FTData' + @Instance
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@DataDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@LogDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@BakDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'FullTextDefaultPath',
@FTDir output,
'no_output'
Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir, FTCatalogs = @FTDir
If IsNull(@DataDir, '') <> @DefDataDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, @DefDataDir
End
If IsNull(@LogDir, '') <> @DefLogDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, @DefLogDir
End
If IsNull(@BakDir, '') <> @DefBakDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, @DefBakDir
End
If IsNull(@FTDir, '') <> @DefFTDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'FullTextDefaultPath', REG_SZ, @DefFTDir
End
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@DataDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@LogDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@BakDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'FullTextDefaultPath',
@FTDir output,
'no_output'
Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir, FTCatalogs = @FTDir
Exec xp_create_subdir @DataDir
Exec xp_create_subdir @LogDir
Exec xp_create_subdir @BakDir
Exec xp_create_subdir @FTDir
June 24, 2012 at 7:44 pm
Robert Davis (6/22/2012)
You should be aware that the registry xp's are undocumented and subject to change at any time as are the paths of the values int eh registry. You can get it via a supported method via SMO. The defualt paths are properties of the Server Class.
Robert Davis (6/24/2012)
Not very relevant any more, and I'm only posting it as a bit of trivia, but there is also a default path for full-text catalogs there.I wrote a script years ago to run against all of my sQL Servers to make sure the default paths were set according to the standards. I also include calls to xp_create_subdir to make sure the paths I set actually exists.
Declare @DataDir nvarchar(4000),@LogDir nvarchar(4000),
@BakDir nvarchar(4000),
@FTDir nvarchar(4000),
@DefDataDir nvarchar(4000),
@DefLogDir nvarchar(4000),
@DefBakDir nvarchar(4000),
@DefFTDir nvarchar(4000),
@Instance sysname
Set @Instance = IsNull('\' + Cast(ServerProperty('InstanceName') as sysname), '')
Set @DefDataDir = 'H:\mssql\data' + @Instance
Set @DefLogDir = 'O:\mssql\data' + @Instance
Set @DefBakDir = 'E:\mssql\bak' + @Instance
Set @DefFTDir = 'H:\mssql\FTData' + @Instance
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@DataDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@LogDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@BakDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'FullTextDefaultPath',
@FTDir output,
'no_output'
Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir, FTCatalogs = @FTDir
If IsNull(@DataDir, '') <> @DefDataDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, @DefDataDir
End
If IsNull(@LogDir, '') <> @DefLogDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, @DefLogDir
End
If IsNull(@BakDir, '') <> @DefBakDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, @DefBakDir
End
If IsNull(@FTDir, '') <> @DefFTDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'FullTextDefaultPath', REG_SZ, @DefFTDir
End
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@DataDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@LogDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@BakDir output,
'no_output'
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'FullTextDefaultPath',
@FTDir output,
'no_output'
Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir, FTCatalogs = @FTDir
Exec xp_create_subdir @DataDir
Exec xp_create_subdir @LogDir
Exec xp_create_subdir @BakDir
Exec xp_create_subdir @FTDir
Ok... I have to ask. Why didn't you use something like SMO for all of that?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 24, 2012 at 7:51 pm
The script I posted pre-dates PowerShell. If I were to write it today, i would use PowerShell and SMO.
June 24, 2012 at 9:28 pm
For what it's worth this PowerShell:
$v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$v.FullName
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ".\SQL2012"
$bkup = $s.Settings.BackupDirectory
$bkup
Yielded this output on my machine:
Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup
And yields this T-SQL when pointed at a SQL2012 instance (attained via quick Profiler Trace):
declare @SmoAuditLevel int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @SmoAuditLevel OUTPUT
declare @NumErrorLogs int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT
declare @SmoLoginMode int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @SmoLoginMode OUTPUT
declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT
declare @SmoMailProfile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName', @SmoMailProfile OUTPUT
declare @BackupDirectory nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT
declare @SmoPerfMonMode int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'Performance', @SmoPerfMonMode OUTPUT
if @SmoPerfMonMode is null
begin
set @SmoPerfMonMode = 1000
end
SELECT
@SmoAuditLevel AS [AuditLevel],
ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],
(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],
ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],
ISNULL(@SmoDefaultLog,N'') AS [DefaultLog],
-1 AS [TapeLoadWaitTime],
ISNULL(@SmoMailProfile,N'') AS [MailProfile],
@BackupDirectory AS [BackupDirectory],
@SmoPerfMonMode AS [PerfMonMode]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply