Page Table of Contents
Reviews and Awards
To facilitate data management, you may want to use one computer as a central server in your local area network to store backups of other client computers. You may want to back up a remote SQL Server database to a local drive. Is this really possible? Certainly, this article from EaseUS will provide you with three methods to back up a remote SQL Server database to a local drive.
- Tip 1. Use EaseUS Todo PCTrans Server
- Tip 2. Backup Remote SQL Server Database to the Network and Copy it to Local Drive
- Tip 3. Directly Backup Remote SQL Database to Local Drive
First, let's start with the simplest method. For beginners, using trustworthy third-party tools is undoubtedly the best choice.
Tip 1. Use EaseUS Todo PCTrans Server
EaseUS Todo PCTrans Server serves as a central console to manage all devices within the local network, breaking the limitations of other methods on downtime and scope of application and allows you to remotely and intuitively back up any SQL Server. When creating a database backup task, you can also flexibly set the schedule, configure the compression level, or encrypt the backup according to your needs.
100% Secure
Free Inquiry
To keep your database safe and the backup process smooth, download and use this tool.
Step 1. Launch EaseUS Todo PCTrans on source and target Server PCs, on the source PC, select "This is Old PC" and "Next".
![select a location and click scan](/images/en/screenshot/todo-pctrans/pc-to-pc-server.png)
Step 2. Connect the target new Server computer via the password or identification code.
![Connect two server PCs](/images/en/screenshot/todo-pctrans/pc-to-pc-2.png)
Step 3. EaseUS Todo PCTrans will analyze the content on the source Server PC, once done, at the Applications section, select the desired SQL apps and click "Transfer".
![Select SQL to transfer](/images/en/screenshot/todo-pctrans/transfer-sql-to-new-server-pc-1.png)
Step 4. Wait for the SQL transfer proceduer to complete and click "OK" to finish.
![Finish Transferring MySQL](/images/en/screenshot/todo-pctrans/transfer-sql-to-new-server-pc-2.png)
With its help, you can transfer or back up the SQL database within any two PCs.
100% Secure
Free Inquiry
Tip 2. Backup Remote SQL Server Database to the Network and Copy it to Local Drive
How do you backup the remote server database to the local drive? In fact, you can first back up the database to a network path on the remote server and then access the network path on the local server to copy the backup file to the local target disk.
- Notice:
- You can use the backup GUI or T-SQL in SSMS. The former does not allow you to select the network path directly, but you can enter it manually. Both methods require a UNC name (\\fileserver\share\filename.bak) rather than a mapped drive letter. Otherwise, you may encounter operating system error 3 (The system cannot find the path specified).
I'll take T-SQL as an example, and here are the detailed steps:
On the remote Server: First, Backup the SQL Server Database to a Remote location
Step 1. Open SSMS on the remote server, select New Query and connect to the instance you wish to backup.
Step 2. In the popping-out window, type and enter the T-SQL statements to perform the backup, and click Execute to run the command script:
BACKUP DATABASE databasename TO DISK = 'filepath\filename.bak'
If operating system error 5 (Access Denied) is returned, it is because the account you logged into SQL Server does not have permission to read and read data. To fix it, you can choose Tip 1 or try to give the current account enough permissions.
On the local server: Copy Backup Files to the Local Drive
After the backup is successful, you can access the network path from the local server and copy the backup files to the local disk.
🔎Tip:
To do it regularly, use xcopy or robocopy commands to create a backup batch file and automate it using Windows Task Scheduler.
You can share this article to let more users know these details and help them.
Tip 3. Directly Backup Remote SQL Database to Local Drive
In this part, you may like to know the Copy Database Wizard. It makes it easy to move/copy databases and certain server objects from one SQL Server instance to another without downtime. But you need to be aware of the limitations of this approach:
- The Copy Database Wizard is unavailable in the Express edition.
- You can't use the Copy Database Wizard to copy or transfer system databases.
- The Copy Database Wizard requires system administrator privileges.
- Databases cannot be moved or copied to earlier versions of SQL Server.
If you still want to use this method, please follow the steps below:
Step 1. Start SSMS, connect to your instance, and right-click on any user database under Object Explorer. Select the task in the menu and click Copy Database.
Step 2. In the pop-up Copy Database Wizard, first specify the source server and the remote server from which you want to back up the database. You can enter Windows Authentication or SQL Server Authentication. Then, click Next to link to it.
Step 3. On the next page, you can specify the target server, which should be your local server as the receiving end. Enter the required authentication information again and click Next to establish the connection.
Step 4. In this step, you need to choose a transfer method. SSMS automatically checks for using the detach and attach method, as it is generally faster and better suited for large databases. If you don't want any downtime, using the SQL-managed object approach is a better option. Although slower, it guarantees that the source database remains online.
Step 5. Depending on your needs, select to copy or move. If you select the move option, this tool will automatically delete the source database after completing the move.
Step 6. After these settings, you can choose to execute immediately or check the schedule on this screen and click Change Schedule to open the schedule dialog box for configuration. Finally, click Finish to perform the database copy.
Step 7. Finally, select the database you copied successfully, and then you can back up the remote SQL Server database to the local drive.
You May Also Like:
Conclusion
This article introduces you to three methods to help you back up or move a remote SQL Server database to a local drive. Among them, the EaseUS Todo PCTrans Server is the most suitable for beginners. You don't need a lot of background knowledge to use it directly. On the contrary, Tip 2 and Tip 3 require you to be more familiar with Windows Server Database and computer operations. Therefore, in order to ensure the security of the database, it is recommended that you use reliable third-party software - EaseUS Todo PCTrans Server.
100% Secure
Free Inquiry
Backup Remote SQL Database to Local Drive FAQs
Backing up the SQL database is very necessary. This article provides three ways to achieve this; if you have additional questions, the following FAQs may be helpful.
1. How can I back up a remote SQL Server database to a local drive?
In Microsoft SQL Server Management Studio, right-click the database that you want to back up and then click Tasks > Generate Script.
This action will bring up a wizard where you can set up a proper backup of your database, even on a remote server. Once it's done its job, you'll have a backup script ready in front of you.
2. How do I backup my SQL database to a local drive?
Follow the instructions below, and you can backup the SQL database to a local drive:
- Start your SQL Server instance and establish a connection with SQL Server Management Studio (SSMS).
- Object Explorer's Databases node can be expanded.
- Hover your cursor over Tasks with a right-click on the database, then choose Back up.
- Verify that the path for your backup is accurate under Destination. If the path needs to be changed, choose Remove to erase the current path and then Add to enter a new one. The ellipses can be used to browse to a certain file.
- Choose OK to take a backup of your database.
3. What is Remote SQL Server backup?
A remote SQL Server is a server that allows you limited access to the server's file system, just like in a shared hosting environment. Basically, you run the BACKUP DATABASE command using SQL Server Management Studio or 3rd party tools. This can create a *.bak file on the Server's local file system. You can compress the file, encrypt it, and upload it to a network drive, FTP, or a cloud, etc.
About the Author
![](https://www.easeus.com/images/en/icon/author11.png)
Sherly joined EaseUS in 2022 and she has always loved writing articles and enjoys the fun they bring. She receives professional training here, focusing on product performance and other relative knowledge. She has written over 200 articles to help people overcome computing issues.
Reviews and Awards
-
EaseUS Todo PCTrans Free is a useful and and reliable software solution created to serve in computer migration operations, be it from one machine to another or from an earlier version of your OS to a newer one.
Read More -
EaseUS Todo PCTrans is by the folks at EaseUS who make incredible tools that let you manage your backups and recover your dead hard disks. While it is not an official tool, EaseUS has a reputation for being very good with their software and code.
Read More
-
It offers the ability to move your entire user account from your old computer, meaning everything is quickly migrated. You still get to keep your Windows installation (and the relevant drivers for your new PC), but you also get all your apps, settings, preferences, images, documents and other important data.
Read More
Related Articles
-
Transfer Data, Software/Applications to Windows 10 or Windows 11
Tracy King/2023/12/13
-
Best Method to Transfer Files from PC to PC without USB
Tracy King/2023/12/08
-
Can I Run PC Games Off External Hard Drive? Sure! You Can!
Sherly/2023/10/25
-
How to Move Minecraft to Another Hard Drive on Windows 10
Cedric/2023/11/14