none
How to change the default backup location?

    Question

  • SQL Server puts backups in a default folder C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
    Is there a way to change that to a different location?

    I'm using Smo.Backup object in my code to create backups. The backups I create are stored in that default folder, but I was hoping that I can change that default location without having to make code changes.
    Is that possible?

    thanks.
    Tuesday, February 23, 2010 7:51 PM

Answers

  • Please don't update registry directly. It is not a supported scenario.

    1) You can update BackupDirectory by setting BackupDirectory property in SMO's Server object and issue a Alter()

    2) You can use SQL Powershell to perform the same task

    Sample Powershell script ( you can use sqlps.exe to run these commands)

    CD SQLSERVER:\SQL\SETHU-LAPTOP\DEFAULT
    $server = (get-item .)
    $server.BackupDirectory ="c:\temp"
    $server.Alter()

    3) from SSMS User interface, right click on Object explorer's server menu, select Facets, select "Server" facet, set the value for BackupDirectory in the property grid and click Ok

    Thanks

    Sethu Srinivasan [MSFT]

    SQL Serverttp://blogs.msdn.com/sqlagent

    Wednesday, March 07, 2012 4:10 PM
  • SQL Server puts backups in a default folder C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
    Is there a way to change that to a different location?

    I'm using Smo.Backup object in my code to create backups. The backups I create are stored in that default folder, but I was hoping that I can change that default location without having to make code changes.
    Is that possible?

    thanks.

    To read the value from the registry you can issue the following command:

    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 this back to the default folder we can use the following command

    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 as highlighted below will show you the corresponding install name for the instance such as "MSSQL2" highlighted below, so you know which registry entry needs to be changed.

    Wednesday, February 24, 2010 11:05 AM
  • You can change the backup location here - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer

    The key name is BackupDirectory - you can change it to any other folder.

    Thanks, Leks
    Tuesday, February 23, 2010 9:00 PM
    Answerer

All replies

  • You can change the backup location here - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer

    The key name is BackupDirectory - you can change it to any other folder.

    Thanks, Leks
    Tuesday, February 23, 2010 9:00 PM
    Answerer
  • SQL Server puts backups in a default folder C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
    Is there a way to change that to a different location?

    I'm using Smo.Backup object in my code to create backups. The backups I create are stored in that default folder, but I was hoping that I can change that default location without having to make code changes.
    Is that possible?

    thanks.

    To read the value from the registry you can issue the following command:

    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 this back to the default folder we can use the following command

    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 as highlighted below will show you the corresponding install name for the instance such as "MSSQL2" highlighted below, so you know which registry entry needs to be changed.

    Wednesday, February 24, 2010 11:05 AM
  • And how we set the default backup file name format? I want every backup file to have the format DBNAME_Year_Month_Day_Hour_Minute.bak by default.
    Thursday, November 25, 2010 12:45 PM
  • You can make use of Maintenance Plans --> Backup task. It does it for you


    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Thursday, November 25, 2010 1:41 PM
  • I think a better, more appropriate question and answer would be along the lines of:

    "How do I use the existing SQL built-in tools (Management Studio, Setup, etc.) to change the backup [and other] locations?"

    During setup, there is a wonderful grid-like interface that comes up, and it shows you all the locations for all the various components, and I think, if you re-run the "setup.exe" again, it will let you alter those DB & Backup locations - but I'm not sure.

    I know there is a way to re-display the grid and then select new locations for DB, Backups, logs, etc.

    Does anyone know the precise details on that, without having to manually monkey with the registry?


    tnjman

    Wednesday, March 07, 2012 2:05 PM
  • Please don't update registry directly. It is not a supported scenario.

    1) You can update BackupDirectory by setting BackupDirectory property in SMO's Server object and issue a Alter()

    2) You can use SQL Powershell to perform the same task

    Sample Powershell script ( you can use sqlps.exe to run these commands)

    CD SQLSERVER:\SQL\SETHU-LAPTOP\DEFAULT
    $server = (get-item .)
    $server.BackupDirectory ="c:\temp"
    $server.Alter()

    3) from SSMS User interface, right click on Object explorer's server menu, select Facets, select "Server" facet, set the value for BackupDirectory in the property grid and click Ok

    Thanks

    Sethu Srinivasan [MSFT]

    SQL Serverttp://blogs.msdn.com/sqlagent

    Wednesday, March 07, 2012 4:10 PM
  • Option 3 is the proper way of changing the default backup location for the given SQL Server instance. Thank you, Sethu.
    Wednesday, March 26, 2014 3:04 PM
  • Hi ,

    If you are looking for 2008 server changes. please check the below link ....it will help to you...

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

    Thanks,


    RAM

    Wednesday, May 28, 2014 6:28 PM