commvault.com commvault.com
Solved

SQL Transaction Log backup

  • 1 April 2022
  • 6 replies
  • 4901 views
N
Rank
Badge +4

Noob SQL query.

 

Can SQL Transaction log backups be performed using different backup solutions, such as Commvault and native log backup?


We have a customer who performs tlog backups every hour and a daily full backup using SSMS native backup tool (not a copy-only backup).
AppAware is also used to run a full Snapcopy backup of MS SQL on commvault.
The customer has requested that transaction log backups in Commvault be performed every 15 minutes in addition to the hourly native log backups. 

My understanding is that if we use separate backup solutions, we might end up with one solution containing one chunk of log backup data and the other containing another chunk of data.
As a result, neither solution has a consistent chain that allows for point-in-time recovery.
Is my understanding correct?

Could someone please suggest the best solution to this?

Should I ask the customer to disable native backups and rely solely on Commvault due to its ease of use in restoring? 

 

Thank you.

icon

Best answer by Scott Reynolds 1 April 2022, 17:18

View original
If you have a question or comment, please create a topic

6 replies

S
Rank
Userlevel 6
Badge +14

Hello @Nikramak 

You are correct the issue with having different methods to perform Tlog backups is the tlog LSN chain must remain intact. When you are performing a restore SQL requires all tlogs up to the time you are restoring to. If this restore is performed in application A and there is a tlog that was ran in application B the chain is broken and the restore will not complete.

CV provides a mechanism to detect a log backup performed outside of CV then it converts that backup for that database to a full backup. This ensures the data can be restored. While this can be disabled it prevents restores using the native method and also CV since logs are in two different locations. Conversion rules

https://documentation.commvault.com/11.24/expert/61499_automatic_conversion_of_non_full_backups_into_full_backups.html

 

In short its best to perform log backups in one single method. Allowing CV to perform log backups allows flexibility restores and also data management of that data so its best to have them stop the native SQL backups. 

N
Rank
Badge +4

Thanks for your feedback @Scott Reynolds . 

 

To test this, I scheduled tlog backups to run every 4 hours on a test SQL server, and so far only the tlog jobs that run immediately after the Daily fulls have completed - for some reason, I don't see the rest of the jobs in the job history, nor failures or conversion to full.

To be clear, fulls are performed as part of the VSA Appaware workflow; could the tlog conversion to full be skipped due to the inability of the tlog to initiate a VSA backup? 

 

 

In addition, I see that the native log backup is running smoothly.
If the commvault tlog job finished, it means that it truncated the logs, which means that any further native log backups on the SQL server should have failed.
Is there a consistency check feature in the SQL native tool that I need to enable in order for it to detect a broken chain?

 

S
Rank
Userlevel 6
Badge +14

@Nikramak  Since this is appaware there is no conversion the databases will be skipped. That is unique to appaware. As for this statement “If the commvault tlog job finished, it means that it truncated the logs, which means that any further native log backups on the SQL server should have failed.”

That is not true. yes the logs are truncated but you can still run a backup and there are new logs generated constantly.

If you goto the properties of the job (double click the backup job) you will see status tab which will show if the database has convert/skipped etc and the reason.

 

N
Rank
Badge +4

thanks heaps for the clarification @Scott Reynolds.

 

If I may, I have another question about transaction log backups.


How long do you recommend keeping SQL transaction log backups?
I understand that this is dependent on the needs of the business, but are there any facts to be aware of that may assist in determining what is required?
 

We typically take daily full snaps (intellisnap - NetApp) that are retained for 7 days, 4 weekly backups, and the backup copy job to run once a month that are stored in the Azure. 

 

Thanks!

 

S
Rank
Userlevel 6
Badge +14

@Nikramak 

Yes this is all dependent on the need to restore to a point in time. Transaction logs are required for point in time restore. So it really only depends on how long that ability is needed. Usually we see many customer have a specific time for tlog (say 30days or so) so they can always restore to any time within last 30days then retain the fulls snaps for extended periods or time. In your case you probably wont want to store all these tlog backups in azure so 4 weeks would most likely suit your config.

But there are no restrictions here its really all dependent on the business need.

 

N
Rank
Badge +4

Thanks @Scott Reynolds 

Terms & ConditionsCookie settings