Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-sql to get the default backup path? Expand / Collapse
Author
Message
Posted Thursday, June 21, 2012 9:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 270, Visits: 1,607
Hi all,

Any way to get the default backup path using t-sql? I'd like to use it in a SP.

cheers
Post #1319398
Posted Thursday, June 21, 2012 9:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 21,092, Visits: 14,767
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1319401
Posted Thursday, June 21, 2012 9:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 270, Visits: 1,607
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?
Post #1319414
Posted Thursday, June 21, 2012 9:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 21,092, Visits: 14,767
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1319421
Posted Thursday, June 21, 2012 10:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, June 27, 2014 5:59 AM
Points: 7,079, Visits: 12,567
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
Post #1319461
Posted Friday, June 22, 2012 8:14 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 270, Visits: 1,607
Makes sense.. thanks very much for your replies.
Post #1319960
Posted Friday, June 22, 2012 9:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 21,092, Visits: 14,767
You're welcome.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1320020
Posted Friday, June 22, 2012 9:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:57 PM
Points: 1,612, Visits: 1,531
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.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1320224
Posted Sunday, June 24, 2012 6:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 130, Visits: 603
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;
Post #1320410
Posted Sunday, June 24, 2012 6:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:57 PM
Points: 1,612, Visits: 1,531
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





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1320413
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse