I am trying to configure log shipping from a cluster of two Windows Server 2008 R2 x64 machines running SQL Server 2008 R2 to a single machine running the same OS and DB. Setup appears to go okay (no errors) but at the scheduled time I get the following pair of errors in the primary machine's SQL Server event log:
BACKUP Failed to complete the command BACKUP LOG MyDb. Check the backup application log for detailed messages
Error: 3041, Severity 16, State 1
Questions: 1. Where is the 'backup application log'? 2. How do I find out what error 3041 is?
SQL Server and the SQL Server Agent are running on the primary and secondary under a domain account. I created a share on the secondary as \secondary\SqlLogShipData, gave full control to the domain account, and added two sub-directories: Backup and Destination.
When I configured log shipping I gave \secondary/SqlLogShipData\Backup as the network path to the backup folder, and for the secondary I give \secondary\SqlLogShipData\Destination as the destination folder for copied files.
I put the backup folder on the secondary because the primary is a cluster and I want the log shipping to work when either node is active. I also tried specifying a directory that is local to the primary but this made no difference.
There are no relevant errors in the secondary's sql event log.
My guess is that this is a permissions issue, but I'm not sure why. I'd appreciate any help that you may be able to give me.
Best Answer
Configuring Log Shipping on a Cluster is a pretty straightforward setup. I've used it many times.
Before we begin, be sure to remove the exiting Log Shipping setup since it's not working. Remove the Secondary, and then remove the Primary.
Prerequisites
First, let's get some pre-requisites down.
Primary Setup
OK, now let's try to walk through your Log Shipping setup.
First, just try to get the Log Shipping Primary working.
Go back into your database properties and check 'Enable this as as primary database', and then click the Backup Settings button.
Here you need to specify two things: the network path to your LOG Backups, and the local path to the same.
On my server, it is something like this:
Log Shipping Folder Permissions
Next, locate this folder in Windows Explorer, right-click, choose propertities and select the Permissions tab. Here, you will need to add your Primary SQL Agent Service account and ensure that it has Read and Write permissions on this folder.
Next, select the Sharing tab. Here, you will need to share the folder and ensure that the Secondary SQL Server Service account has Read permissions on the share.
Test The Log Shipping Backups
Once you have this in place, let's test your Log Shipping Backups. Look in SQL Agent Jobs and you should see something named LSBackup_YourDatabase.
Right-click the job and run it. Any errors are most likely related to folder and share permissions. Post the details.
Do not configure the Secondary until you get this part working first.
Initialize the Secondary
Once you have the Log Shipping Backup Job running multiple times without and error, run your FULL Backup job once more.
Copy and restore the FULL Backup onto your Secondary server. Be sure to use NORECOVERY.
Secondary Setup
Go back into database properties and the Log Shipping section. Under the Secondary Databases area click on Add. Connect to your Secondary server.
On the Initialize tab choose 'No, the secondary is itiialized'.
On the copy tab, enter the local folder on the Secondary server where you want to Log Shipping backup files to be copy to. Be sure that the Secondary SQL Agent Service Account has read and write permissions to this folder.
Test the Copy and Resore Jobs
Run the Copy job on the Secondary server. Any errors are most likely related to folder and its permissions. Post the details. Log at the job history. Use the built-in Log Shipping reports to see how far it's getting.
Once the Copy job is working, move on to the Resore job. Same testing steps.
Good luck!