SQL Database Backup best practice

  • 30 January 2023
  • 2 replies

Userlevel 2
Badge +4

We are currently looking to go Tapeless and I realize that some of our Backups are not setup as best they could be.  One of the places is backup of SQL Databases.  Currently we have the SQL Databases backed up by the SQL Program itself and then we make a backup of the .BAK files.  This adds ALOT of extra time for restores as we have to restore the BAK and then restore the BAK again inside SQL.  I have been told when Commvault was first installed years ago we tried using Commvault for the full backup of the DB’s but it was very slow and had issues.  I am hoping with the newer technology things will be better.  My question is what are best practices.  I have been reading through the Docs from Commvault for SQL server and it seems straight forward, but I am not sure if the backup window needs to be when no-one is using the DB, or if the DB needs to be locked for a few hours as it backs up.  We normally have processes running against the DB’s most of the day and night.  I am sure many companies do this and maybe Commvault doesn’t need to worry about locking.  Sorry its so long winded just trying to get a starting point.  The docs walk you through pretty easily how to set it up and I am of half a mind to just set up a backup for one DB and let it fly, but I do kind of need my job :-)  So any pointing in the right direction would be great.  Here is the link to the docs I am currently reading through  Thank you in advance.


**EDIT**  One thing I am reading about the is Block-Level Backups and it talks about the SQL Server Converts to using Indexing Version 2.  Things like that scare me as I don’t know what our SQL Server is currently doing.  I will need to verify with the SQL DB teams about that.

2 replies

Userlevel 6
Badge +14

Commvault leverages native SQL queries to protect SQL databases so technically SQL streams the backup to Commvault protected storage.  As the backup is a VDI backup, there is no lock on the database so this will not affect users needing to access the database. 

The index version 2 being referred to here is hosted on the MediaAgent, not the SQL server itself.  It is the Commvault index used to track the metadata of the protection jobs.

Userlevel 2
Badge +4

Thank you @Orazan  After doing some rereading until my eyes went cross I figured that the Index Version was for something in Commvault, I couldn’t believe that Commvault would change another program just to fit its own process.  The one thing that I found out the issues we had in the past if the backup job failed it would put the SQL DB in a Single User Mode.  Maybe it was the way the job was setup years ago when it was first done or maybe that issue has been resolved.  But having that info you just gave I am going to give it a shot on one of our QA databases and see what happens.  Maybe even break the job in the middle to see what happens.  But its good to know that basically Commvault is just taking what our system is doing already and just copies it, so we can cut out that middle man of SQL doing it and then we back it up.  Just let commvault grab it from SQL on its own.