Change Default Backup Location on SQL Server

This is just a quick post of a task that seldom needs to happen… Changing the default location of the backup directory.

There is no way to change this setting in SSMS…

The quickest way is to use an extended stored procedure.

[sourcecode language='sql'  padlinenumbers='true']
DECLARE @BackupDirectory VARCHAR(100) 
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', 
  @value_name='BackupDirectory', 
  @BackupDirectory=@BackupDirectory OUTPUT 
SELECT @BackupDirectory

EXEC master..xp_regwrite 
     @rootkey='HKEY_LOCAL_MACHINE', 
     @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', 
     @value_name='BackupDirectory', 
     @type='REG_SZ', 
     @value='\\hsdd1\Litespeed\UAT\HSPSQLHFC01T'
     
DECLARE @BackupDirectory VARCHAR(100) 
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', 
  @value_name='BackupDirectory', 
  @BackupDirectory=@BackupDirectory OUTPUT 
SELECT @BackupDirectory
[/sourcecode]

You may need to run regedit and “find” the BackupDirectory key location on your instance.

For a much more comprehensive post on this topic, please refer to Greg Robidoux’s post.

Stay curious, keep learning…

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>