SQL Clone
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1890 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 Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144193 Visits: 18651
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
Learn Extended Events

kiril.lazarov.77
kiril.lazarov.77
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1890 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 Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144193 Visits: 18651
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
Learn Extended Events

Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)

Group: General Forum Members
Points: 104076 Visits: 14506
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1890 Visits: 2248
Makes sense.. thanks very much for your replies.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144193 Visits: 18651
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
Learn Extended Events

Robert Davis
Robert Davis
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15581 Visits: 1671
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
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management
Arshpreet
Arshpreet
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 722
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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15581 Visits: 1671
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
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server, Data Platform 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