Backup a SQL database to a remote drive

Question: I’d like to backup a remote database and add a notification when it’s completed.

Answer: The main issue with remote SQL Server backups  is accessing the .BAK file once it's been created. Accessing the SQL Server is normally not an issue if privileges are set up correctly.

There are different tactics to  employ which will allow you to execute the script on the remote server. Which tactics to employ are dependent on the specific set of circumstances.

A "typical" backup command   - backup database MYDB1 to disk = ‘H:\mydb1.bak’ - backs up to a local disk. The issue is how to access the file.?

Method 1 – Access the target SQL Server using your preferred method , such as ssms or sqlcmd , execute the sql script or command  which will back it up to a local disk

Add some code to move the files to an accessible path.

Method 2 – Run the scripts or commands locally but save the .BAK file to a network share that is accessible by the SQL Server and you.

backup database test to disk = '\\myserver\c$\tmp\test.bak'

You could use the script SQL Server - BackUp All Databases , and in the @path variable set the value with the network path .

It is assumed the login has valid privileges on the target network share.

If you want to add some automation around this task , you could schedule a task using SQL Server Agent.    The SQL Server Agent job could include notification of success or failure of the job

SQL Server has a database mail component , which you can access to send the notification .Read this post  on how to Generate query results and attach to email using SQL Server Database Mail

A note on executing scripts using SQL Agent. There are plenty of benefits of using SQL Agent to manage the regular backup schedule. If specifying a path use the UNC path – as SQL Agent doesn’t handle mapped drives well. The other point is that SQL Agent quite often managed by the Local Service account .

There are ways around this problem – such as using a different start up account and applying the permissions on the target path . For example , you could configure SQL Server Agent to start with NETWORK SERVICE , and configure the target share to allow read\write privileges on the target path .

Read more on backups

Database backup does not cause blocking (SQL Server DBA)

ASYNC_IO_COMPLETION during sql server backup (SQL Server ...