{"id":52,"date":"2014-10-01T09:53:50","date_gmt":"2014-10-01T09:53:50","guid":{"rendered":"http:\/\/www.codereview.co\/?p=52"},"modified":"2014-10-06T16:12:51","modified_gmt":"2014-10-06T16:12:51","slug":"how-to-change-the-sql-server-backup-folder","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/how-to-change-the-sql-server-backup-folder\/","title":{"rendered":"How to change the Sql Server backup folder?"},"content":{"rendered":"

Scenario proposal
\n<\/strong> When you install SQL Server the path for the installation is generally something such as the following: C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQL.\u00a0 In this directory there are also folders for your DATA files and also your BACKUP files.\u00a0 Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases, but you can not change the default directory for your backups.\u00a0 Is it possible to change the default directory for backups, so it does not need to be specified each time I run a backup?<\/p>\n

Solution
\n<\/strong> The directories for the default data files, log files and backups are stored in the system registry.\u00a0 As mentioned already you have the ability to change the default data and log directories using SQL Server Management Studio, by right clicking on the server name and selecting properties and navigating to the Database Settings page as shown below.<\/p>\n

\"\"<\/a>
\n <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory.\u00a0 To find this we need to look in the registry.
\nIf we open the registry using REGEDIT or some other tool and if you navigate to this key: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServer <\/strong>or similar for your instance of SQL Server we can see the following information\u00a0\u00a0 The highlighted line below shows the default directory for the backups for this one instance.
\n\"\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

If you want to change the value you can edit the registry value and save the new location.\u00a0 So for this example let’s change it to ‘D:SQLBackups’.
\nIf 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’.
\n\"\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Another way to change the registry is to using the extended stored procedures XP_REGREAD and XP_REGWRITE.
\nTo read the value from the registry you can issue the following command:
\nDECLARE @BackupDirectory VARCHAR(100)
\nEXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
\n@key=’SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServer’,
\n@value_name=’BackupDirectory’,
\n@BackupDirectory=@BackupDirectory OUTPUT
\nSELECT @BackupDirectory
\nThis will provide the following output, since we changed the value above directly in the registry.
\n\"\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

If we want to change this back to the default folder we can use the following command
\nEXEC master..xp_regwrite
\n@rootkey=’HKEY_LOCAL_MACHINE’,
\n@key=’SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServer’,
\n@value_name=’BackupDirectory’,
\n@type=’REG_SZ’,
\n@value=’C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBackup’
\nIf we run this command again we can see that this has changed:
\nDECLARE @BackupDirectory VARCHAR(100)
\nEXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
\n@key=’SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServer’,
\n@value_name=’BackupDirectory’,
\n@BackupDirectory=@BackupDirectory OUTPUT
\nSELECT @BackupDirectory
\n\"\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

To determine where SQL Server is installed you can right click on the server name, select Properties.\u00a0 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.
\n\"\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

That’s all there is to it, so save yourself some time and change the default location.<\/p>\n

Thanks for reading this article,<\/strong><\/p>\n

Next steps :<\/strong><\/p>\n