2008r2 Log Shipping database goes out of sync

Question

I have a production primary SQL2008R2 server (Windows 2008 standard) and a DR secondary SQL2008R2 server (Windows 2008 R2). The database I am working with is roughly 1.8G. I have two servers in a mirrored set for production (A and B nodes). We keep the mirror on production the production A node unless a failover occurs or we are doing maintenance. There are two admin accounts being used for these servers. Node A is admin1 and Node B is admin2.

I have configured log shipping from Node A using admin1 credentials via the wizard. The wizard runs fine and the SQL agent, the lscopy, and lsrestore jobs start. I monitor the processes and note the backup (*.bak) create on the primary server in the log shipping folder.  I then note the backup (*.bak) create on the secondary server in the log shipping folder. I have set the database to restore in standby so that it will be able to take log restores from the log shipping. I then watch the database on the secondary server populate the SQL Management Studio in standby mode. I watch the logs generate and populate on the primary server in the log shipping folder. I watch the logs generate and populate on the secondary server in the log shipping folder.

At this time the log restore process on the secondary scans the log shipping folder but skips the first log erroring that the database is out of sync and will not restore the first log. I have checked the permissions on both the primary and secondary log shipping folders and they are both set for admin1, in addition to the the local groups on the secondary server. All the permissions from the secondary server match the primary server again they are set for admin1. I have attempted to duplicate each step in the wizard using the credentials for admin1. I have manually backed up the database on the primary to the log shipping folder. I have copied the backup from the log shipping folder on the primary to the log shipping folder on the secondary. I have restored the database using the standby option and it failed.

I have noticed that the database owner in the security settings is shown as admin2. The sql agent and sql server processes are set for admin1. I also noted admin1 account in the logins is named dbo but not with the domain credentials. I am wondering if this is a sid related issue. I have noted when I log in with admin2 credentials that I can perform the restore. I have run several tools to auto fix the sids and the reports come up clean. The security settings on the primary and secondary are completely the same. If everything runs fine with admin1 credentials why is the log shipping failing with the admin2 credentials?

---

Thank you

Answer 1

Related thread:

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/e923bccf-b530-4dc9-aa0c-7673833bc489

---

Read my blog (Blog)
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Thanks Mark that did not resolve the issue. I am still working on it.

---

Thank you

Answer 3

Hi,

What error would you get if you manually try to restore the first transaction log file?

Can you give the correct error message. If the secondary server restore job skips a transaction log file, then the possibility is that there was a transaction log backup taken at the primary which is not shipped here. Most probably due to another tlog backup which runs at primary.

Run this command and see if there was any backup taken in the primary 

select * from msdb.sys.backupset where database name like '<Give the name of the db here>' order by backup_start_date desc 

Now check the backup types, after the full backup, which is the first transaction log file? 

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 4

The issue is resolved part of it was a permissions issue and part of it was there was a backup job truncating the logs. Also the restore jobs needed to be run in the object explorer window. I had queries running against the database when it was trying to restore. We ended up setting the restore job in the log shipping to disable all connections to the database so that we would have exclusive access. 

---

Thank you