Over the past week I had the pleasure of working with an amazing DBA team at a large insurance firm to successfully prove out how they can transform SQL backups for their 4,000 DBs with Rubrik. As part of this trial we created and ran multiple PowerShell scripts to automate and assimilate Rubrik into their existing processes. In this post, I’m going to share with you what we built.
Before we get to the scripts, first let’s cover the use case. With the 4,000 DBs across 400 hosts, the DBA team had been using traditional dump and scrape with Ola scripts. While this method is used by practically every major SQL shop globally, managing so many scripts against multiple hosts/instances/DBs is a huge administration challenge at scale. Add in silos of target storage with no central source for monitoring SQL backups and automating tasks, and it was clear that both the team and organization needed a new solution.
With Rubrik there is now a better way and we just had to prove it would work at scale. By using Rubrik, the team can remove Ola scripts for backup, multiple silos of storage, backup both DBs and log files to replace everything they did today. But this is where it then gets interesting because with Rubrik they now had a central REST API driven platform enabling access to a central source of truth for the status of all backups and a single target for automation tasks. Even better, with Rubrik SQL live mounts they can instantly access multiple copies of DBs from the backup platform to realize immediate business value.
Need a refresh of a multi-TB critical DB from the last 15-minute log backup to report on the latest data without impacting production? We proved Rubrik could live mount the DB in 30 seconds making it instantly accessible to the business and automate this process end to end.
So, what scripts did we create, where can you get them, and what are they? First, download the zip file below:
Unzip the file to “C:\ RubrikSQLAutomationv1” to save yourself having to edit more variables. Within the folder you will find:
- StoreRubrikCredentials
Run this to securely store your Rubrik login credentials. Each script is configured by default to use it to authenticate and each needs to be edited with the Rubrik cluster DNS name or IP, plus any additional settings/variables available. Each subsequent script can be integrated into your existing workflows with the variables passed to the script, or hardcoded as per the examples. - ListDBs
Export a CSV list of all protected and unprotected DBs seen on SQL hosts and instances registered to the Rubrik cluster. Useful for identifying which hosts might need an SLA domain policy applying to them to protect the DBs within, but more importantly gives you a simple list of DBs by host, instance, and DB name, to reference for the subsequent scripts. - HostRefresh
By default, Rubrik refreshes the DBs available for protection on a SQL instance every 15 minutes. If you need to immediately refresh the DBs on a host/instance, this script will do just that. I.E you just created a DB and now you want an on-demand snapshot (see below) or you immediately want Rubrik to apply an SLA domain. - OnDemandSnapshot
Perform an on-demand backup of a SQL DB with task status verification ensuring the script only completes when the backup task is successful, fails, or times out. Useful for taking a specific point in time backup of a DB to the same or even a different SLA domain for subsequent operations. This can be for a live mount, export, or even a yearly backup archived to a separate SLA domain to archive to Glacier. - BulkOnDemandSnapshot
The same as OnDemandSnapshot, except the input is taken from a CSV and the script doesn’t wait for a backup (snapshot) operation to complete before progressing to the next. Best used when you need to take a specific point in time backup across many databases. - LiveMount
Perform a live mount of a SQL DB to any host/instance specified with task status verification. Includes an unmount script to undo the operation for the exact DB specified. This can be used to automate SQL live mounts as part of another process or to verify successful mount before performing an operation on the DB. I.E, set to read-only or run a query. - Export
Unlike live mounts which mount the DB over the network, exports copy/restore the DB to the storage of the host/instance specified. This takes longer, but this script automates this process with task status verification, while also supporting multiple .mdf, .ndf, and .ldf files being restored to the default or a new folder on the host. - BulkExport
The same as export, but the input is taken from a CSV and the script doesn’t wait for each export to complete before progressing to the next. Includes an example for the latest point in time (inclusive of log backups), or from the last on-demand snapshot, if you want to integrate it with the on-demand example script. Best used for exporting a group of SQL DBs simultaneously. - BusinessSLAReport
This script gives you a grid view and optional CSV list of every database protected by Rubrik along with the time since the last full backup, last log backup, and whether it’s meeting your business SLA (default 24 hours). - + Many more added since!
With the above, you have everything you need to hit the ground running with automating SQL with Rubrik and PowerShell on a massive scale. If you want to call them Josh scripts that’s ok, but Virtually Sober scripts would be funnier!
Let me know in the comments if you found them useful or to request any future additions. Happy scripting,
Joshua