SQL  Server  Detailed steps for changing the default backup directory for databases

This article also applies to the latest version of SQL server 2008, 2016, 2019Open SSMSSelect Database, right-click, PropertiesSelect&quo; Permissions”, Click on&quo; View Server Permissions&# quo;Select&quo; Database Settings;, Modify&quo; Backup; Box PathNote: After changing the backup path of a database, it is valid for all databases. Please note that the final prompt indicates whether to save

This article also applies to the latest version of SQL server 2008, 2016, 2019

Open SSMS

Select Database, right-click, Properties

Select&quo; Permissions”, Click on&quo; View Server Permissions&# quo;

Select&quo; Database Settings;, Modify&quo; Backup; Box Path

Note: After changing the backup path of a database, it is valid for all databases. Please note that the final prompt indicates whether to save.

If the database is backed up to a new directory now, the following error will be reported:

Unable to open backup device. Operating system error 5 (access denied.) occurred. BackUPDATEBASE is terminating abnormally..

Then right-click on the new backup directory, select Properties, Security, Edit, Add, and fill in the box with&quo; NTSERVICEMSSQLSERVER” This service user, confirm, (this user name will automatically change to" MSSQLSERVER". If you do not have this user, click" Advanced" -" Find Now", select MSSQLSERVER or  NTSERVICEMSSQL $InstanceName), and then select" Full control;, Determine along the way

Now you can backup to the new directory

How to modify the default backup path and registry of SQL Server

1 Win+R, enter Regedit to open the registry;

2 Search: BackupDirectory

3 Find the corresponding value and modify it to the path you want to set.

The reason for using search instead of directly searching for the registry is because different SQL Servers may have different locations.

When using SqlServer to backup a database, it prompts that the backup directory does not have permission. Operating system error 5: Solution to access denied

If the backup fails or the contents of the backup directory folder cannot be browsed, there may be a problem with the permission settings of this folder.
Today, while helping the client manually perform a SqlServer database backup, I found an error prompt indicating that BackupFailedFromServer (Microsoft SqlServer...) System. Data. SqlClient... Cannot open backup device... Operating system error 5 access was denied. Considering that before doing this, I only cancelled the sharing of the backup directory and suspected that canceling the sharing of the backup directory would affect security permissions. Compared to other related directories, I found that the permissions for this directory have indeed changed, just add the relevant permissions.

When doing a database backup, you can use SSMS to open and view the backup files at any time

But once the folder was set for sharing, and then the sharing was cancelled. Later, it was found that opening using SSMS failed. Prompt; Cannotopenbackupdevice”

I thought it was a permission issue, but after logging in as an administrator, the problem still exists

terms of settlement

Go to the property settings of that folder and add LocalService and NetworkService to&quo; Security; &quo; in the label; User Group” Inside, check 'Full Control' separately.

reason:

A SQL runtime environment is composed of many services, each of which is logged in and running under a built-in account. This account is not our own account for logging in to the computer, and it is not important to log in with what identity, but rather the service account of the SQL Server service

That's all for this article on the detailed steps to change the default backup directory for SQL Server databases. For more information on modifying the default backup directory for SQL Server, please search for previous articles or continue browsing related articles below. We hope everyone can support Script Home more in the future!