Restoring or Creating Mapped Drive Sql Server Database

OK, so our staging environment has been eclipsed in drive space by our production database. This creates a problem, because the database is too big for the Staging server, so now we can't restore. SO, in the interim, I restored it to a mapped drive on a different server on our network. What? Isn't that going to cause problems? Isn't it going to be slow?
Well, maybe.. but our staging environment doesn't get a lot of hits, it's just internal. No outside access, so we figured it's OK for now while we are going to re-build the box soon anyway. DON'T do this for a production environment.
So, here is how you do it.
1. Get the drive mapped and visible in Explorer (my computer)

2. Ok, drive is visible to the user. Now, can Sql server see that drive? Probably not, most likely NOT. Here is how I got the drive visible to sql server...

a. first test to see if sql server can see the drive.. run this in query analyzer
exec master..xp_cmdshell "dir P:" (whatever your drive letter is)
You will probably get an error like this : "The system cannot find the path specified."

b. Turn on the drive visibility for Sql server. Run this..
exec master..xp_cmdshell "net use P: \\ServerName\Directory"

if things went right, you get this message:
"The command completed successfully." Now, move onto the next step.

if they didn't go right.. Then, well, check the documentation for the "net use" command, and make sure you are in accordance.
ALSO, make sure that you are running the command while you are ON the server where the mapped drive is to be USED from in other words, don't do it remotely.


c. Test Connection to mapped drive.
exec master..xp_cmdshell "dir P:"
Hopefully tested out just fine..

3. Restore database to mapped drive.
Congrats, you have now gotten you sql server able to see the mapped drive, now you need to restore your database to the drive. But you get this error (Or something similar when you try to restore):

5110 "File 'file_name' is on a network device not supported for database files."

This is expected. Microsoft by default, doesn't want you to restore a database (or even create for that matter) on a mapped drive, "Don't do it" that's what they say. By default, you cannot create a SQL Server database on a network file share.
So, with all the work you have done .. MSFT won't let you finish the job.. Sorry. Well, that was fun! Who loves wasting time? I know I do!
OK, stop crying. Here is your solution. You ever played with trace flags? I know I have :). If you haven't, I guess you can sort of think of them as ports on a firewall (it's a stretch) . They are either on or they're off. You have to turn them on, or they are on by default and you have to turn them off.
For this instance, you need to turn on trace flag 1807. Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files. (Yes!)
OK, do this in Query Analyzer:

--BEGIN SCRIPT
use master
go

dbcc traceon(1807)

--END SCRIPT

Alright, now check to make sure that it's turned on:


--BEGIN SCRIPT

use master
go

dbcc tracestatus(1807)

--END SCRIPT

OK, the above should return a row that will show a status of "1". If the Status is "0". Then, maybe you somehow ran the first script incorrectly.

That's it.. You can now restore, or create your database as you normally would . Enjoy!