Access a Network Drive Using SQL Server
Published: Dec 25, 2015
This is a handy hint as to how to access a network drive from within SQL Server. It just happened to be something that I was using in some code and was asked what the code was doing.

As a rule, SQL Server cannot see anything but attached drives. Therefore even though you can log onto the server yourself and map a network drive, SQL Server will likely not be able to see it, ruining the plans you’d just come up with. Well there are ways around this.

What you need to remember is that when you map a network drive in Windows, it does this only for the user who’s logged in and not for everyone accessing the machine.

This means that you can add a network drive but this is only for your login and therefore SQL Server, which will (hopefully) be using its own Windows account under which to run the SQL Server Service, won’t be able to see it.

This gives us two options to pursue. Both of these options have security requirements and therefore you may only be able to do one or the other, but hopefully at least one will work for you and be allowed under your security settings.

The first option is simple… if the network drive can only be accessed by the user who created it, then simply log in to the server using the SQL Server Service Windows account.

Doing this means that you create the mapped drive under the SQL Server account and therefore SQL Server will now be able to access it as expected.

The second option involves xp_cmdShell, which can be a contentious point, but it’s actually the method I prefer (as I don’t like to remote into any server with any user if I can help it due to that unnecessarily using system resources).

Also I find that this is cleaner because you can create the network drive at will, dynamically if needed, and then drop it at the end after using it within any code (such as backups etc).

The basic code for this is as follows:

exec xp_cmdShell 'net use Z: \\myNetworkShare\myFolder'


So, for example you could create a network drive to a folder, take a backup, and then drop the network drive again…

exec xp_cmdShell 'net use Z: \\myNetworkShare\myFolder'
go

backup database myDatabase to disk = 'Z:\myDatabase.bak'
go

exec xp_cmdShell 'net use Z: /delete'
go


If you want something more generic you could do as follows:

declare @cmd varchar(500)

select @cmd = 'net use Z: \\myNetworkShare\' + db_name()

exec xp_cmdShell @cmd
go

backup database myDatabase to disk = 'Z:\myDatabase.bak'
go

exec xp_cmdShell 'net use Z: /delete'
go


See… simple yet effective… and also quite clean.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron