The default backup path...where does SQL keep it?

Question

Post reply

The default backup path...where does SQL keep it?

Lowell

SSC Guru

Points: 323518
More actions
March 10, 2011 at 7:02 am

#236551

I'm trying to do some nice "response" scripting of various issues based on the BLITZ! 60 Minute Server Takeovers by Brent Ozar;
one of his pointers has to to do with finding any database that is in recovery mode "FULL", but has no log backups.
/*
Transaction log backups - do we have any databases in full recovery mode
that haven't had t-log backups? If so, we should think about putting it in
simple recovery mode or doing t-log backups.
*/
/*--Results
namerecovery_modelrecovery_model_desc
LOWELLDEM 1 FULL
SANDBOX02012011 1 FULL
*/
SELECT d.name, d.recovery_model, d.recovery_model_desc
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'L'
WHERE d.recovery_model IN (1, 2) AND b.type IS NULL AND d.database_id NOT IN (2, 3)
ok, so in my case, that script produces results for one or more databases... so i figured i could script out the backup log command.
By going to Tasks...backup...change to log backup...then right clicking in SSMS and then clicking script, i get a nice model; but if I'm going to script this out, i need to discover the backup path from some table in the database:
in this case, on this server, it happens to be D:\SQLServer\MSSQL.1\MSSQL\Backup\
BACKUP LOG [SANDBOX02012011] TO DISK = N'D:\SQLServer\MSSQL.1\MSSQL\Backup\SANDBOX02012011.bak'
WITH NOFORMAT, NOINIT,
NAME = N'SANDBOX02012011-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
where can i find that default folder? do i have to go to the registry, or is it in memory or a system table somewhere?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It should be:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\BackupDirectory

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Ah! Found it:

http://www.mssqltips.com/tip.asp?tip=1583

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 3

darn; both that registry key and the that script on SQL tips assumes you know WHICH installation you are looking for already...MSSQLSERVER,MSSQL.1,MSSQL.2, etc;

I wanted it to be a bit more dynamic, to discover the full path;

DECLARE @BackupDirectory VARCHAR(100)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer',

@value_name='BackupDirectory',

@BackupDirectory=@BackupDirectory OUTPUT

SELECT @BackupDirectory

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

do you think looking at sys.sysaltfiles fo rthe master database, and assuming there is a parallel \Backup folder at the same level as the \DATA folder is safe?

--C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf

select filename from sys.sysaltfiles where name = 'master' and filename like '%.mdf'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

OK, here it is:

declare @rc int, @dir nvarchar(4000)

exec @rc = master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'BackupDirectory',

@dir output, 'no_output'

select @dir AS BackupDirectory

Found at StackOverflow: http://stackoverflow.com/questions/505007/sql-server-how-to-select-the-installation-path

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 6

beautiful; exactly what i was looking for; thanks so much, Gianluca!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

spaghettidba

SSC Guru

Points: 105732

March 10, 2011 at 7:37 am

#1297099

You're welcome!

-- Gianluca Sartori