Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Changing the default SQL Server backup folder


By:   |   Read Comments (26)   |   Related Tips: More > Backup

Problem

When you install SQL Server the path for the installation is generally something such as the following: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL. In this directory there are also folders for your DATA files and also your BACKUP files. Unless specified, the backup location uses the same base path as your database files.  In this tip we look at how to change the default backup location.

Solution

The directories for the default data files, log files and backups are stored in the system registry. With SSMS you had the ability to change the backup location, but this was removed from SSMS for SQL 2005 and was later put back into SSMS with a later release of SQL Server. 

Using SSMS to Change Backup Location

Here is what SSMS for SQL Server 2005 looks like where you only have the Data and Log locations on the Database Settings page.

Here is what this looks like for SQL Server 2014. You can see the Backup location is available from SSMS, so if you have this option available you can change the default backup directory using SSMS.

Using Registry to Change Backup Location

If we open the registry using REGEDIT or some other tool and if you navigate to this key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer or similar for your instance of SQL Server we can see the following information. The highlighted line below shows the default directory for the backups for this one instance.

If you want to change the value you can edit the registry value and save the new location. So for this example let's change it to 'D:\SQLBackups'.

If I do a backup using SQL Management Studio and click on "Add" for a new destination file the following screen shows the default folder has now changed to 'D:\SQLBackups'.

Using Extended Stored Procedures to Change Backup Location

Another way to change the registry is to using the extended stored procedures XP_REGREAD and XP_REGWRITE.

To read the value from the registry you can issue the following command (make sure the key value matches the correct SQL Server installation 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 

This will provide the following output, since we changed the value above directly in the registry.

If we want to change the folder back to the default we can use the following command and specify the path in the value parameter:

EXEC master..xp_regwrite 
@rootkey='HKEY_LOCAL_MACHINE', 
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer', 
@value_name='BackupDirectory', 
@type='REG_SZ', 
@value='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup' 

If we run this command again we can see that this has changed:

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 

To determine where SQL Server is installed you can right click on the server name, select Properties. The root directory highlighted below will show you the corresponding install name for the instance such as "MSSQL2", so you know which registry entry needs to be changed.

That's all there is to it. Save yourself some time and change the default backup location instead of changing it for each backup.

Next Steps
  • If you are tired of changing the location for your backups each time you do a backup you can make this one time registry change and not have to select a the folder each time. In addition, this works the same if you use the T-SQL BACKUP commands. You will not need to specify the folder just the file name and the backups will work without a problem.
  • Take a look at these other backup tips


Last Update:





About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, February 18, 2016 - 9:12:05 AM - Greg Robidoux Back To Top

Hi Waqar,

You can take a look at the tip below and then add a step to create a folder with the date as part of the name using xp_cmdshell.

https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

This script can then be scheduled using SQL Agent.

Hope this gives you enough to get started.

-Greg


Thursday, February 18, 2016 - 2:43:59 AM - Waqar Ahhmed Back To Top

 

Hyyy

 

I want to manage the auto SQL Backup, on the way, Auto SQL backup all databases, then create auto folder with currant date then all databases move in folder,

 

Please help me,

 

Thanks,

 

 

 


Thursday, October 01, 2015 - 9:38:52 PM - Joyce edwards Back To Top

Thanks, this explains it well.  I am running management studio on one windows server and MySQL on another one and can't see my files even though the account has privileges so it must be related to the way the studio server is connected to the SQL. Server


Tuesday, June 10, 2014 - 12:33:09 PM - Rick Sheeley Back To Top

I found I also needed to change the following key/value under SQL server 2008R2:

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer

Value: BackupDirectory

 

Just FYI.


Saturday, March 01, 2014 - 8:12:48 PM - Alexander Back To Top

Greg, you are awesome! The solution of restoring currupted Backup folder path is brilliant!


Wednesday, January 29, 2014 - 2:09:46 PM - Pete Back To Top

Or try this: 

http://www.mytechmantra.com/LearnSQLServer/Changing_Default_Backup_Location.html


Thursday, January 02, 2014 - 12:28:07 PM - Omar Back To Top

For Windows 64 Bits the regedit key "BackupDirectory" is under:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer


Thursday, January 24, 2013 - 10:53:41 AM - Greg Robidoux Back To Top

@Eric - I am not sure how to resolve this one.  I tested this on SQL 2008 R2 and get the same error that you reference. For some reason SQL is only looking at the local drives and does not want to use the UNC path.

Sorry I don't have an answer for you.


Friday, January 18, 2013 - 4:19:35 PM - Eric Back To Top

This has worked for me; however, there are issues when I go to restore my backups. When I select restore -> from device, and try to select a source, I get the error

 

------------------------------

\\server\backuplocation

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.  

 

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

------------------------------

 

If I manually enter the entire share path and filename into the 'Locate backup file' dialog that appears afterwards, it works and is able to restore the file - however, this is a large pain in the rear. Why is it not able to see / browse from the default location in the first place? Can this be resolved, or is the slow & roundabout way our only option?


Thursday, October 04, 2012 - 5:30:41 PM - Greg Robidoux Back To Top

This tip should be updated.  In SQL 2000 you were able to do this and then in SQL 2005 they removed this option.  But now it is back again as you mentioned using Facets.


Thursday, October 04, 2012 - 4:24:00 PM - RobM Back To Top

Also worth a mention is the fact you get a chance to set the dafault backup directory during the installation process.


Monday, August 20, 2012 - 11:43:56 AM - Ankit Shah Back To Top

More Easiest way to change it for sql 2008 and later versions....

  •  

     

     

    Using SQL Server Management Studio, connect to SQL Server Database Engine.
  • In Object Explorer right click the SQL Server Database Engine Instance and choose Facets from the popup menu as shown in the snippet below.
  • View Facets select Facet as Server Settings and change the value of BackupDirectory Facet Property to the New Folder Location where you want to save your database backups in future and click OK to save the changes.

 Once you have updated the SQL Server Backup Folder location go ahead and restart the SQL Server Services for the new values to come into effect.








 


Saturday, June 16, 2012 - 12:08:32 AM - Ahmet Back To Top

Thank you. With the help of this tip I solved my "The BackupDirectory registry key is not configured correctly..." problem.


Saturday, April 09, 2011 - 8:08:55 AM - Greg Robidoux Back To Top

Hi Sam, I just tried this on SQL Server 2008 and the GUI showed the new location for me after I made the change.  I also had done a backup for this database prior to changing the location and it still showed the new location using the GUI.

I am not sure why it is not working consistently for you.

You could also try this to see if the backup for one of these databases goes to the new location and put in your database name and the name of the file for the backup file without specifying a backup location.

BACKUP DATABASE dbName TO DISK = 'fileName'

BACKUP DATABASE master TO DISK = 'master_test.bak'


Friday, April 08, 2011 - 6:09:24 PM - Sam Back To Top

Correct.  I'm able to change the location and remove the old location, but every time I right click-->all tasks-->back up, the default location would revert back to what it was before I made the change.


Friday, April 08, 2011 - 5:06:31 PM - Greg Robidoux Back To Top

Sam are you saying that for newer databases the backups go to the new backup location, but for older databases the backups go to the older backup folder? Just want to make sure I understand the need.


Friday, April 08, 2011 - 4:58:34 PM - Sam Back To Top

This worked great for new databases that are created.  However, the default location for databases that existed before the registry change still defaults to the old location.  Is there a fix for that?


Tuesday, February 02, 2010 - 9:15:27 AM - admin Back To Top

For SQL Server 2000 it is basically the same thing. 

Here is an example of the key path for a SQL 2000 installation 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2000\MSSQLServer

The parameter is called "BackupDirectory"


Wednesday, September 24, 2008 - 9:48:20 AM - SAinCA Back To Top

To do what you want, I'd recommend using Red-Gate's SQL Backup as it runs exceptionally quickly, including compression (HUGE disk saving), and has a simple "copy to network drive" option you can employ.  The guys here tried local and network-copy using native commands but it became a pain - they were glad of SQL Backup's feature...  Hope you can justify the minimal expense for what the tool delivers.


Wednesday, September 24, 2008 - 9:44:36 AM - SAinCA Back To Top

Thanks, Mike, it had to be "something different" for 2K.  Will give it a whirl on our test box...


Wednesday, September 24, 2008 - 5:04:50 AM - psivakumar Back To Top

Is there a way to point to a remote server's shared folder(s)? This will make backup to remote servers and keep a copy standing by for better recoverability. Currently, I am backing up in the local server (SQL) and then copying to remote server.

 

Thanks


Wednesday, September 24, 2008 - 4:42:06 AM - MikeHinds Back To Top

[quote user="SAinCA"]

How does one CHANGE the default backup destination folder?  There doesn't appear to be an equivalent registry area for 2K.

[/quote]

Create ONE Backup device using Enterprise Manager, which will then use your chosen backup folder as the default (one of many useful behaviors that went away in 2005).

[quote user="SAinCA"]

AND, do I have to make the change to both nodes of the cluster, if indeed it is a registry change that is required...?

[/quote]

I've done this on a cluster, but can't verify fully that this is the case without actually failing one over to test. Not knowing where the default is stored: if in the registry, you may need to do it twice; but if it's stored in Master or Msdb, then seems like you're covered.


Wednesday, September 17, 2008 - 6:21:36 PM - aprato Back To Top

Do you have a BackupDirectory key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer ? I don't have access to SQL Server 2000 at the moment and I'll have to install it to check the installed reg keys


Monday, September 15, 2008 - 6:06:16 PM - SAinCA Back To Top

Yep - known...

How does one CHANGE the default backup destination folder?  There doesn't appear to be an equivalent registry area for 2K...  Poster is NOT a DBA, hence the seemingly "basic" nature of the question...

AND, do I have to make the change to both nodes of the cluster, if indeed it is a registry change that is required...?


Monday, September 15, 2008 - 6:02:25 PM - aprato Back To Top

 There's a similar screen in SQL 2000

In Enterprise Manager, right-click on the server name and choose Properties and then the Database Settings tab


Monday, September 15, 2008 - 11:42:21 AM - SAinCA Back To Top

The tip is useful for 2005+ - is there an equivalent for SS2K, please?


Learn more about SQL Server tools