SQL Server – Creating a Database on a Local Folder Mapped as a Drive

sql serversql-server-2012

I have mapped a local folder as a drive, as follows:

subst E: c:\MyFolder

I need to create a database on that drive, but it fails:

CREATE DATABASE MyDb
ON(NAME=MyDb_dat,FILENAME='E:\MyDb.mdf',SIZE=30,MAXSIZE=45,FILEGROWTH=1)
 LOG ON(NAME=MyDb_log,FILENAME='E:\MyDb.ldf',SIZE=10,MAXSIZE=20,FILEGROWTH=1)

Here is the error message:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\MyDb.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Restarting the service did not help at all. How can I have SQL Server create a database on the mapped folder?

Edit: why do I need this.

This database is a local Dev Edition sandbox to run automated tests against. Historically, we used to create such database on RAMDisk, so that tests run fast. Currently we are getting faster workstations with SSD drives. When everyone have them, we shall do away with RAMDisk altogether, and just create the sandbox on C: drive.

For now, while some team members still have to use RAMDisk, I'd like to create my sandbox on E: drive, without setting up RAMDisk. This is why I tried subst.

Can suggest a better approach?

Best Answer

subst is scoped per session. Your command and the instance run on different sessions.

Creating a database on a subst virtual folder does not sound like a good idea. What are you trying to achieve?