In this article, you’ll learn the key skills that you need to copy tables between SQL Server instances including both on-premises and cloud SQL databases. In this article, I’ll walk-through several ways of copying a table(s) between SQL databases, helping you to see the benefits and trade-offs of each option.
To backup SQL database from remote to local successfully, you need to do some additional operations.
1. Backup remote SQL Server database to network then copy 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).
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.
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.
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 and backup it to your local drive.
Conclusion
This article describes approaches to backup remote SQL Server database to local drive, most of which require you to operate on two computer separately. Hope you enjoy the article.
Andriy Kravets is writer and experience .NET developer and like .NET for regular development. He likes to build cross-platform libraries/software with .NET.
