September 20, 2012 at 3:48 pm
I am attempting to set up Log Shipping, and after going through the configuration wizard, I continually get the following error when I try to kick it off:
"Cannot open backup device '\\Servername\sharename\filename.bak'. Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
And on the secondary server, I have the following error in the event viewer:
BackupDiskFile::OpenMedia: Backup device '\\Servername\sharename\filename.bak' failed to open. Operating system error 2(The system cannot find the file specified.).
The service account used in the SQL Server Agent on both servers is a domain account with full permissions to the shares in both locations. It completes the initial backup, then goes to the copy and that's where it breaks. Any ideas?
September 20, 2012 at 4:38 pm
thotvedt (9/20/2012)
is a domain account with full permissions to the shares in both locations. It completes the initial backup, then goes to the copy and that's where it breaks. Any ideas?
Besides the share permissions you must also set the ntfs permissions on the security tab 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 21, 2012 at 9:25 am
Yes, sorry. I did those as well. For the sake of testing, the account has full permissions under both tabs, and is the service account used for the SQL Server Agent.
September 21, 2012 at 12:26 pm
can you post a screenshot of the point in the wizard where the error appears?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 21, 2012 at 12:46 pm
I attached a jpeg screenshot. I apologize for size ration, but you should be able to zoom in and scroll over.
Essentially, the error occurs upon completion of the wizard when I select "Okay" to kick off the creation of the Log Shipping config. It performs the backup, but cannot restore it to the secondary.
September 22, 2012 at 10:50 am
Get back to basics, firstly the agent service account on the primary doesn't need any access to the copy location on the secondary server. It doesn't know about it and indeed, should not have access. The agent service account on the secondary must be able to access the share on the primary unchallenged.
On the primary you have created a folder to hold the LS backups, this folder is shared, list the share permissions below
Now list the security permissions applied to the folder
Now list the agent accounts for the primary node and secondary node sql server agent services
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 22, 2012 at 2:15 pm
One further question,
When you set the details on the restore tab did you edit the restore options and enter different paths for the target\secondary data and log files?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2012 at 11:42 am
Perry Whittle (9/22/2012)
Get back to basics, firstly the agent service account on the primary doesn't need any access to the copy location on the secondary server. It doesn't know about it and indeed, should not have access. The agent service account on the secondary must be able to access the share on the primary unchallenged.Bear in mind, I'm merely testing right now. I intend to tighten security once I have a good handle on everything and move to production. I am using the same domain account as the SQL Server Agent Service account for both servers. My intent was to completely rule out rights issues in the OS while testing log shipping.
On the primary you have created a folder to hold the LS backups, this folder is shared, list the share permissions below
Full control
Now list the security permissions applied to the folder
Full control
Now list the agent accounts for the primary node and secondary node sql server agent services
Same domain account. And it also has full control on the secondary for the share under sharing and security tabs.
September 25, 2012 at 12:18 pm
Perry Whittle (9/22/2012)
One further question,When you set the details on the restore tab did you edit the restore options and enter different paths for the target\secondary data and log files?
?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2012 at 2:17 pm
No I didn't. My understanding is that it would use the default restore paths for the secondary instance.
September 25, 2012 at 6:19 pm
Normally i would expect an error 5 if the backup couldn't be accessed during the restore part of the wizard. Haven't managed to recreate an error 2 yet.
What account does the sql server service run under on each instance, is it same as the agent?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2012 at 11:58 am
No. The SQL Server service runs under LocalSystem. Do you think that could be the problem? I wouldn't think so.
September 28, 2012 at 12:56 pm
thotvedt (9/26/2012)
No. The SQL Server service runs under LocalSystem. Do you think that could be the problem? I wouldn't think so.
No, just want to accurately recreate your scenario.
Open up the secondary instance and perform a database restore pointing the restore to the backup file on the primary share, post back the results
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 2, 2012 at 10:05 am
We gave rights to the LocalSystem machine account and it worked.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply