SQL Database Backup and Restore Workflow

Last post 06-08-2018, 2:06 PM by smcadams107. 18 replies.
Sort Posts: Previous Next
  • SQL Database Backup and Restore Workflow
    Posted: 05-30-2018, 1:28 PM

    Hello,

    Has anyone created a SQL Database Backup and Restore Workflow..  The SQL Documentum Backup and Restore Workflow that is preloaded in CommVault will not work cause we do not use SQL Documentum.

    I have tried to modified the Demo_FileSystemBackupRestore Workflow and was able to get the backup of the SQL Database to work but when i go to modified restore in the Workflow and under desinationClient there is no option to add dataAgent so i can choose to select Q_MSSQL  like there is under the sourceClient.  

     

    I am trying to automate a full backup of a SQL Database and as soon as the backup completes run a restore of that database to a different SQL server this is something that the customer wants ran everyday at a scheduled time.

     


    Attachment: Capture.PNG
  • Re: SQL Database Backup and Restore Workflow
    Posted: 05-31-2018, 7:27 AM
    Just an idea. You can use live sync for automatic backup and replications https://documentation.commvault.com/commvault/v11/article?p=92005.htm
  • Re: SQL Database Backup and Restore Workflow
    Posted: 05-31-2018, 7:55 AM

    The customer has other databases on that instance that are live databases will this affect those databases. Also when i select the live sync option I only see one database in the box but the instance has 7 total databases and the one I see is not the one I am looking for.

  • Re: SQL Database Backup and Restore Workflow
    Posted: 05-31-2018, 8:13 AM

    Hi,

    I just checked in my environment.

     

    I believe only if database are in full recover mode we can perform a live sync replication.

    This should not cause any issues with source databases because,once backup is complete the replication happens from backups automatically after scheduled backup completes.

    Also you have options to configure destination database in no recovery and standby modes.

     

    -prudhvi

  • Re: SQL Database Backup and Restore Workflow
    Posted: 05-31-2018, 8:46 AM

    THe customer does not want to put the database in full recover mode.. They want to run a full backup of that database then restore it to a different SQL server....  I am just confused as to why I can't modified the workflow or create my own workflow to accomplish this when the option is not there for me to create the workflow that i need.

  • Re: SQL Database Backup and Restore Workflow
    Posted: 05-31-2018, 2:23 PM

    Hi Sean,

    you do not see the option to select Q_MSSQL in the restore activity? on mine I see the option in the dropdown list.

    you could use your own xml from here:

    http://documentation.commvault.com/commvault/v11/article?p=18366.htm#o18368

     

    Out-of-Place Restore on the Same SQL Server

    1. Download the out_of_Place_restore_template.xml file and save it on the computer from where the command will be executed.
    OR run the restore from the GUI and before you kick it off do a "save as script" and use that XML in a new activity to replace the current restore activity that is not behaving.

    Gary Seibak
    Technical Account Manager - Commvault
  • Re: SQL Database Backup and Restore Workflow
    Posted: 05-31-2018, 2:37 PM

    I only see that option under the sourceClient i do not have a option to select it under the destinationClient.

  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-01-2018, 10:07 AM

    How do i use that XML in a new activity to replace the current restore activity that is not behaving?  

  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-01-2018, 11:01 AM
    • efg is not online. Last active: 09-13-2019, 10:59 AM efg
    • Top 10 Contributor
    • Joined on 02-02-2010
    • CommVault Tinton Falls NJ
    • Master
    • Points 1,678

    I'm not that familiar with workflows, but have used pre-post scripts to perform similar functions.   You can create a qcommand script that runs the restore (Go through the GUI to prepare the restore, then use save as script)  Then once the script is saved, you can add it as a post backup script from the source DB backup.

    You may need to modify the script to add an absolute path to the location of the "input XML file" as it's referenced only by file name in the script file.

    Just a thought.


    Ernst F. Graeler
    Senior Engineer III
    Development
  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-01-2018, 4:20 PM

    Hi Sean,

    Use the execute activity under the qoperation folder. (see attached image)

    More info here:

    http://documentation.commvault.com/commvault/v11/article?p=49663.htm#o49671

    If you generated an XML using the Save as Script option in the CommCell Console, you can use the QOperationExecute activity to run the XML.

    Navigate to the Inputs tab of this activity, copy the contents of the XML file, and paste them into the Value column of the inputXML input. We recommend this approach instead of specifying the XML path because you can later export the Workflow to another CommCell without the need to set up the XML file in that CommCell.

    I would clone the workflow before you make any changes to the original.



    Gary Seibak
    Technical Account Manager - Commvault
    Attachment: 2018-06-01_1510.png
  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-04-2018, 10:18 AM

    Hello,

     

    This works great can you put multiple save as script in the excute command?

  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-04-2018, 10:46 AM
    • efg is not online. Last active: 09-13-2019, 10:59 AM efg
    • Top 10 Contributor
    • Joined on 02-02-2010
    • CommVault Tinton Falls NJ
    • Master
    • Points 1,678

    I believe you would need to write a "wrapper" script that calls each one of the other scripts created by "save as script"  Also note that when you save the scripts there is an option to save as either an asyncronous script or a syncronous script.   This will determine if the whole process would run as a serial process (one at a time) or concurrent (multiple in the background) You may need to experiment to see if the environment can handle asyncronous.


    Ernst F. Graeler
    Senior Engineer III
    Development
  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-04-2018, 3:16 PM

    I have WFs to restore SQL DBs. What I do is create the SQL restore as you would normally but instead of submitting by clicking on the OK button choose "Save As Script". You will then have the xml script to perform the SQL restore, you can copy / paste that into the WF and replace the literal values with variables where needed. 

  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-07-2018, 11:33 AM

    I have gotten it to work.  I qould like to send a email to myself once the restore completes or if the restore fails is this possible? 

  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-07-2018, 11:42 AM
    • efg is not online. Last active: 09-13-2019, 10:59 AM efg
    • Top 10 Contributor
    • Joined on 02-02-2010
    • CommVault Tinton Falls NJ
    • Master
    • Points 1,678

    Sure, take a look here:  Alerts and Notifications

    You can configure alerts for all kinds of processes including backup and restore.  Alerts can be configured to send e-mail to any specified user...


    Ernst F. Graeler
    Senior Engineer III
    Development
  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-07-2018, 11:45 AM

    Yes, I have my WFs email me with the restore results. You can reference Demo_FileSystemBackupRestore or Documentum SQL DB InplaceRestore to see examples of getting the restore JobID, Waiting for Job Completion and then emailing the results. 

  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-07-2018, 11:46 AM

    Can you configure alerts within the Workflow?

  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-07-2018, 11:51 AM

    Not sure what you mean? In the WF Utilities there is a LogEvent option where you can create a message to appear in the Event Viewer.

  • Re: SQL Database Backup and Restore Workflow
    Posted: 06-08-2018, 2:06 PM

    Bstibal,

     

    I was wondering if you could send me an example of your WF I have configured my WF and it works for the backup and restore but I have configured to send out email if restore fails and if restore completes.  I can't get either of the emails results to work?

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