Commvault automated restores

We recently switched to Commvault for our SQL Server backup solution, and it slowed down a lot of our restores, because it took a long time to click through the GUI.  It was also error prone and dangerous, since it defaults to overriding production.  And we have to change the file paths, and change some names, and click different options.  So I built a new GUI with drop downs, to choose the source and target locations, and database names, and then you just hit Submit, and it runs.  Then I built another utility that shows the results of the restore.

The most challenging part was to use the Commvault command line, and build a Powershell process to call it, and to receive output from it.  To learn how to do this, I used the Save As Script functionality of the restore.  This will produce an XML file and a .bat file.   What you want to do is have your Powershell generate an XML file, and then start the .bat file.

Not all of the XML is needed.  I took out the unnecessary values.  I won’t post an XML sample here, but I can answer any questions in the comments section.  One tip though, is that you may need UTF8 encoding when you create the file.

$XMLOutput | Out-File “RestoreParameters.xml” -Encoding “UTF8”;

Here is the code to start the batch process.

$pinfo = New-Object System.Diagnostics.ProcessStartInfo
$pinfo.FileName = “\\fileserver\RestoreDatabaseFromXMLv3.bat”
$pinfo.UseShellExecute = $false
$pinfo.RedirectStandardOutput = $true
$process = New-Object System.Diagnostics.Process

$process.StartInfo = $pinfo
$process.Start() | Out-Null; # ignore return value with Out-Null
while (!$process.HasExited )
{        $process.Refresh();
#custom function to update database with progress
UpdateProgress $RestoreFileNumber $RestoreProgressResults.PercentComplete                    Start-Sleep -s 10
$stdout = $process.StandardOutput.ReadToEnd();
Write-Host $stdout;

Notes – these lines help redirect the output, so that you can capture it in your automation.

  1. $pinfo.UseShellExecute = $false Setting this property to false enables you to redirect input, output, and error streams.  When UseShellExecute is false, you can start only executables by using the Process object.
  2. $pinfo.RedirectStandardOutput = $true.  When a Process writes text to its standard stream, that text is typically displayed on the console. By setting RedirectStandardOutput to true to redirect the StandardOutput stream, you can manipulate or suppress the output of a process
  3. $stdout = $process.StandardOutput.ReadToEnd(); You can then update your database with the value from $stdout.

Let me know if there is any interest on this topic, and I can flesh out the details more.

Posted in Uncategorized | Leave a comment

Purge orphan MDW query plans

The solution that Microsoft proposes to remove orphan query plans from the Data Collector was running too slowly.

After looking at the query plan, I created an index to help with the delete statements.  After this, it should run quickly.

ON [snapshots].[query_stats] ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time])

Using the second index, it runs very quickly.


Posted in Uncategorized | Leave a comment

Powershell ComboBox DropDownList

In order to prevent users from typing in a ComboBox, I saw the advice to use this code:
$ComboBox.DropDownStyle = ComboBoxStyle.DropDownList;

But got this error:
ComboBoxStyle.DropDownList not recognized

In order for Powershell to recognize it, use this syntax:
$ComboBox.DropDownStyle = [System.Windows.Forms.ComboBoxStyle]::DropDownList;

Posted in Uncategorized | Leave a comment

UTF8 XML file in Powershell

I am trying to automate restores using CommVault.  Part of that is to generate XML files.  I’ll circle back here when I’m done with programming it.  Or maybe I’ll have my company sell it.  Probably no one wants it though.  Anyway, cut to the chase…

When the batch file was calling the XML file that I created, I was receiving this:
“execute: Error 0x908: Invalid XML Input. Please check the input.”

When I checked, my files were exactly the same, even special characters.

It turned out to be a difference in encoding.  CommVault was looking for utf-8.   My file, created from Powershell, was encoding in ucs-2.

To rectify this, I changed the encoding of the file with Powershell by using the encode flag.

$XMLOutput | Out-File “RestoreParameters.xml” -Encoding “UTF8”;

Posted in Uncategorized | Leave a comment

SQLPS warning message

WARNING: Failed to load the ‘SQLAS’ extension: SQL Server WMI provider is not available on SERVERNAME

I was getting this error when loading SQLPS from a job.  I never did figure out the root cause.  It may have something to do with SQL Server 2008 management objects being installed along with SQL Server 2014 components.

I just suppressed the warnings using the WarningAction flag

Import-Module -Name SQLPS -DisableNameChecking  -WarningAction SilentlyContinue

Posted in Uncategorized | Leave a comment

Restore history

For MSDB Restorehistory column restore_date, the SQL documentation claims that this means “Date and time of the completion of the restore operation.”  However, this doesn’t appear to be the case.  It shows the start time.

This is bad, because I’m trying to kick off an email based on the restore completion time.  It is looking for restores completing in the past 15 minutes.  It wasn’t finding any, because this restore_date is over an hour ago, even though the restore just completed.

Another indication that a restore took place, is that my job took more than a minute.  And my email gets kicked off in the second step.  So it will now check to see if the job has been running more than an hour, and if so, it will send the email.  Here is the new code.

DECLARE @RunTime int
SELECT @RunTime = DATEDIFF(N, start_execution_date, GETDATE())
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL
AND = 'Restore Databases'

IF @RunTime > 60

SET @text = 'The restore completed on ' + @@SERVERNAME
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile'
, @recipients = ''
,@subject = 'Restore completed'
,@body = @text

Posted in Database Administration | Tagged , , , | Leave a comment

SetParent failed for Login

I’m back for another boring Powershell blog post, that might help some people

I was getting this strange error:

New-Object : Exception calling “.ctor” with “2” argument(s): “SetParent failed for Login ‘GFreeSQL’. ”
At line:1 char:22
+ $WindowsGroupLogin = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Log …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException
+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

It turns out that I needed to specify the domain in the server name, because I was running it from a different domain.
So it should look like this:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null;
$servername = “sqlserver12345.gfree.lan
$ServerObject = new-Object Microsoft.SqlServer.Management.Smo.Server($servername )
$LoginName = “GFreeSQL”;
Microsoft.SqlServer.Management.Smo.Login -ArgumentList $ServerObject,$LoginName

Posted in Uncategorized | Leave a comment