An issue where SQL Server Management Studio doesn’t quite live up to expectations.
What I wanted to do:
I wanted a copy of a database that lived on a different server. I wanted the database to live on my own machine so I could do dev stuff with it. What I tried to do is backup the database and copy the backup to my own machine.
Unfortunately when you use the backup database wizard, the wizard only displays locations on local hard drives only. Not even mapped network drives show up:
In this dialog, there is seemingly* no way to specify a network drive! And in my case, there was no space to do backups on any of the local drives that were displayed.
What I ended up doing
I went ahead and specified all the backup settings using the wizard (saving the backup to a local drive). But before hitting OK, I pressed “Script” instead:
And that gave me the script shown below
BACKUP DATABASE [AdventureWorks]
TO DISK = N'C:\Temp\AdventureWorks.bak'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10,
NAME = N'AdventureWorks-Full Database Backup'
GOOnce I had the script, I was able to change the location of the backup to a shared drive on my local computer. I specified the location using a UNC path (e.g. //MYSERVER/mySharedFolder/mybackup.bak). I ran it in a query window and it worked perfectly.
Related Microsoft Connect issues
Microsoft Connect issue 124707 sort of mentions it. Issue 126396 is closed as being fixed, but I don’t understand that.
* Update!
Ahh. Now I understand that. Turns out that you can specify a network path through the UI, but you can’t browse for it. In the Locate Database Files dialog specify the full UNC path in the file name text box. It works and more embarrassingly, it’s in the manual.
