commvault.com commvault.com

Automated backup/restore testing of a MSSQL Database

  • 3 February 2021
  • 4 replies
  • 3177 views
C
Rank
Badge +1

I currently have a workflow setup to do a weekly backup/restore of some Windows filesystems, which works great.  Is it possible to do the same thing with a Workflow and backup/restore a single Microsoft SQL database (out of place)?

 

I cloned the workflow I have for the Windows files and was trying to modify it for MSSQL.  It looks like I can change the agent type to MSSQL and even set an instance name.  However, I’m not sure how to define a specific database.  As I’m typing, I just thought of maybe a possible solution?  Maybe I need to create a new backup set with the database(s) I want to test?

 

Any recommendations on the best way to automate these backups/restores?

4 replies

E
Rank
Userlevel 4
Badge +10

Hi! I’ve been dealing with something similar recently.

 

Here, my example of SQL backup/restore workflow (still in early stages!).

Phases: Backup → Out-of-Place Restore → CHECKDB → Email

Backup phase: in order to backup specific db (or more of them), you simply create a user defined subclient and put db in it.

Restore phase: this one is a bit trickier. The easiest way is to use ‘Save As Script’ xml exports ‘Execute’ activity. Use your Java GUI: <client> → <SQL Server> → <Instance> → All Tasks → Browse and Restore. Select ‘Advanced Restore’, dest. client, instance... and click ‘Save As Script’.

Use ‘Execute’ workflow activity (QCommands → Operation → ‘Execute’) and put xml content from previous step into inputXml.

Finally, replace values (client, instance, db, path...) in xml with user inputs.

Example:

<clientName>yoursqlserver</clientName>

change to

<clientName>xpath:{/workflow/inputs/SQLClient/clientName}</clientName>

 

That’s it!

 

There is also a workflow called ‘SQL-Documentum Restore’. You can download it from Commvault Store and modify it to your needs.

Mike
Bart
Rank
Userlevel 6
Badge +13

Such a workflow would be handy for automating "refresfes". (I asked the same question on the old forum)

 

Henke
Rank
Userlevel 4
Badge +13

I currently have a workflow setup to do a weekly backup/restore of some Windows filesystems, which works great.  Is it possible to do the same thing with a Workflow and backup/restore a single Microsoft SQL database (out of place)?

 

I cloned the workflow I have for the Windows files and was trying to modify it for MSSQL.  It looks like I can change the agent type to MSSQL and even set an instance name.  However, I’m not sure how to define a specific database.  As I’m typing, I just thought of maybe a possible solution?  Maybe I need to create a new backup set with the database(s) I want to test?

 

Any recommendations on the best way to automate these backups/restores?

@Chris M would you be able to share that workflow for Windows that you use?

BR

Henke

C
Rank
Badge +1

Hi! I’ve been dealing with something similar recently.

 

Here, my example of SQL backup/restore workflow (still in early stages!).

Phases: Backup → Out-of-Place Restore → CHECKDB → Email

Backup phase: in order to backup specific db (or more of them), you simply create a user defined subclient and put db in it.

Restore phase: this one is a bit trickier. The easiest way is to use ‘Save As Script’ xml exports ‘Execute’ activity. Use your Java GUI: <client> → <SQL Server> → <Instance> → All Tasks → Browse and Restore. Select ‘Advanced Restore’, dest. client, instance... and click ‘Save As Script’.

Use ‘Execute’ workflow activity (QCommands → Operation → ‘Execute’) and put xml content from previous step into inputXml.

Finally, replace values (client, instance, db, path...) in xml with user inputs.

Example:

<clientName>yoursqlserver</clientName>

change to

<clientName>xpath:{/workflow/inputs/SQLClient/clientName}</clientName>

 

That’s it!

 

There is also a workflow called ‘SQL-Documentum Restore’. You can download it from Commvault Store and modify it to your needs.


Thank you so much for your help on this.  I’ve got this setup and it’s working well.  The only thing I need to add is for it to cleanup the restored database after it’s successful.  Other than that, it works great!

Reply


Terms & ConditionsCookie settings