We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL.
I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically.
When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore.
The backup and copy both suceed. The restore claims to suceed. If I review the job history for each step, it says that it skipped the log file and then reports that it did not fina any log files to restore.
Message
2006-11-06 05:00:01.92 Skipped log backup file. Secondary DB: 'MyDemo', File: 'D:\MSSQL\Backup\MyDemo\MyDemo_20061106115619.trn'
Message
2006-11-06 05:00:01.95 Could not find a log backup file that could be applied to secondary database 'MyDemo'.
2006-11-06 05:00:01.96 The restore operation was successful. Secondary Database: 'MyDemo', Number of log backup files restored: 0
Any ideas or suggestions?
A little more info for you. I can manually restore the successive log file backups without any errors. However, the output says that there were 0 pages processed. The successive log files are being created empty. Well, these are test databases, so there are no active transactions ocurring, so no big surprise, right? I updated a lot of data in the tables, and the next log file is ....... empty.
So even with committed transactions ocurring, the tran log backup is still empty.
|||I found the problem. It was a security setting that the Log Shipping wizard does not give you the ability to change. It defaulted to using Windows security (the account used by SQL Agent) for the connection to the Monitor server to update the state of the restore process. Because we use local system accounts, not domain accounts, for SQL Agent, it was not able to update the Monitor.
My scripts were based off of the first log shipping instance that I set up, so the option to change it there wasn’t included in the script generated by SQL Server. Since I originally set it up with the primary, secondary, and monitor on my local machine, it worked fine.
In the wizard, there is a setting for the account that the monitor should use, but it only affects the primary's connection to the monitor, not the secondary's connection.
