Backup SQL Database to Remote Location

1. Creating Network Share in Local or Remote Machine

In this method you had to create a network share in your local desktop or remote computer. With the use of this remote share you can take backup directly from SQL Server

• Create a network share in your local desktop or remote computer
• Provide SQL Service account Full privilege (i.e modify privilege for SQL Server) so that it can read & write data to the share. If you forget this step then you will be ended with OS error 5 and backup command will fail.

That’s all setting you had to do. Now, Just point this share path in your backup. Let’s say I need to backup Pubs database to my pc then I’ll create a network share called remote backup and use it to take Pubs db backup

BACKUP DATABASE Pubs TO DISK='\\sagarpc\remotebackup\Pubs.bak'

SQL Backup time is directly proportional to network bandwidth

Due to network fluctuations, lets say if you are in middle of the backup and if you face a network problem then the backup gets cancelled and you need to start from the beginning

2. Creating Network Share in SQL Server (server level)

In this method you will take the backup in the server itself and then make the backup path as network share and using this network share you will copy the backup file to your desktop.

• Create a network share in the server
• Make sure the login used to copy the backup has necessary permission (only read is enough) in the server network share

Once  you are ok with this start taking the backup. We will consider the same scenario for copying the Pubs db, first I’ll take the backup to the server local drive (the folder which is made as server network share, in this case I’ve shared D:\Backups folder), then I’ll manually map the share from my desktop to copy it.

BACKUP DATABASE Pubs TO DISK='D:\Backups\Pubs.bak'

You had to manually copy the backup file to local computer.

3. Use Database Publishing Wizard

In this method you will just script the entire database with schema and data and you can specify the the local path in the wizard so that it will script to the path given.

You need to install Database publishing wizard separately

After installation open it and follow the wizard to script it. You will be getting a page as shown below where you can provide the path to save the script. Run this script against your local SQL server to get the database.

You had to install the Microsoft SQL Server Database Publishing Wizard.

It is totally depends on the speed of the network.