Friday, December 23, 2011

Multiple SQL Restores with Powershell, Part 2

Since the events detailed on Part 1, there have been several changes. The application in question has been through 2 updates and now, for every 'case' opened on it, 3 SQL databases are generated. In 18 months, the SQL cluster acting as the backend for this app has gone from 240+ databases to 1300+ databases. The upgrades to the app also introduced more changes to the databases themselves. The main database for any case now has 5 files (3 data, 1 log, 1 full text catalog). The remaining 2 databases associated with a case have 2 or 3 datafiles plus a logfile.

Another change was the deployment of RedGate's SQL Backup Pro to our SQL environment. It was something I strongly proposed after the first upgrade. Unlike the Comvault SQL agent, SQL Backup Pro can be fully scripted via T-SQL and therefore, I could leverage Powershell on top of it.

The second upgrade had its challenges. A big issue was that, when cases were removed from the application frontend, the databases were not cleaned out of the SQL backend. Therefore we had quite a few databases that were never upgraded during the first upgrade. These databases were significantly different from the databases that were upgraded in terms of number of files in them. Therefore my original script would break the first time it encountered one when processing the loop.

Also, I wanted to leverage SQL Backup Pro for the new restore process, which meant I could not use SQL SMO on my Powershell script. Due to my own inexperienced in Powershell, I ended up bloating my script to more than twice the number of lines in order to accommodate the use of SQL Backup Pro and the different database versions of the app on the SQL server. Clearly I needed to devote some time to tidy up the script and make it more modular. So I started getting serious about using Powershell functions and they are absolutely phenomenal.

The script below is how I'm handling these massive SQL database restores using Powershell and leveraging SQL Backup Pro. In a nutshell, it does the following:

  1. Recursively grabs all the more recent backup files on a network share/local path of my choice.
  2. Starts a loop on each backup file which does the following:
    • Gets the database name from the backup file header using the stored procedure by Red Gate.
    • Gets the logical name and physical path of the files in the database to be restore, again using the stored procedure from Red Gate.
    • Once the logical/physical file info is gathered, a new function runs a loop on them that strips the old physical path and replaces it with the new physical path. This loop also 'constructs' the SQL restore MOVE FILE syntax that will be used in a query by the next function. With this function, I no longer care how many files are in the database to be restored nor do I care if they are data, log or full text catalog. The function will handle all of them and construct the proper part of the SQL query.
    • The next function starts the SQL database restore by calling the Red Gate's stored procedure and using the different values provided by the previous functions in the SQL Query.
    • The next function that is run after the restore will set the dbowner to the SA account. A lot of accidental DBAs don't realize this but when you restore a SQL database by overwriting the existing one or creating a new one, the dbowner is set to the account that runs the restore process. In the case of my SQL Backup Pro deployment, the agent runs under the credentials of a domain service account. If for some reason the service account for Red Gate ceases to exist, my databases would be suddenly orphaned of a dbowner which creates issues.
And that's it. By implementing Powershell functions, not only I was able to reduce the number of lines needed, but now I'm better prepared to implement future changes. I intend to post a follow up to this with the SQL native restore version of this script.

# Database Server Connection Variables
# Only need to change the $SQLServer variable to the SQL instance you are restoring the databases to.
#--------------------------------------------
[STRING]$SQLServer                = 'sql02'
[STRING]$SQLDatabase             = 'master'
[STRING]$SQLConnectString        = "Data Source=$SQLServer; Initial Catalog=$SQLDatabase; Integrated Security=True;"
[OBJECT]$SQLConnection             = New-Object System.Data.SqlClient.SqlConnection($SQLConnectString);

# Functions we are going to use.
#
# Executes the RedGate sqlbackup store procedure to get the backup header info. We'll use this to
# extrapolate the database name.
function RedgateGetDatabaseName ([STRING]$backupfile)
{
    $SQLConnection.Open()
    $SQLQuery = "exec master..sqlbackup '-sql ""RESTORE SQBHEADERONLY FROM DISK = [$backupfile] WITH SINGLERESULTSET""'"
    $SQLCommand = New-Object system.Data.SqlClient.SqlCommand
    $SQLCommand.CommandText = $SQLQuery
    $SQLCommand.Connection = $SQLConnection
    $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SQLAdapter.SelectCommand = $SQLCommand
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SQLConnection.Close()
    return $DataSet.Tables[0].Rows[12] | Select-Object 'SQL Backup v6.5.1.9'
}

# Executes the RedGate sqlbackup store procedure to get the backup filelist info. We'll use this to extrapolate all 
# the different data, log and full text search files that may exist on a database.  
function RedgateGetDatabaseFiles ([STRING]$backupfile)
{
    $SQLConnection.Open()
    $SQLQuery = "exec master..sqlbackup '-sql ""RESTORE FileListOnly FROM DISK = [$backupfile]""'"
    $SQLCommand = New-Object system.Data.SqlClient.SqlCommand
    $SQLCommand.CommandText = $SQLQuery
    $SQLCommand.Connection = $SQLConnection
    $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SQLAdapter.SelectCommand = $SQLCommand
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SQLConnection.Close()
    return $DataSet.Tables[0] | Select-Object LogicalName,PhysicalName,type
}

