Backing up a Database in a Network Folder

Backing up a Database in a Network Folder

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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.

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 \serverNamebackupFolder 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’,’\serverNamebackupFolderBackupFileName.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!

Until next time!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.