How to Backup SQL Database to Remote Location without Error?

How do I backup SQL database to remote location?

Backing up beforehand can avoid accidental deletion or corruption of SQL database. But when it’s comes to system failure or computer breakdown, a backup on local drive is pointless. That’s why you need to backup SQL database to remote location like network share, NAS or mapped drive.

The way to do this is nothing different - you can set up backup in SQL Server Management Studio just as usual. But some errors may occur during the process, like Operating system error 3 (The system cannot find the path specified) and Operating system error 5 (Access is denied).

Next, I will introduce how to resolve these errors and back up database to a remote location without problems. You could also try Method 2 to skip these troubleshooting steps and easily backup SQL database to network location.

• Method 1. Backup SQL database to network drive in SSMS smoothly
• Method 2. Easily backup SQL database to remote location without problems

1. Backup SQL database to network drive in SSMS smoothly

You can use SSMS backup wizard or T-SQL to backup SQL database to network drive, but while specifying a remote location as destination, some operating system errors (like error 3 or error 5) might fail the backup. This is usually due to path issue or permission issue. Simply put, you should be aware that:

☛ When backing up to a mapped drive, enter its UNC name instead of mapped drive letter.

☛ Provide full permissions of the target share to the account you log on SQL Server as.

I will elaborate on these points in the following article.

How to backup SQL Server databases to a mapped drive without error 3?

The Operating system error 3 (The system cannot find the path specified) usually occurs when you use the mapped drive letter like (Z:) or (N:) as path. To solve it, you need to use its UNC name (\\fileserver\share\filename.bak) instead.

☛ Backup with SSMS backup wizard:

1. Connect to your server. Right-click the database you want to backup, and choose Task > Back Up.

2. In the pop-up wizard, you could configure the database, backup type and destination. In the destination section, Remove the default path and Add a new one.

It’s not allowed to select a share or mapped drive directly, so you need to enter its UNC name of the remote location. For example, I want to backup database to a network share “\DESKTOP-AC51C0M\share” and name the backup file as “1.bak”. Then I need to enter “\DESKTOP-AC51C0M\share\1.bak” .

3. Click OK to perform the operation.

☛ Backup with T-SQL:

Alternatively, you could use T-SQL to perform the same operation.

1. Connect to the server, click New Query button above.

2. In the SQLQuery window, enter the backup command as following:

BACKUP DATABASE databasename TO DISK = 'filepath\filename.BAK'

To backup to a network location without error 3, you need to use the UNC name as path, so my example is:

BACKUP DATABASE example TO DISK = '\\DESKTOP-AC51C0M\share\1.bak'

3. Click on Execute to perform the backup.

Now that the Operating system error 3 should not bother you anymore.

What if backup failed with Operating system error 5 (Access is denied)?

When you specify a network share in your local drive or a remote machine, the backup may also fail due to error 5:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\fileserver\share'. Operating system error 5 (Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This is usually because the account you log on the SQL Server as doesn’t have the permission to read & write data to the share. To fix it, you need to find the account, then provide full privilege to it. Here’s the approach:

1. Press Win + R to open the Run window. Type “services.msc” and press Enter.

2. Find the service of SQL Server (MSSQLSERVER), right-click it and choose Properties.

Then you could find the full account name under Log On tab.

3. Now, right-click the network share you want to save the backup, choose Properties, and switch to Security tab.

4. If you can find the account under “Group or user names”, then you just need to select it, and choose to Allow its Full control. Thus all the permissions for this account will be enabled. Click Apply to confirm the settings.

If this account doesn’t show in the list, you will need to add it manually by clicking Edit and then Add.

In the popping out window, you could paste the account name in the textbox, and click OK to search for it. Then, select the corresponding account from the results, and continue to modify its permissions as normal.

After that, you should be able to backup SQL database to the share without error 5.

2. Easily backup SQL database to remote location without problems

If you are still unable to backup database to a remote location successfully, or feel tired of these troubleshooting steps, you could also try an error-free alternative.

AOMEI Centralized Backupper Database is a centralized backup and management solution for all Windows PCs, servers and virtual machines within LAN. Its SQL Server Backup feature supports SQL 2005-2019, and enables you to backup selected databases to network location without problems.

To get started, you need to download and install AOMEI Centralized Backupper Database on the central machine, and install the client package on client computers. After that, you could launch Centralized Backupper Database on the central machine and request control over those clients to proceed.

Have a try on the 30-day free trial:

How to backup SQL database to remote location via easier alternative?

1. Navigate to Home > SQL Server Backup to create a new task.

2. In Step 1, click + Add Computers to detect the controlled clients with SQL database, and choose the computer you want from the results.

3. In Step 2, click + Add to detect all the SQL Server instances on the selected computer. Then choose the databases (s) on one or more instances to backup.

4. In Step 3, you could click Add Storage to add a network share or NAS as destination. You need to enter its UNC path and corresponding authentication.

5. Now you can click Start Backup to execute the operation. But before that, you could also enable email notification in Settings, or set up Schedule to perform SQL Server automatic backup.

This way, you won’t receive Operating system error 3 or error 5 during the process. With the centralized backup and restore feature, you can also backup SQL database to another server easily.

Bottom line

It’s a good idea to backup SQL database to remote location, thus even the entire machine breaks down, you can retrieve databases with the backup. However, various operating system errors might appear during the process. To save time from error-proof settings and troubleshooting steps, you could also use third-party software like AOMEI Centralized Backupper Database Edition.

This software allows you to backup SQL databases to network share or NAS within several steps, and you can select multiple databases or even multiple instances at once. In addition, it also contains system/disk/partition/file backup functions for Windows PCs and servers, so you can easily backup all computers on network effortlessly.