# This function will cycle trough all the data, log, full text search files contained in a backup. It will strip the
# old path where the file used to be and replace it with the new path where we want it restore to on the new server.
# This will be used to construct the SQL restore query.
function NewDBFilePaths ($SourceFiles) 
{

    $MoveCommand = @()
    
    foreach ($item in $SourceFiles) {
    
        If ($item.Type -eq "D") {
            $LogicalName = $item.LogicalName
            $DBFile = $item.PhysicalName
            $DBFile = $DBFile.Replace($DBFile.Substring(0,$DBFile.LastIndexOf("\")+ 1), $DBFilePath)
            $MoveCommand = $MoveCommand + " MOVE ''$LogicalName'' TO ''$DBFile'',"
        }
                                
        If ($item.Type -eq "L") {
            $LogicalName = $item.LogicalName
            $DBFile = $item.PhysicalName
            $DBFile = $DBFile.Replace($DBFile.Substring(0,$DBFile.LastIndexOf("\")+ 1), $DBLogsPath)
            $MoveCommand = $MoveCommand + " MOVE ''$LogicalName'' TO ''$DBFile'',"
        }
                                                        
        If ($item.Type -eq "F") {
            $LogicalName = $item.LogicalName
            $DBFile = $item.PhysicalName
            $DBFile = $DBFile.Replace($DBFile.Substring(0,$DBFile.LastIndexOf("\")+ 1), $DBFTSPath)
            $MoveCommand = $MoveCommand + " MOVE ''$LogicalName'' TO ''$DBFile'',"
        }
    }    
    
    Return $MoveCommand
}

# Executes the RedGate sqlbackup store procedure to start the restore of a database while at the same time moving the
# database files to their appropriate locations.
function RedgateRestore ([STRING]$Database,[STRING]$BackupFile,[STRING]$FilePath)
{
    $SQLConnection.Open()
    $SQLQuery = "EXECUTE master..sqlbackup '-SQL " + '"' + "RESTORE DATABASE [$Database] FROM DISK = ''" + $BackupFile + "''" + " WITH RECOVERY," + $FilePath + " REPLACE, ORPHAN_CHECK" + '"' + "'"
    $SQLCommand = New-Object system.Data.SqlClient.SqlCommand ($SQLQuery, $SQLConnection)
    $SQLCommand.ExecuteNonQuery() | Out-Null
    $SQLConnection.Close()
}

# When a RedGate SQLBackup restore completes, the database that's restore is owned by the service account running the 
# SQLBackup service. I use the function below to change the database owner to SA.
function ChangeDBOwner ([STRING]$Database,[STRING]$Owner)
{
    $Owner = $Owner -Replace("'","")
    $SQLConnection.Open()
    $SQLQuery     = "EXEC [$Database].dbo.sp_changedbowner @loginame = '$Owner', @map = false"
    $SQLCommand = New-Object system.Data.SqlClient.SqlCommand ($SQLQuery, $SQLConnection)
    $SQLCommand.ExecuteNonQuery() | Out-Null
    $SQLConnection.Close()
}

# Variables
#
# Directory on target SQL instance were you want the MDF files to land at.
$DBFilePath = "H:\Databases\"
# Directory on target SQL instance were you want the MDF files to land at.
$DBLogsPath = "I:\Tranlogs\"
# Directory on target SQL instance were you want the Full Text files to land at.
$DBFTSPath = "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\FTData\"
# This one is useful if you are going to filter out files by date
$filedate = Get-Date -Format M-d-yyyy_hh-mm-ss
# Directory where backup files are located.
$Directory = '\\dwcifs01\sqlbackups01\trace'

# Here I'm setting a variable which will do a recursive search of the files I want to restore, 
# in this particular case I'm grabbing the most recent full backup file and filtering out system databases
# and some specific databases. Of course, you would adjust this to your particular needs.

$files = Get-ChildItem $Directory -filter "*full*.sqb" -rec | Where-Object `
    {($_.Directory -notlike $Directory+"\master\FULL") `
    -and ($_.Directory -notlike $Directory+"\msdb\FULL") `
    -and ($_.Directory -notlike $Directory+"\model\FULL") `
    -and ($_.Directory -notlike $Directory+"\portal1\FULL") `
    -and ($_.Directory -notlike $Directory+"\FTIConversionEngine\FULL") `
    -and ($_.Directory -notlike $Directory+"\rpfdb\FULL") `
    -and ($_.Directory -notlike $Directory+"\rs_tempdb\FULL") }

$files = $files | group directory | foreach {@($_.group | sort {[datetime]$_.lastwritetime} -desc)[0]} | sort {$_.name}

# Start looping through each backup file and restoring the databases
foreach ($filename in $files) {

    #Set the filename variable to the fullpath/name of the backup file
    $filename = $filename.FullName
    
    # Get Database Name
    $dbName = RedgateGetDatabaseName -backupfile $filename
    $dbName = $dbName[1..$dbName.Length]
    $dbName = $dbName[0].'SQL Backup v6.5.1.9'.Replace("Database name       : ", "")
    
    # Get Database Files
    $dbFiles = RedgateGetDatabaseFiles -backupfile $filename
    $dbFiles = $dbFiles[1..$dbFiles.Length]
    
    # Update Database Files with new destination path
    $Filepaths = NewDBFilePaths -SourceFiles $dbFiles
    
    # Execute SQLBAckup restore
    RedgateRestore -Database $dbName -BackupFile $filename -FilePath $Filepaths
    
    # Change restored database dbowner to SA
    ChangeDBOwner -Database $dbName -Owner sa
}

No comments: