Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-sql to get the default backup path?


T-sql to get the default backup path?

Author
Message
kiril.lazarov.77
kiril.lazarov.77
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 2248
Hi all,

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

cheers
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

kiril.lazarov.77
kiril.lazarov.77
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 2248
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?
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
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
kiril.lazarov.77
kiril.lazarov.77
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 2248
Makes sense.. thanks very much for your replies.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Arshpreet
Arshpreet
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 720
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;

Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search