July 27, 2010 at 9:09 am
I have a perplexing issue that I hope someone out there can help me with. I recently installed and configured a new 2008 R2 2 node cluster running on Windows Server 2008 R2. We store all of our backups on a network share on a different server, not on the server where SQL Server is hosted. I originally was having trouble getting the backup jobs to write to our directory, however I fixed this by giving the SQL Server service access to the share. Both my domain login and the SQL Server service have access to the share. The backup jobs are all running fine, however if I attempt to restore via the GUI (have also tried via T-SQL), I recieve the error message:
"Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box."
My workaround is currently to copy over the backups to the actual server where the SQL Server instance is hosted, however this won't be acceptable long term. I also looked in the log noticed the following errors are occuring when I successfully perform a restore (after copying the backup file over to the server where SQL Server is hosted):
"The operating system returned the error '5(Access is denied.)' while attempting to 'DeleteFile' on '(network path)\RestoreCheckpointDB12.CKP'".
Error 3634, Severity: 16, State: 2
I also verified the 'BackupDirectory' registry key for each instance on the cluster and it is set currently to the network path where the backups are stored. I should also mention that we have multiple 2005 instances set up this way and performing restores from the network share are not an issue. We have one 2008 development instance where this is also occuring. The problem described above is happening on a 2008 R2 test 2 node cluster.
If anyone has any ideas on how to resolve this, I'd greatly appreciate it.
Thank You,
Adam
July 27, 2010 at 11:16 am
Sounds like the SQL Server service account has write-only access to the directory, and can't read from it. Check to make sure that read access is available in both the share and in NTFS.
July 27, 2010 at 11:33 am
The service has both - it is part of a local group on the machine that the rest of the sql server services are part of(both 2005 and 2008). As I mentioned before, the 2005 instances work fine reading and writing to the network share.
July 27, 2010 at 12:24 pm
If you logon to the node as the SQL service account can you access the network share and read/write to the directory where the backups are?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 27, 2010 at 12:27 pm
Does the error come right away or is there a delay? I don't know about restores, but backups do not like network delays.
July 27, 2010 at 1:02 pm
Thanks for the replies everyone. I'm unable to remote into the node as the service account as that login was not given remote desktop privileges, I'll see if I can get temporary RDP access.
Steve - there is no delay, the error message comes up almost immediately when I click the (...) to add a backup file to restore via the GUI. The error message also comes up immediately when I run the restore command via T-SQL. This doesn't occur on our 2005 instances. The only thing I can think of that changed in 2008, as far as authenicatin goes anyway, is the use of service SIDs in 2008 - however I don't believe that would affect accessing a network share.
July 27, 2010 at 1:30 pm
Problem solved. I've done some reading, and it seems that no matter what you change the default backup directory to when you do the install, the GUI attemps to look in the backup directory buried with all the system files. This is a GUI error only - if you type the full network path in the filename when you add a file to restore, it works. My issue was that I was also attempting to restore from a share that was not the SQL Server's default share that I set up when I did the install (we are upgrading databases from 2005 to 2008) and apparently the service account only had access to it's own share, not the 2005 instances' share, so even when I typed the full path in the filename, I got a failure in this case. If I type the full path to a filename in the instances network share that I pointed to during the install, it works. Thanks for the help everyone.
July 27, 2010 at 1:35 pm
Thanks for the update.
Stupid GUI
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply