Backup SQL Database to Remote location

Does the title sounds crazy? You might think that this topic is very simple? Yes you are right however some folks still raise this question in forums. Im seeing this question for for a long time in forums and thought of sharing the solution here, so that the folks can give a search to get this (coz Google is our first techie teacher or instant solution provider :P).

Ok why cant SQL use my local drives (desktop) to take the backup? Good question! When you install SQL Server the drives in scope are limited to the server where you are installing SQL server, lets say you are installing in Server1 then SQL can access all the drives existing in Server1, thats the concept. Even in windows explorer normally you cant browse other system drives directly, however you can use $ (eg.) \server1\c$) symbol to access the drives but this requires administrator privilege on destination server.

Now we talk about taking backup to our local machine.. I have always 3 different suggestions (if you have any better method, thats good update it in comment) to make a copy of SQL backup.

Creating Network Share in Local or Remote Machine

In this method you will be creating a network share in your local desktop or remote machine. With the use of this remote share you can take backup directly from SQL Server

Pre-requisite

• Create a network share in your local desktop machine or remote machine
• 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 miss this step then you will be ended with OS error 5 and backup command will fail.

Thats it you are done with it. Just point this share path in your backup. Lets say I need to backup Pubs database to my pc then Ill create a network share called remotebackup and use it to take Pubs db backup

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

Pros

• SQL Server will take care of entire backup and copy operation
• No server network share is required

Cons

• Prone to network fluctuations, lets say if you are in middle of the backup and if you face a network glitch then the backup gets cancelled and you need to start from the beginning
• SQL Backup time is directly proportional to network bandwidth
• If your SQL Service runs in Localsystem service account then you cant use this method

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.

Pre-requisite

• 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 Ill take the backup to the server local drive (the folder which is made as server network share, in this case Ive shared D:Backups folder), then Ill manually map the share from my desktop to copy it.

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

Pros

• SQL Server doesnt need to wait for network delays
• SQL Backup time is not directly proportional to network bandwidth
• You can use this method even if SQL Service used Local system account

Cons

• You need to manually copy the backup to local machine
• Incase if you have space constraint in the server and if the db is large then you cant take the backup in server

Use Database Publishing Wizard

This is the last method which I know. 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.

Pre-requisite

• You need to install Database publishing wizard separately
• Download & install it from Microsoft SQL Server Database Publishing Wizard

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.

Pros

• Pretty Straight forward
• You can use Windows or SQL authentication
• No elevated access is required for SQL Service account
• You can use this even if SQL service account uses Local system account

Cons

• You need to install the software
• It directly depends on network speed
• It will take more time compared to regular backup
• Suggested only for small sized database