Powershell to do automated SQL Server restores

Last post 08-01-2019, 11:48 AM by Yashwanth. 18 replies.
Sort Posts: Previous Next
  • Powershell to do automated SQL Server restores
    Posted: 07-22-2019, 3:59 PM

    We have a desire to do verification of all of our backups at least once a week using automated restores.  In past jobs, I found this easy as I had access to the backups and could either use a SQL Server Agent job or a Powershell script.  With CommVault, though, I am finding this difficult.  I can get a batch file and an XML file for a specific system, but I need something more robust.  In essence, I want to get a list of all our agents, like 100 instances, and several of those split into multiple AG groups, start at 8 AM each day from where I left off, and run until 5 PM doing restores.  The loop would restore the database, run DBCC CheckDB, delete the restored DBs, and go on to the next instance as long as there is time.

     

    However, the Powershell docs are fragmentary on how to do this. I cannot imagine we are the first customers who want to script a restore process like this.  Without resorting to the batch files, is there not a way to write a Powershell script to do this?  Are there any examples to use as reference that we could adapt?

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-22-2019, 4:13 PM

    Yes I have found CV Powershell module to be very rudimentary at best. I've resorted to using their CLI cmds and wrapping that around Powershell using invoke-command. Have tried looking at Workflows? I know that's a beast but that's another option.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-22-2019, 4:18 PM

    Even the CLI cmds are a problem because you have to scrape manual output to determine whether an error has happened and manually modify the exit code so that the caller can see whether the job succeeded or failed.  

     

    I guess I am at a loss as to why there are so many backup commands but not a restore command.  I am a SQL DBA and not the primary CV admin (storage team does that), but I am still accountable for the integrity of the backups.  Using a generic "qoperation" command with an XML file seems so outdated for something that is a simple command in basic SQL.

     

    No idea what workflows are, our CV admins have never mentioned them.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-22-2019, 4:41 PM

    Yes that is true. I've had to rely on parsing logs files etc to determine errors.

    Workflows is the automation engine of CV. You can practically do anything with it. The challenge is understading how to call the objects etc. It's basically Object Oriented programming. Seeing that you're a DBA....getting info from CV is all queries...

    Workflows is not for the faint... and takes some time to get good at it.

    https://documentation.commvault.com/commvault/v11_sp15/article?p=49530.htm

     

    The other option is REST API but even then you're still dealing with xml content

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-22-2019, 4:47 PM

    I assume that these workflows don't do well with stuff outside of CV.  Being able to do actions in SQL Server after a restore is critical, as well as the need to run the loop from the database to know which job to run where.

     

    Is there any documentation on the REST API?

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-22-2019, 5:08 PM

    That's the whole idea.... you can do just about anything.... as long as CV has a way to integrate whether it's running a batch,powershell,python....whatever API you have... you just need to know how to reference it and handle errors.

    Here is the library

    https://documentation.commvault.com/commvault/v11_sp15/article?p=45619.htm

    Here is a sample restore API

    https://documentation.commvault.com/commvault/v11_sp15/article?p=45606.htm

    But it's for simple file restore.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-26-2019, 2:13 PM

    Data Verification is enabled by default now in V11. You could leverage that to verify the data an no need for custom scripts.

    https://documentation.commvault.com/commvault/v11/article?p=12567.htm

    By default, the deduplicated data verification is automatically associated with the System Created DDB Verification schedule policy. This schedule policy runs an incremental deduplicated data verification job every day at 11:00 AM on all the active DDBs in the CommCell that have the Verification of Existing Jobs on Disk and Deduplication Database check box selected. However, you can also run the data verification job at any time.

     

    AUX copies also validate the data becuase it perfoms a resoter/read operation of the data.


    Gary Seibak
    Technical Account Manager - Commvault
  • Re: Powershell to do automated SQL Server restores
    Posted: 07-26-2019, 2:20 PM

    That isn't enough for DBAs.... they need see the data actually restored successfully.... I deal with them in my environment as well.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-26-2019, 2:22 PM

    Does CommVault provide guarantees that a verified backup will 100% for certain restore if used in SQL Server? This is the goal, assurance that the restore process will succeed.  Historically DBAs only feel able to give that guarantee if a backup is actually restored.  I once had a transaction log backup for a chain fail to restore and therefore I could not do a point-in-time restore.  I had Microsoft in on a call, and they searched everything.  Restore verifyonly worked, all of the standard MS checks worked, the backup was well formed -- it just would not restore, and there was nothing I could do (or could have done to prevent it).  I would only want to rely on this if we knew beyond a shadow of a doubt that the CommVault verification process guarantees the ability to restore into SQL.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-26-2019, 2:25 PM

    oalexis:

    That isn't enough for DBAs.... they need see the data actually restored successfully.... I deal with them in my environment as well.

     

    Given that the alternative is a disaster for the company, and because that is literally our most important task for our jobs, that seems like a reasonable expectation.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-28-2019, 1:30 PM

    Per the information provided so far, your previous "chain" restore failure will not be ensured thru "verify only" since "verify only" is to ensure that the given backup data (a transaction log backup for example) is structually sound by checking the format of data and a few header information.  The Commvault SQL agent has the same "verify only" option as part of restore jobs but the chain calculation for the required logs will happen before the actual data restore flows into the destination to make sure that the given PIT.  Do you know the exact failure on the previous SQL restore failure just to make sure what I stated in the above is correct?

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-30-2019, 8:18 AM

    I was using a "for instance" from a prior job because one of the replies kind of made it sound like DBAs expect a little too much verification for their backups, and I wanted to explain why that verification is important to us.  That case, many years ago, was a time when the absolute only way anyone could have seen a problem with the backup files was to restore it, because it passed all verification steps that I could do short of a full restore.  Because of that backup failure, we incurred data loss.  This matters.  Since the CommVault verification process was offered as an alternative, I am asking how in depth its validation is.  If it does not 100% ensure that a backup can be restored, it is no more useful to me than the SQL RESTORE VERIFYONLY command, and we are back to needing to run automated test restores which prompted this thread in the first place.

     

    Unfortunately, this thread has convinced me that my only option is to save those batch files and have around 100 XML files for all of our instances of SQL and run them in a loop with the rest of our scripts.  If anyone from CommVault research is listening, your product desperately needs a simple "RESTORE" command akin to what DBAs use in SQL Server so that we can run a command in a PowerShell or other sort of script.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-30-2019, 10:28 AM

    you dont have to save one xml file for each sql database or instance. You can overwride any parameter in the xml file on the command line as shown here. Put -parametername and then value. This way you just need one sample xml and keep reusing it

    http://documentation.commvault.com/commvault/v11/article?p=45127.htm

     

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-30-2019, 10:43 AM

    The problem with that approach is file name.  We have DBs that have over 10 filenames, and an instance has 10-30 databases.  So the parameters cannot accommodate that many variances in a file name.  Easier to just make a static XML than deal with 10-20 parameters which probably won't even fit in the command space.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-30-2019, 11:09 AM

    Not sure if this makes a difference in your situation but you can also wrap the commands in Powershell and use one xml file. You'd then have to create a txt or csv file with all the param values for each restore. Then do a foreach for each line. In the end it's still one xml file your're using.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-30-2019, 11:16 AM

    For all of the database and files we would be doing, we either have to maintain them in a DB or in the XML file.  Somewhere, we have to manage all those parameters.  At this point it would be easier just to do XML files.

     

    For those wondering, DBAs working traditionally normally would do a RESTORE VERIFYONLY to get the parameters straight from the backup file and plug them into the command, so no management of parameters like this would be necessary.  Since we can't do that we have to store all of the files needed to do a restore somewhere, either in multiple XML files or in a database that then is cycled through.

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-30-2019, 2:05 PM

    I feel you could achieve this using the existing rest apis wrapped around powershell and that will not require XMLs.  I will see if I can come up with an example of such and will document in our BOL.  Here the requirement is to have a script that kicks off a restore (I think you kind of concluded that "verify only" will not ensure the validity of data all the way to the bits so just regular restore) and should not require xmls to manage paramenters.  What will be the minuimum amount of input you want to put in here besides obvious instance and db information?

  • Re: Powershell to do automated SQL Server restores
    Posted: 07-30-2019, 2:16 PM

    So our script would loop through every instance for every database in production (we have about 100 prod instances and over 1000 databases) and restore it.  In Powershell I would do a DBCC CheckDB and a delete for each database, and I can do that now.  I just need to be able to call a command that passes all of the source information and a static target that will be different in every case from the source, and then move all of the files to a specific location on the target that may well be a different drive than the source.  So source information, target information, and file move information seem most obvious.  I also would want to restore the most recent backup, regardless of whether that was full, differential, or transaction log.  And then it has to return pass/fail so I know whether the CommVault command succeeded.  I don't necessarily need to know the error, though that would be nice, but I do need to be able to fail the job so I can be notified and I can review what happened in the CommVault logs.

  • Re: Powershell to do automated SQL Server restores
    Posted: 08-01-2019, 11:48 AM

    We've couple of examples for SQL Server Restores here. Hope it helps:

    https://api.commvault.com/?version=latest#7fcec94e-6f0e-1a2a-80cd-1ceae31a86ca

     


The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2019 Commvault | All Rights Reserved. | Legal | Privacy Policy