Backing up a Database in a Network Folder from SQL Server

Hi! I hope you are all doing well and having a great summer time!

It’s been a while since the last time I have posted an article but hey, besides the heavy workload, it’s summer time and the beaches in Cyprus are really great! 🙂

This post discusses about a quite simple task in SQL Server: backing up a database in a network folder / remote server.

The Challenge to Solve

To cut the long story short, imagine the following scenario:

You have a SQL Server Instance located on a server with only one local drive (!) and you urgently need to backup a database somewhere! Well, the first thing that comes on my mind, is to backup the database on a remote location (yep, you do not have physical access to the server and cannot mount a USB flash drive 🙂

In order to do this you first need to mount the remote location (network folder) as a backup device.

To this end, let’s say you have the network folder \\serverName\backupFolder and you want to mount it as a backup device. For doing that, you have to run the following stored procedure:

USE master
GO

EXEC sp_addumpdevice 'disk','NetWorkDeviceName','\\serverName\backupFolder\BackupFileName.bak'
GO

Then from SSMS you can select to backup the database on the mounted backup device and that’s it!

However, there is one consideration; the service account which runs the SQL Server Instance needs to have read/write permissions on the network folder (shared permissions).

In the opposite case you will receive the following error message:

I hope you found this post useful!