I have the above case open, but would like some community input before I start trying my options. As you know, database restores can be quite time intensive and I'm sure I'm not the only one trying to accomplish the same goal. Also, I'm not a SQL guy, but I have worn out the old google machine on this issue. Please excuse any basic questions.
Current Situation:
-- I am backing up a MS SQL Server 2008R2 with Veeam Backup and Recovery 9.5.
---- Application Aware Processing Enabled
---- Require Successful processing
---- Processing Transaction Logs with the job
---- Truncating the Logs
-- This has been working well for quite some time (and saved my tail twice)
New Complication:
-- I have to set up a reporting server to offload custom reporting tasks.
-- The application vendor's upgrade tool will not run if the database has replication enabled. From what I can tell, this is due to additional information added to the database.
-- Log Shipping has been requested as the preferred method to create and update new database.
-- During testing the logs were set to restore every 15 minutes, and the job was successful. No issues when done on the Test Server, or Production Server.
-- Once the log shipping was validated, the schedule for the log restore was set to occur once a day at 7PM.
-- I've been put in a pretty small box on this project, but would like to exhaust all options before I tell management I need a bigger box. I don't like being the guy that says something can't be done.
The Problem:
-- The Veeam backup job runs at 10PM when our last location closes for the day in order to capture that day's work.
---- I can't move this backup to another time right now, I can't backup during production hours.
-- As mentioned, the "Reporting Server" has it's database refreshed via log shipping at 7PM.
---- I can move this time up to about 6PM, but I can't have it later than maybe 8PM if at all possible.
---- It needs to start late enough that anyone running reports has time to finish, but early enough that the next day's reports can be generated. Granted, some of the day's data will not be included in the reports, but it is not enough to make a big difference.
---- This process seems to take a few hours, and runs into the backup window of the primary server. This may or may not be the root problem based on the error message I received, however this information is included in an attempt to be thorough.
---- The error message I received in the Log File restore job seems to point to the transaction log chain being broken.
The Question(s):The restore operation completed with errors. Secondary ID: '34900fd0-bcb3-4e23-985b-75a6e6aa5fe3'
- Am I right to think that the truncating of the logs is what is messing up the log shipping job?
- Is there a way to accomplish the same thing with Veeam?
- Would I be better off just to use MSSQL to do a SQL backup and then backup the dump with Veeam? (If I have log shipping enabled, I will have the read only/stand by copy of the database in production already, so an Instant Recovery from Veeam wouldn't be necessary.)
- Is there another option that I'm just missing?
Thanks in advance!
TL/DR: I need to do SQL Log shipping to create a reporting server, and I suspect the Veeam backup job's truncation task is breaking the log chain. What are other folks doing, or what is the best practice for this?