SQL Restore/Verify Only after each backup

@Robert Horowski , can you link the feature you are referring to?

Does this DBA want a report/alert EVERY time a backup for the database runs, or daily?  Might be simplest to schedule a report to run each day and email this person.

Hi @Mike Struening 

DBA is currently running 

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sql?view=sql-server-ver16

after each backup to disk. He is using maintenance plans to accomplish this.

I’m trying to convince him Commvault is better solution then what he already have, but it is important for him to run verification job after each backup.

There is an option to run verification in Commvault which I hope is what the DBA expects since the name of the feature is the same as in MS docs 😀

It’s described here:

https://documentation.commvault.com/2022e/expert/61506_advanced_restore_options_options.html

Verify Only

Select this option to run a restore validation job.

On successful validation, the restore job returns a successful message in the  Commvault Event Viewer:

"Query Result [the backup set on file 1 is valid]"

The restore validation job validates the following before running a restore operation:

• The volumes have sufficient permission to restore the database files.

• The header fields of the database pages exist at the destination.

• The restore destination device has sufficient space.

Ideally this should run after every database backup and we could generate some alerts or reports for him with the outcome.

Ok, perfect.  This looks like the same feature in the Microsoft doc, just need to find out how this can be done in Command Center.\

Let me chat with some of our SQL support folks.

Hello @Robert Horowski 

You are correct that option is not currently in the Command Center. This along with almost all advanced options are not in the Command Center as the idea is simplified approach in CC.

Can I ask are they doing this even for tlog backups that run frequently? Are they doing this for every database on every server?

Some other possible options here….

Set a post script on the backup that runs a restore with that option enabled.

This would after the backup is complete it kicks off the restore job which would run with the option enabled. All these jobs could be setup for reports, etc to DBA

Live sync. They can setup live sync to ship tlog backups to an alternate server this ensures valid tlog backups as they are restored as soon as the backup completes on the source.

Schedule restores to alternate servers/database names, etc, Can be set to just restore full or full+logs whatever is desired.

Save restore as a script. You can setup the verify only options in java and save it as a script. DBA could run command line restore just modifying some parameters for server name, etc. This can be ran directly from one of the SQL servers in question.

Workflows. There could most likely be some options here to create a workflow that accomplished this, but again still would not be seen by the DBA but they would get reports

Provide limited access to java gui to DBA. would still only see and be able to run backups/restore on only specific servers.

CMR to include the option in CC. We can bring this up in next SQL engineering meeting to discuss if this could be a possible enhancement.

Thank you @Jos Meijer and @Scott Reynolds for your suggestions. I think the most fitting is the one with post script configured in subclient properties, although I have some doubts:

1. If I click this through Java GUI and save it as script, wouldn’t this require a qlogin first?
2. If I save it as a script, it will have a static date/time which would be the same for every  backup job started, so wouldn’t it always verify the same backup job even when new backup job is created?

Other suggestions require additional licenses or manual intervention so I don’t think they are a great fit here. As for the CMR I don’t feel like it’s needed cause to be honest this is the first time a customer asks me for such a solution. The goal here is to set it and forget it with what we have and I wouldn’t want to push it if that is not achievable.

Hi @Robert Horowski 

To answer your questions:

1. If I click this through Java GUI and save it as script, wouldn’t this require a qlogin first?
   
   No, when saving the script you can select several options to assign a user up front:

   

    

2. If I save it as a script, it will have a static date/time which would be the same for every  backup job started, so wouldn’t it always verify the same backup job even when new backup job is created?
   
   Not sure, need to test this properly, but if you edit the XML and remove this section:
   
   <timeRange>
                 <toTimeValue> </toTimeValue>
    </timeRange>
   
   I believe a default value (latest data) should be used, a quick test shows that the restore validate job is initiated properly without this section..
   But the catch for me is that I am testing in a local lab where I don’t have an active SQL agent to use at the moment, so I can’t say for sure at this time if the latest data is selected.

@Scott Reynolds 

This is a bit offtopic but since you mentioned discussing enhancements I could suggest one or two. For example when you go for SQL DB restore, the first option that is ready and set to be launched is overwriting your production DB which is a little scary and some DBA guys even asked me to remove in-place restore privileges for them, because of how easy it is to overwrite their production DB. I would suggest to first focus Out-of-place restore than In-place.

And since I already mentioned Out-of-place restore, when you do this using the same (source) instance it’s a little tedious to change DB name and then every DB file name one by one. There is a very nice feature in Java GUI allowing you to Find&Replace a part of filenames or when doing Hyper-V VM restore you can specify prefix or suffix for VM name. Something similar for SQL DB Out-of-Place restore that would allow you to change DB and Filenames in one step would be very nice.

I took closer look, never mind editing the XML by deleting the timeRange section.

In the advanced restore options there is actually an option to override with latest data 😀

So if you select this and then save the script you can re-use it for all backup validations 

Checked the script and the section has been replaced with only:

<timeRange/>

So this removes any entry of a time stamp and defaults to latest data 👍

@Jos Meijer 

This looks great!

I believe a default value (latest data) should be used

I wasn’t aware of this.

I will test it and hopefully it will be enough to make another customer happy 🙂

Let us know the result 😃

I sure will! In the meantime it’s almost midnight so that’s it for today. Thanks again!

Another option is to trigger an alert on the backup completion and use it to execute a workflow that triggers the restore verify (using the qexecute block and the XML already discussed). Similar to pre/post script method just a different way.

@Robert Horowski  I understand the concern on in-place-restore. I will discuss it with engineering.

The find and replace function being added to Command Center, I believe there is a CMR for this I will find it and add you to the list.

@Robert Horowski 

Just an FYI

We have a CMR for find and replace for restores in the GUI. This could make it into future service packs.

We are still discussing the out of place first option.

Scott

Hi @Scott Reynolds 

I appreciate you letting me know. I suspect that CMR doesn’t have any date yet but please correct me if I am wrong :-)

If anything comes out of the discussion about out of place restore please also do let me know.

Thanks and have a great day!

Robert

@Robert Horowski Correct. We agreed it can be something which should be completed so I would say its very possible to make it in a future service pack of course however there is no guarantee nor time provided.

As for the out of place restore function this is something we are going to continue to monitor and discuss various options as well as some more customer feedback.