0

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.

Community
  • 1
andyjohnson
  • 131
  • 2
  • 11

2 Answers2

1

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.

  • Is the database in the FULL Recovery Model?
  • Has a FULL backup been taken?
  • Do you have a LOG Backup job in place? If so, be sure to remove this database from that job. Log Shipping will handle the LOG Backups for you.

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:

  • C:\SQL Backups\LS
  • \MyServer\LS

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!

Steven
  • 176
  • 3
0

Try creating an additional cluster disk to backup onto, in case there are issues writing to one of the nodes local drives - that way the log shipping should always work. Failing that, backup and restore the log shipping from another separate remote server.

steve
  • 1