Using UNC share as Backup/Restore location
Question
Tuesday, December 15, 2009 10:42 AM | 1 vote
Hi
I have just set up Sql Server 2008 with SP1 on a new development server. I want the backups to be placed on a network share (\bv\files\TempSql\Sql02Backup), this works fine, I can take a backup without problems. But when I wan't to restore the backup using Task > Restore > Database and selecting from a device (file) and choose Add the dialog tells me that it cannot access "\bv\files\TempSql\Sql02Backup"
Both the user trying to restore and the account that the service is running under has full access to that share. If I paste in the full unc path for the .bak file, the backup is restored.
As this is a development server we will be doing a lot of backup/restore oprations during application development, and I want to control where backups are placed and where they can be restored from. On the old server we had a lot of "Temp", "Tmp" "ADSF" folders floating around in one big MESS :) So I wan't to CONTROL how the developers backup and restore databases.
The share is not located on the server itself but on one of our storage servers.
How should i approach this?
--
Christian
All replies (8)
Tuesday, December 15, 2009 11:25 AM ✅Answered
I was able to access a fileshare through my SQL2008 SP1 GUI, so that is a bit strange. Still, i'd definitely encourage your devs to go the T-SQL route. A simple saved script should work for them along the lines of:
RESTORE DATABASE YourDB
FROM DISK = '\bv\files\TempSql\Sql02Backup\YourDB.bak'
WITH REPLACE
The actual command shouldn't change TOO much between restores.
Good luck!every day is a school day
Tuesday, December 15, 2009 6:21 PM ✅Answered
First, do not use mapped drives. The SQL Service may not see all your user account mappings.
The SQL Service account must have FULL CONTROL over both the SHARE and the underlying NTFS folder to create and restore backups.
SSMS won't directly explore UNC paths but you can paste in the location and it works.
Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
Tuesday, December 15, 2009 11:03 AM
Christian,
Why not mount the UNC path as a netowrked drive (if this is a non-clustered sql server installtion), this should alleviate your not being able to see the UNC path share.
Hope this helps,
Sean
Tuesday, December 15, 2009 11:10 AM
So if I understand you correctly, you are able to restore from your share?
It sounds like this could be an issue with GUI rather than any limitation on UNC paths. Perhaps you could bypass the need for the GUI and write your own T-SQL restore commands? Personally, i prefer to do all my restores/backups using T-SQL as it gives me clarity on what is happening and thats what i'd recommend.
every day is a school day
Tuesday, December 15, 2009 11:13 AM
Yeah, no problems restoring from the UNC path if I pass the full location of the .bak file.
I also think it might be a limitation of the Management Studio. Some of the developers are not that proficient in T-SQL so I need some sort of GUI, I might need to develop that myself then :)
--
Christian
Tuesday, December 15, 2009 10:02 PM
*"SSMS won't directly explore UNC paths but you can paste in the location and it works."
*
Yeah, that was what I figured, from a usability standpoint, that is really a shame...
I'll see how I can overcome this..
Thank you all..
--
Christian
Thursday, May 27, 2010 7:55 PM
I have a similar issue to what Christian is experiencing and SSMS will not accept the UNC Path to where the .bak files are saved by SQL Server Agent BT backup job (full backup or Log file). So, we have to copy/paste the files from UNC folder to a local folder and then run the restore. Any ideas how this can be resolved?
Regards,
Ben Bagheri
Dallas, TX
Friday, June 3, 2011 1:49 PM | 1 vote
I've discovered the Management Studio generally runs as the machine you are on, and not your user account, try finding out how it is attempting to connect then granting that account permissions.
On the machine holding the share (or on a test machine temporarily holding a share for test purposes), run MMC then choose "Shared Folders"
Goto Open Files, you will see what shares are open by whom. Try your restore, and immediently refresh this Open Files window ( F5 )
Under Accessed By you may see a user account, or you may see MachineName$
if it is a machine name you need to grant access to that folder to the machine, when you are doing do before you "Check Names" click on Object Types and add Computers.
You may have to do this again as the account it uses when you are setting it up versus the account it uses when running from a automated script may be different, just monitor the Shared Folders to see what it is being access from.
Edit: You can also use the Event Viewer obviously and check the security logs.