Change SQL backup location

by | Feb 27, 2013 | BizTalk Community Blogs via Syndication | 0 comments

During the installation of a SQL instance, you’ll have the ability to define some folders
These locations can be defined:

  • User database directory
  • User database lof directory
  • Temp DB directory
  • Temp DB log directory
  • Backup directory

%ufeff
Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases. Just %ufeff right click on the server name and select properties, navigate to the Database Settings page. Here you can find a section Database default locations for changing the data and log directories.
%ufeff

But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory.  To find this we need to look in the registry.

Open the registry tool REGEDIT and navigate to following key:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer

Or something similar for your instance of SQL server. The registry key BackupDirectory is the one you’ll need to change to set another default Backup Directory.

%ufeff
Changing the registry can also be done with a T-SQL query command. To do so, you’ll be using the extended stored procedures XP_REGREAD and XP_REGWRITE.

Reading the falue in registry can be done by using this command:

DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey=‘HKEY_LOCAL_MACHINE’,
  @key=‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer’,
  @value_name=‘BackupDirectory’,
  @BackupDirectory=@BackupDirectory OUTPUT SELECT @BackupDirectory

This will result in something similar as this:

Changing the default folder can be done by using the following command

EXEC master..xp_regwrite
     @rootkey=‘HKEY_LOCAL_MACHINE’,
     @key=‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer’,
     @value_name=‘BackupDirectory’,
     @type=‘REG_SZ’,
     @value=‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSTPTST\MSSQL\Backup’

Submit a Comment