(3 Methods) Backup Remote SQL Server Database to Local Drive

Can you backup database from remote SQL Server to local drive?

To facilitate the data management, you may want to use one computer as a central server in the LAN for storing backups of other client computers. When it comes to MSSQL, you may also want to backup remote SQL Server database to local drive. But can you really do that?

Well, the answer is yes, but the steps will be different from normal MSSQL backup. Because even if you can connect to remote server in SSMS and run BACKUP DATABASE TO DISK commands, the *.bak file will be stored on local drive of that server, and normally you do not have access to process the file further.

To backup SQL database from remote to local successfully, you need to do some additional operations.

How to backup remote SQL Server to local drive (3 methods)

Please note that Method 1 require you to operate on both the remote server and the local server. If you only want to operate on your local server, then you can refer to Method 2 and Method 3, but Method 2 is not available for SQL Express versions and has more restrictions on use.

• Method 1. Backup remote SQL Server database to network then copy to local drive
• Method 2. Copy database from remote SQL Server to local SQL Server directly
• Method 3. Centrally backup databases from remote SQL Server (easiest)

1. Backup remote SQL Server database to network then copy to local drive

How to backup remote server database to local drive? The method actually involves backing up the database to the network path on the remote server, then accessing the network path on the local server and copying the backup file to the local disk.

For backup methods, you can use either backup GUI or T-SQL in SSMS. the former does not allow you to select a network path directly, but you can enter it manually. Also, no matter which method you use, you need to use the UNC name (\\fileserver\share\filename.bak) instead of mapped drive letter, otherwise you may receive Operating system error 3 (The system cannot find the path specified).

You can check details in backup SQL database to remote location, here I will use T-SQL as an example.

On remote server: backup SQL Server database to remote location

Launch SSMS on the remote server, connect to the instance you want to backup, and click New Query.

In the popping out window, enter the T-SQL statements to perform the backup, and hit Execute to run it:

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

Here’s my example:

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

If it returns Operating system error 5 (Access is denied.), this is 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, please refer to SQL Server backup to network share access denied.

On local server: copy backup files to local drive

Once the backup is successful, you can access this network path from the local server and copy the backup file to the local disk.

If you want to do it regularly, you can use xcopy or robocopy commands to create a backup batch file, and use Windows Task Scheduler to automate it.

2. Copy database from remote SQL Server to local SQL Server directly

The Copy Database Wizard makes it easy to move or copy databases and certain server objects from one instance of SQL Server to another without downtime. However, it also has the following limitations:

• The Copy Database Wizard is not available in the Express version.
• It is not possible to move or copy databases to earlier versions of SQL Server.
• The Copy Database Wizard cannot be used to copy or move system databases.
• The Copy Database Wizard requires SysAdmin privileges...

1. Launch SSMS, connect to your instance, and right-click any user database under Object Explorer. Choose Tasks in the menu and click Copy Database.

2. In the popping out Copy Database Wizard, specify the Source server first, which is the remote server you want to backup database from. You can either enter Windows Authentication or SQL Server Authentication, then click Next to connect to it.

3. In the next page, you can specify the Destination server, which should be your local server as the receiving end. Again, enter the required authentication information and click Next to establish the connection.

4. Then, select the transfer method. SSMS will automatically check Use the detach and attach method because it’s generally faster and better suited for large databases. If you don’t want any downtime, Use the SQL Management Object method is a better option. It’s slower, but can ensure the source database remain online.

5. In this dialog box, you can select the database you want to Copy or Move and then follow the wizard to make your settings.

Note: If you select the Move option, the wizard will automatically delete the source database after moving it.

6. After setting, you can choose to execute immediately or check Schedule in this screen, and then click Change Schedule to open the schedule dialog box to configure it.

7. Once the settings are complete, click Finish to perform the database replication.

8. Finally, select the database you copied successfully, then you can backup remote SQL Server database to local drive.

3. Centrally backup databases from remote SQL Server (easiest)

The above methods either require you to operate on both servers separately, or have limitations in terms of downtime, scope of application, etc. If you want to back up or restore SQL database between any two computers within a LAN, it is easier to use a centralized data management software like AOMEI Centralized Backupper Database.

This program works as a central console to manage all the desktops, laptops, servers and virtual machines on the network, supporting you to backup any SQL Server remotely and intuitively.

When creating database backup task, you also have the flexibility to set up a schedule, configure the compress level, or encrypt the backup.

To make use of it, you need to download and install AOMEI Centralized Backupper Database on your local machine first:

Launch Centralized Backupper Database and it will auto detect all the available computers within LAN. You can remotely (or manually) Install client program on them, and Request Control over the computers you want to manage.

All the selected clients will receive a request. After confirmation, you can start to create centralized backup task.

1. Navigate to Tasks > New Task > SQL Server Backup.

2. Then you will see a three-step setup scree. You just need to proceed step by step.

In Step 1, click + Add Computers to detect controlled computers with SQL Server installed, and select the one you want to back up.

In Step 2, click Add to view instances on the SQL Server, and select databases according to your need. You can select multiple (or all) databases at once.

In Step 3, click Add Storage in the pop-up window, and enter the path of your share folder or NAS as the target directory.

3. Click Settings to manage more backup options, or click Schedule to set up automatic backup (Once/Daily/Weekly/Monthly). Then you can hit Start Backup to create and execute the task.

Note: This method will backup remote SQL Server database to a share folder. You can then copy the backups to local drive manually, or create a batch file using xcopy or robocopy commands to copy them automatically. For details, please refer to scheduled task to copy files to another folder.

Conclusion

What to do if you want to backup not only your local computer, but also a remote computer? This article describes approaches to backup remote SQL Server database to local drive, most of which require you to operate on two computer separately. If you want to do this on your own computer, you can use a professional backup tool - AOMEI Centralized Backupper Database Edition.

It can backup files/partitions/systems/disks/SQL databases on any computer within your LAN. The created tasks will be displayed in Tasks tab for centralized management. You can even restore database to another SQL Server directly.