I have been blogged non-technical topics for too long and it is time for me to return to my favourite technical topics now.

I plan to write seven real-world cases where I feel PowerShell solution is much more efficient than pure T-SQL one, hopefully these cases can stir some interests in PowerShell for DBAs.  So here is the first one:

Case Scenario:

There is a production SQL Server instance (SQL 2005+) hosting about 150 SharePoint databases, from time to time, I need to refresh the QA environment using the full backup files of the production databases.

Assuming QA SQL instance has the same storage configuration as the production, and all the production database full backup files are located under a specific folder.

Requirement: For each QA database, add suffix _QA in each database name, for example, if the production database is FarmA_XYZ, the QA database will be FarmA_XYZ_QA

So how can we automate QA refresh process?

PowerShell Solution:

(note: I purposely omit the error handling to simplify the script)

## generate / execute restore script based on the backup files in a folder.
## the script is tested in SQL Server 2012 Developer SP1 + Windows PS V2/V3 
#import-module SQLPS ## you may need to load SQLPS explicitly if not auto loaded
$fd = get-ChildItem “C:\Backup\*.bak” |
select FullName #replace “c:\Backup\” to your own folder (can be network share)

[boolean]$debug=$true; #$true=print out the command, i.e. debug, $false=execute, i.e.not debug

[string] $destServer = “TargetServer” # the server to which you want to restore the dbs

foreach ($f in $fd)
{# foreach #1

    [string] $query = “restore headeronly from disk=`’$($f.fullname)`’”;
   
    $dt_h = invoke-sqlcmd -server $destServer  -QUERY $query;
    
    [string] $query = “restore filelistonly from disk=`’$($f.fullname)`’”

    $dt_f = invoke-sqlcmd -server $destServer -QUERY $query;
 
    [string]$destDB = $dt_h['DatabaseName'] + ‘_QA’; # change target db to your own name convention

    [string] $q = “restore database [$($destDB)] from disk=`’$($f.fullname)`’ `r`n with replace, restricted_user`r`n”
 
    foreach ($p in $dt_f)
    {  $q = $q + “, move `’$($p['LogicalName'])`’ to  `’$($p['PhysicalName'])`’” + “`r`n”;   }

    [string]$header =    “if db_id(`’$($destDB)`’) is not null `r`n” + `
    “begin `r`n” + `
    “`t alter database [$($destDB)] set offline with rollback immediate;
end`r`n “

    $q=$header + $q;

    if ($debug)
    { write-host $q;}
    else
    { invoke-sqlcmd -ServerInstance $destServer -Query $q -Connection 0 -QueryTimeOut 36000; }

}# foreach #1

Advantages Analysis:

Compared with pure T-SQL,

1st, I do not need to use xp_cmdshell to “import” the backup file name list into a table.

2nd, when I retrieve the backup meta data information, using restore filelistonly / restore headeronly, in T-SQL, the common way is to create a table first and then use the following format to “dump” out the meta data info into the newly created table, as

insert into <tbl_name> restore filelistonly from <bkup_device>

The pain-point here is not only  that you need to create that <tbl_name> table but also that the restore filelistonly /headeronly will return different number of columns for different version of sql server instances. For example, in SQL Server 2008, restore filelistonly will return an additional column: TDEThumbprint varbinary(32), while in SQL Server 2005, no such column.

But in Powershell, I do not need to worry about creating any intermediate table to store the meta data info and I do not even need to care about sql server version. This makes the code concise, beautiful and easier to maintain.

Other Usages of this case solution:

Say there are 150 full backup files, each corresponding to a specific production database, and I am asked to refresh 50 databases in QA environment. All I need to do is to create a text file that contains the 50 production database names, then use the following logic to pick the right 50 backup files out of the 120 files to do the restore.

[String[]]$a = get-Content “c:\backup\db_list.txt”
…..
if ($a -contains $dt_h['DatabaseName'])
{ ## continue to process otherwise continue,
  ## i.e. by-pass the current backup file and move on to next  }