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!