Combining log shipping and regular Transaction log backups

Question

Combining log shipping and regular Transaction log backups

SQL Bandit

SSCommitted

Points: 1757
More actions
September 13, 2022 at 2:40 pm

#4087131

Hi there,
This is more of a confirmation question really.
We perform Full backups weekly and transaction log backups every couple of hours via commvault.
I take it there is no way to have standard DB and transaction log backups while having log shipping to replicate data to another DB as part of a large DB migration is there?
Am I correct in thinking you just can't have transaction log backup and log shipping. As this will generate confusion in the logs as the log shipping is actually performing a backup.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23013 More actions · Answer 1

If you have access to the log files taken using Commvault, why can't you use some homegrown method to restore those to your target database? We use the OH scripts to take our backups and at various points over the last few years we've used some PowerShell scripts to restore those logs when we've needed to migrate things. You don't necessarily have to use the native log shipping to ship your logs.

As an aside, "every couple of hours" seems like a lengthy gap between log backups. Can you afford to lose that much data?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

SQL Bandit SSCommitted Points: 1757 More actions · Answer 2

Thanks for the reply Neil,

We have sql server mirroring to a DR DB Server and commvault backups Full (weekly) Transaction logs (every 4 hours)

Apart from plucking up the transaction logs from commvault can you think of any other way to replicate data to DB Server we are trying to migrate to?

Is there anything in "publication and distribution" worth investigating? Sorry if that's a newbie question? I just don't have time to burn going down potential fruitless rabbit holes.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-publishing-and-distribution?view=sql-server-ver16

If the answer is no then far enough.

SQL Bandit SSCommitted Points: 1757 More actions · Answer 3

Hi Neil

I've managed to find someone with a bit more experience of Commvault than me.

I've got Commvault to perform a full restore from the media agent and then hopefully I'll get it to schedule the restore of the transaction logs.

Thanks for your help.

Neil Burton SSC-Insane Points: 23013 More actions · Answer 4

I'm glad to hear that you've found a solution. As far as synchronising data during migrations, replication or log-shipping (one way or the other) are probably the most straightforward options. Both of them have their pros and cons but either could have been a valid choice depending on your situation.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Michael L John One Orange Chip Points: 27065 More actions · Answer 5

Log shipping IS transaction log backups.

Here's a suggestion. Instead of taking the backups with Commvault, use native SQL backups to a disk or UNC, and then set up Commvault to backup the folder.

I know of no way to automate a restore to a different server from Commvault. I tried, and gave up.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/