SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OS Error when configuring Log Shipping


OS Error when configuring Log Shipping

Author
Message
thotvedt
thotvedt
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 156
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?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51813 Visits: 17672
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" ;-)
thotvedt
thotvedt
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 156
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.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51813 Visits: 17672
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" ;-)
thotvedt
thotvedt
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 156
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.
Attachments
LogShipping.jpg (43 views, 298.00 KB)
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51813 Visits: 17672
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" ;-)
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51813 Visits: 17672
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" ;-)
thotvedt
thotvedt
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 156
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.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51813 Visits: 17672
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" ;-)
thotvedt
thotvedt
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 156
No I didn't. My understanding is that it would use the default restore paths for the secondary instance.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search