I recently had to download a scrubbed copy of a large Production database for the developers to troubleshoot an issue. Normally, I would have purged much of the older data and run a shrink before handing it over, but they convinced me that all of the data was required in this specific instance. I had an 85 GB database and only 70 GB free space on the Dev machine. After a bit of research, I found an excellent article by Taylor Gibb over at MyTechMantra. While I normally shun XP_CMDSHELL like a stray vial of smallpox, I discussed its limited use with the rest of the data team and we decided it would be okay.
What I ended up doing was adding a mapped drive to SQL Server. The drive went to a share that had the scrubbed backup and I both restored from there and created the MDF and LDF files there. I enabled XP_CMDSHELL only long enough to map the drive and then again to unmap the drive when the database was no longer need for troubleshooting. It did run slow, but it was good enough to troubleshoot the issue at hand. I would only recommend this option for a Dev environment, and only when no other options are available.
There are a few important things to note here:
1. The new drive will NOT survive a restart, even with the /persistent:yes switch.
2. The new drive will NOT show up in xp_fixeddrives.
3. XP_CMDSHELL is a HUGE security hole that should only be used sparingly and then *immediately* disabled when finished.
4. See # 3.
USE master; GO EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE; EXEC sp_configure; -- Check that nothing is using the proposed drive EXEC XP_CMDSHELL 'dir z:' -- Map the drive -- EXEC XP_CMDSHELL 'net use z: \\server\share' -- Disconnect the drive -- EXEC XP_CMDSHELL 'net use z: /delete /y' -- Check that the drive is now mapped EXEC XP_CMDSHELL 'dir z:' EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE; EXEC sp_configure;