{"id":539,"date":"2014-09-30T19:59:28","date_gmt":"2014-09-30T13:59:28","guid":{"rendered":"http:\/\/dotnetsql.info\/net-sql\/?p=539"},"modified":"2015-07-09T17:03:48","modified_gmt":"2015-07-09T11:03:48","slug":"how-to-change-the-sql-server-backup-folder","status":"publish","type":"post","link":"http:\/\/dotnetsql.info\/net-sql\/index.php\/how-to-change-the-sql-server-backup-folder\/","title":{"rendered":"How to change the Sql Server backup folder"},"content":{"rendered":"

Scenario proposal<\/strong>
\nWhen 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.<\/p>\n

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.<\/p>\n

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

<\/p>\n

Solution<\/strong>
\nThe 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

\"\"<\/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_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL.2\\MSSQLServer <\/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

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\"\"<\/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=’SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL.2\\MSSQLServer’,
\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

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=’SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL.2\\MSSQLServer’,
\n@value_name=’BackupDirectory’,
\n@type=’REG_SZ’,
\n@value=’C:\\Program Files\\Microsoft SQL Server\\MSSQL.2\\MSSQL\\Backup’
\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=’SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL.2\\MSSQLServer’,
\n@value_name=’BackupDirectory’,
\n@BackupDirectory=@BackupDirectory OUTPUT
\nSELECT @BackupDirectory
\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

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

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

Next steps :
\n1.\u00a0Share this with your colleagues because Sharing\u00a0 is Learning
\n2.\u00a0Comment below if you need any assistance<\/strong><\/p>\n

Powered by CodeReview – Let’s make it Better!<\/a><\/strong><\/em><\/p>\n

Share this:<\/h3>