Friday, August 12, 2011

Backing up a Database in a Network Folder

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 \\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!

Until next time!
[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: