How to Map Network Drive as Fixed Drive? – Interview Question of the Week #258

Question: Can you map a network share as a fixed local drive for SQL Server? A drive-letter mapping does not turn remote storage into a fixed local disk. For service access, use a UNC path with the correct account permissions.

This small problem came up while I was helping a health-check client move a database between servers. My original answer used net use through xp_cmdshell to assign a letter to a share. The command can create a mapping, but the original description promised more than it does.

-- Historical mapping shape only. Do not enable xp_cmdshell for this example.
-- EXEC master.sys.xp_cmdshell
--     'net use Z: \\fileserver\SQLBackup /persistent:no';

-- Prefer a reviewed UNC destination for a deliberate backup:
-- BACKUP DATABASE YourDatabase
-- TO DISK = '\\fileserver\SQLBackup\YourDatabase.bak';

Mapped letters belong to a Windows logon session. A Z: drive you see in File Explorer may not exist in the SQL Server service’s session, even if the account name looks similar. It also remains network storage with network availability and permission requirements.

For a reviewed backup destination, a UNC name such as \\fileserver\SQLBackup avoids the drive-letter ambiguity. Grant the service’s effective network identity the required share and filesystem access, and verify the destination and restore procedure deliberately.

The legacy xp_cmdshell form is shown only to explain the original idea. It is disabled by default, runs under the SQL Server service account for a sysadmin caller or an explicitly configured proxy for an eligible non-sysadmin, and should not be enabled merely to read this interview example. The original embedded username/password pattern has been removed.

Storing live database files on an SMB share is a separate storage design with its own SQL Server support and configuration requirements. Successfully mapping a letter doesn’t establish that the storage is suitable for MDF and LDF files.

I still enjoy these migration puzzles. The useful interview follow-up is, “Which process and which logon session need access?” That usually explains why a path works interactively but fails from a service.

Related: copying files.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.