Mapping a Drive with XP_CMDSHELL

Today I want to talk about one of my favorite tricks that’s not talked about much: mapping a network drive under the SQL Server account.

You see, mapped drives are “per user” – so if you map one on the SQL server, then SQL server can’t see it. Likewise, if you map one for SQL Server, you can’t see it – directly anyway.

What!? (You might say) Why would you even want to do that?

Oh, this one is cool!

Imagine a scenario where you need to backup a….  development database (ahem) to another server or maybe even to your local machine but the SQL Server services account had no permissions there. Well, you can map a drive!

Example Drive Mapping

--Map drive F: to a remote machine:
EXEC xp_cmdshell 'NET USE F: \\DefinitelyNotMyLaptop\SQLBackups'

--List the directory contents:
EXEC xp_cmdshell 'DIR F:'

--Unmap the newlt mapped drive:
EXEC xp_cmdshell 'NET USE F: /D'

Results

Notable Uses

As I mentioned earlier, one of my favorite uses for these mapped drives is backup/restore. I also used it in another life to check for disk space – back in those days we didn’t have any monitoring tools.

BACKUP DATABASE [WordList] TO DISK = N'F:\WordList.bak'
    WITH NAME = N'WordList-Full Database Backup',
    STATS = 10
GO

As you can see, the mapped drive is even available in the SQL Management UI – even when connecting to the server remotely:

Please don’t forget to unmap the drive when you are done! (⊙⊙)