Using PowerShell to clone production SQL Server databases in non-production

We all know how to restore SQL Server databases from a backup but being the automation fanatic that I am, I wanted a method in PowerShell to quickly build a “clone” instance with all the databases as backed up from production. I wanted to do this with minimal number of parameters possible.

The criteria that I set forth for myself are
1) Be able to restore one or more databases from latest full-backup
2) Have the script detect the latest backup & location
3) Have the script detect the backup version the target databases were restored from
4) Restore in target only if backup used was not the latest (optionationy all force overwrite again)
5) If there was a failure, resume from where it left off last time
6) If permissions in target databases
were different, optionally, preserve them after restore
7) Produce a detailed report of what was done

Without further delay, let us see the script that uses SQLPSX module

<#  
.SYNOPSIS
      Given a source server and target server, restores backups of given databases from source to target 
.DESCRIPTION
      Detects the backup location of given databases on source server and restores to target                      
.INPUTS       
.OUTPUTS      
.EXAMPLE
     Restore-SQLDatabase -SourceSQLServer "MyProdInstance" -TargetSQLServer "MyDevInstance" -Database "MyDB1" -Verbose
     This example does a simple restore of a specific database from source to destination and requests verbose output 
.EXAMPLE
     (Restore-SQLDatabase -SourceSQLServer "MyProdInstance" -TargetSQLServer "MyDevInstance" -Database "*" -Verbose -force) 4>C:\_dblog\Refresh.log

    This example does a restore of all databases in source and redirects the verbose output to a file

.NOTES 

Version History 
    v1.0  - Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com] - Initial Release         

.LINK 

#> 
function Restore-SQLDatabase
{ 
    #[CmdletBinding(SupportsShouldProcess=$True)] #For WhatIf and Confirm preference support!
    [CmdletBinding()] 
    param
    (            
        [Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
		[System.String[]] $Database,

        [Parameter(Position=1, Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
		[System.String] $SourceSQLServer,

        [Parameter(Position=2, Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
		[System.String] $TargetSQLServer,

        [Parameter(Position=3, Mandatory=$false, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
		[System.String] $PermissionsScript,

        [Parameter(Position=4, Mandatory=$false, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
		[Switch] $force,

        [Parameter(Position=5, Mandatory=$false, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
		[Switch] $StopOnError = $true
    )

    # Executes once before first item in pipeline is processed    
    Begin
    {
        # Declare all variables with the right datatypes to avoid bugs due to PowerShell not casting properly
        [string[]] $databases = @()
        [string] $dbPermissionsLogFile = "c:\_dblog\permissions.log"

        #Hashtables (keyed by string)
        $dbSourceInfo = @{}
        $dbTargetInfo = @{}
        $dbSkipReasons = @{}
        $dbErrorReasons = @{}
        $dbRestoreReasons = @{}

        [string] $sqlLatestFullBackupInfo = $null
        [string] $sqlLatestRestoreInfo = $null

        [string] $operation = "[UN-INITIALZIED]"
        [string] $subOperation = "[UN-INITIALZIED]"
        [string] $error = ""
        [string] $fatalError = ""

        [Microsoft.SqlServer.Management.Smo.SqlSmoObject] $source = $null;
        [Microsoft.SqlServer.Management.Smo.SqlSmoObject] $target = $null;
        [System.Data.DataRow[]] $backupInfo = $null;
        [System.Data.DataRow[]] $restoreInfo = $null;

        [string] $db = $null;
        [bool] $canRestore = $false;
        [int] $restoredCount = 0;

        [Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase] $user = $null;
        [string] $userName = $null;
        [string[]] $usersToDrop = @();

        $sqlLatestFullBackupInfo = 
        #------------------ Begin SQL ---------------------------
@"
        SELECT  DatabaseName = x.database_name,
			    LastBackupFileName = x.physical_device_name,
			    LastBackupDatetime = x.backup_start_date        
	    FROM (  SELECT  bs.database_name,
					    bs.backup_start_date,
					    bmf.physical_device_name,
					      Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
			      FROM  msdb.dbo.backupmediafamily bmf
					      JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
					      JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
			      WHERE   bs.[type] = 'D'
					      AND bs.is_copy_only = 0 ) x
	    WHERE x.Ordinal = 1
		    AND EXISTS 
				    (
					    SELECT *
					    FROM sys.databases d
					    WHERE d.name = x.database_name
				    )	
		    AND database_name NOT IN ('master','model','msdb','tempdb')
"@
        #------------------ End SQL ---------------------------

        $sqlLatestRestoreInfo = 
        #------------------ Begin SQL ---------------------------
@"
        /* 
        #http://www.mssqltips.com/sqlservertip/1860/identify-when-a-sql-server-database-was-restored-the-source-and-backup-date/ 
        */
        SELECT  x.*    
        FROM (SELECT [rs].[destination_database_name], 
                    [rs].[restore_date], 
                    [bs].[backup_start_date], 
                    [bs].[backup_finish_date], 
                    [bs].[database_name] as [source_database_name], 
                    [bmf].[physical_device_name] as [backup_file_used_for_restore],
                    Ordinal = ROW_NUMBER() OVER( PARTITION BY [rs].[destination_database_name] ORDER BY rs.restore_date DESC )
                FROM msdb..restorehistory rs
                    INNER JOIN msdb..backupset bs
                        ON [rs].[backup_set_id] = [bs].[backup_set_id]
                    INNER JOIN msdb..backupmediafamily bmf 
                        ON [bs].[media_set_id] = [bmf].[media_set_id] ) X
	    WHERE x.Ordinal = 1
		    AND EXISTS 
				    (
					    SELECT *
					    FROM sys.databases d
					    WHERE d.name = x.destination_database_name
							AND name NOT IN ('master','model','msdb','tempdb')
				    )
"@

        #------------------ End SQL ---------------------------

        try
        {

            Write-Verbose "----------------------------------------"
            Write-Verbose "Source: $SourceSQLServer"
            Write-Verbose "Target: $TargetSQLServer"
            Write-Verbose "----------------------------------------"

            $operation = "Connect to source server $SourceSQLServer"
            #----------------------------------------
            Write-Verbose "$operation"
            $source = Get-SqlServer -sqlserver $SourceSQLServer

            $operation = "Connect to target server $TargetSQLServer"
            #----------------------------------------
            Write-Verbose "$operation"
            $target = Get-SqlServer -sqlserver $TargetSQLServer

            $operation = "Identify qualifying source databases"
            #----------------------------------------
            Write-Verbose "$operation"
            if ($Database -eq '*')
            {
                foreach($db in $source.Databases)
                {
                    if ($db -notin 'master','model','msdb','tempdb')
                    {
                        #TODO: Hack to remove surrounding square brackets - need to rework
                        $databases += $db.Replace("[","").Replace("]","");
                    }
                }
            }
            else
            {
                $databases = $Database
            }
            Write-Verbose "Found $($databases.Count) matche(s) - $databases"

            Write-Verbose "----------------------------------------"
            $operation = "$SourceSQLServer - Collect source database backup information"
            Write-Verbose "$operation"
            Write-Verbose "----------------------------------------"

            $backupInfo = Get-SqlData -sqlserver $source -dbname "master" -qry $sqlLatestFullBackupInfo -Verbose:$PSBoundParameters.Verbose
            #$backupInfo = Invoke-Sqlcmd2 -ServerInstance $SourceSQLServer -Database "master" -Query $sqlLatestFullBackupInfo -As DataRow -Verbose:$PSBoundParameters.Verbose

            foreach($row in $backupInfo)
            {
                $db = $row["DatabaseName"]

                #We only care about the databases we need to work with (and not all databases)
                if ($databases -contains $db)

                {
                    $subOperation = "Found backup information for $db"
                    Write-Verbose "$subOperation"

                    $dbSourceInfo.Add($db, 
                                        (New-Object PSObject -Property @{
								            DatabaseName = $db
								            LastBackupDateTime = $row["LastBackupDateTime"]
                                            LastBackupFileName = $row["LastBackupFileName"]
                                            BackupFileExists = (Test-Path -Path $row["LastBackupFileName"] -PathType Leaf)
                                            }
                                        ))
                    Write-Verbose $dbSourceInfo[$db]
                }
            }

            Write-Verbose "----------------------------------------"
            $operation = "$TargetSQLServer - Collect the target database restore information"
            Write-Verbose "$operation"
            Write-Verbose "----------------------------------------"

            $restoreInfo = Get-SqlData -sqlserver $target -dbname "master" -qry $sqlLatestRestoreInfo -Verbose:$PSBoundParameters.Verbose
            #$restoreInfo = Invoke-Sqlcmd2 -ServerInstance $TargetSQLServer -Database "master" -Query $sqlLatestRestoreInfo -As DataRow -Verbose:$PSBoundParameters.Verbose

            foreach($row in $restoreInfo)
            {
                $db = $row["destination_database_name"]

                #We only care about the databases we need to work with (and not all databases)
                if ($databases -contains $db)
                {
                    $subOperation = "Found restore information for $db"
                    Write-Verbose "$subOperation"

                    $dbTargetInfo.Add($db, 
                                        (New-Object PSObject -Property @{
								            DatabaseName = $db
                                            LastRestoreDateTime = $row["restore_date"]
								            SourceBackupStartDateTime = $row["backup_start_date"]
                                            SourceBackupEndDateTime = $row["backup_finish_date"]
                                            SourceDatabaseName = $row["source_database_name"]
                                            SourceBackupFileName = $row["backup_file_used_for_restore"]
                                            }
                                        ))
                    Write-Verbose $dbTargetInfo[$db]
                }
            }

            Write-Verbose "----------------------------------------"
            $operation = "Perform validations"
            Write-Verbose "$operation"
            Write-Verbose "----------------------------------------"

            #Make sure the permissions script exists
            if ($PermissionsScript)
            {
                if (-not (Test-Path $PermissionsScript -PathType Leaf))
                {
                    throw [System.IO.FileNotFoundException] "If specified, permissions script file [$PermissionsScript] must exist and it does not!"
                }
            }

            #TODO: List of things to do
            #----------------------------------------
            #Make sure the source server exists and can connect
            #Make sure the target server exists and can connect
            #Make sure the database(s) exist in the source
            #Make sure the source database has a valid backup
            #Make sure backup file(s) for the source database actually physically exist
            #Make sure prod is not the target (unless overridden)
            #Source can be the same as taget as long as a flag confirms it
            #If the target was already restored from the same source backup, need a -Force switch to re-restore
            #  (helps with re-running the restore upon failures without having to worry about excluding already succeeded restores)
        }
        catch
        {                
            #Any error that happens in the "Begin" block is considered fatal. "Process" and "End" blocks will do nothing upon encountering fatal errors
            $fatalError = $_.Exception;

            Write-Verbose "ERROR: When performing $operation & suboperation $subOperation - $($fatalError)"
            Write-Error "ERROR: When performing $operation & suboperation $subOperation - $($fatalError)"
        }
        finally
        {

        } #try

    }

    Process
    {
        if (-not $fatalError)
        {
            Write-Verbose "----------------------------------------"
            $operation = "Restore qualifying databases"
            Write-Verbose "$operation"
            Write-Verbose "----------------------------------------"

            foreach($db in $databases)
            {
                try
                {

                    $subOperation = "Restore - $db [$($databases.IndexOf($db)+1) of $($databases.Count)]"
                    Write-Verbose $subOperation
                    Write-Verbose "----------------------------------------"

                    # Make the assumption that we will not be able to restore
                    $canRestore = $false;

                    $subOperation = "Determine if it is possible to restore database - $db"
                    Write-Verbose $subOperation

                    #Check to see if we have a backup
                    if (-not $dbSourceInfo.ContainsKey($db))
                    {                
                        $dbSkipReasons.Add($db, "Source SQL Server $SourceSQLServer does not have database $db or is inaccessible!")                
                    }   
                    else
                    {
                        $subOperation = "Check if backup exists for database - $db"
                        Write-Verbose $subOperation 

                        #Does the backup file(s) physically exist?
                        if (-not $dbSourceInfo[$db].BackupFileExists)
                        {
                            $dbSkipReasons.Add($db, "Backup for source server $SourceSQLServer database $db does not exist!")
                        }
                        else
                        {
                            if ($dbTargetInfo.ContainsKey($db))
                            {
                                if ($dbSourceInfo[$db].LastBackupFileName -eq $dbTargetInfo[$db].SourceBackupFileName)                            
                                {
                                    if (-not $force)
                                    {
                                        $dbSkipReasons.Add($db, "Skipping restore since that latest restore of $db is also from backup file $($dbSourceInfo[$db].LastBackupFileName) dated $($dbSourceInfo[$db].LastBackupDateTime). Use -force switch to restore same backup forcibly!")
                                    }
                                    else
                                    {
                                        $dbRestoreReasons.Add($db, "Restore already done from same source but forcing another refresh for -force switch");
                                        $canRestore = $true
                                    }
                                }
                                else
                                {
                                    $dbRestoreReasons.Add($db, "Restore is being done because the prior restore is not from latest backup")
                                    $canRestore = $true
                                }
                            }
                            else
                            {
                                $dbRestoreReasons.Add($db, "Restore is being done because database does not exist in target")
                                $canRestore = $true
                            } #end - else if ($dbTargetInfo.ContainsKey($db))

                        } #end - else if (-not $dbSourceInfo[$db].BackupFileExists)

                    } #end - else if (-not $dbSourceInfo.ContainsKey($db))

                    # We know if we have what we need to restore
                    if (-not $canRestore)
                    {
                        Write-Verbose "WARNING: $($dbSkipReasons[$db])"
                        Write-Verbose "WARNING: SKIPPED restore for $db for above reason!"
                    }
                    else
                    {
                        #-----------------------------------------
                        #Restore: Prework
                        #-----------------------------------------
                        $subOperation = "Restore $db for reason: $($dbRestoreReasons[$db])"
                        Write-Verbose $subOperation 

                        # If DB exists in target, script out permissions before restoring, then drop the database                
                        if ($dbTargetInfo.ContainsKey($db))
                        {
                            if ($PermissionsScript)
                            {
                                $subOperation = "Script out privileges for database $db in $targetSQLServer to $dbPermissionsLogFile"
                                Write-Verbose $subOperation
                                #-----------------------------------------

                                (Get-SqlData -sqlserver $target -dbname $db -qry (Get-Content -Path $PermissionsScript | Out-String) -Verbose) 4> $dbPermissionsLogFile
                                #(Invoke-Sqlcmd2 -ServerInstance $TargetSQLServer -Database $db -Query (Get-Content -Path $PermissionsScript | Out-String) -Verbose) 4> $dbPermissionsLogFile
                            }

                            $subOperation = "Drop database $db in $targetSQLServer"
                            Write-Verbose $subOperation
                            #-----------------------------------------
                            $target.KillDatabase($db)                    
                        }

                        #-----------------------------------------
                        #Actual restore operation happens here!!
                        #-----------------------------------------
                        $subOperation = "Restore $db from backup file $($dbSourceInfo[$db].LastBackupFileName) dated $($dbSourceInfo[$db].LastBackupDateTime)"
                        Write-Verbose $subOperation

                        Invoke-SqlRestore -sqlserver $target -dbname $db -filepath $dbSourceInfo[$db].LastBackupFileName `
                                            -action Database -force -Verbose:$PSBoundParameters.Verbose

                        # If we got up to this point, we did a successful restore (although there might be errors in the post-restore) process
                        $restoredCount++

                        #-----------------------------------------
                        #Restore: Postwork
                        #-----------------------------------------
                        # If DB existed in target, restore the scripted out permissions before we dropped the database
                        if ($dbTargetInfo.ContainsKey($db))
                        {
                            $subOperation = "Drop users in database $db in $targetSQLServer"
                            Write-Verbose $subOperation
                            #-----------------------------------------
                            $usersToDrop = $null;
                            foreach($user in $target.Databases[$db].Users)
                            {
                                if (-not $user.IsSystemObject)
                                {                            
                                    $usersToDrop += $user.Name

                                    #Doing $user.Drop() within this loop will result in error "Collection was modified; enumeration operation may not execute"
                                    #try{ $user.Drop() } catch { "WARNING: Unable to drop $user"}
                                }
                            }

                            if ($usersToDrop)
                            {        
                                foreach($userName in $usersToDrop )
                                {
                                    try 
                                    { 
                                        $subOperation = "Drop user $userName"
                                        Write-Verbose $subOperation                                    
                                        Remove-SqlUser -sqlserver $target -dbname $target.Databases[$db] -name $userName
                                    }
                                    catch { Write-Verbose "WARNING: Unable to drop $userName - $($_.Exception.Message)"}
                                }
                            }

                            $subOperation = "Restore scripted out privileges for database $db in $targetSQLServer"
                            Write-Verbose $subOperation
                            #-----------------------------------------
                            if ($PermissionsScript)
                            {
                                $subOperation = "Restoring permissions for database from $dbPermissionsLogFile"
                                Write-Verbose $subOperation                                    

                                Get-SqlData -sqlserver $target -dbname $db -qry (Get-Content -Path $dbPermissionsLogFile | Out-String) -Verbose:$PSBoundParameters.Verbose
                                #Invoke-Sqlcmd2 -ServerInstance $TargetSQLServer -Database $db -Query (Get-Content -Path $dbPermissionsLogFile | Out-String) -Verbose:$PSBoundParameters.Verbose
                            }

                        } #end - if ($dbTargetInfo.ContainsKey($db))

                    } #end - else if (-not $canRestore)

                }
                catch
                {   
                    $error = $_.Exception
                    $dbErrorReasons.Add($db, $error);

                    if ($StopOnError)
                    {
                        Write-Verbose "ERROR: $($error). Requested halting processing upon error! Stopping immediately."
                        Write-Error "ERROR: When performing $operation & suboperation $subOperation - $($error)"
                        break;
                    }
                }
                finally
                {

                } #try

            } #Foreach $db

        } #if (-not $fatalError)

    } #Process

    # Executes once after last pipeline object is processed
    End
    {
        try
        {
            if (-not $fatalError)
            {
                $operation = "Put summary information together to return"
                Write-Verbose "$operation"

                Write-Verbose "Summary:"
                Write-Verbose "Total databases qualified for restore: $($databases.Count)"
                Write-Verbose "Total databases skipped: $($dbSkipReasons.Count)"
                Write-Verbose "Total databases w/error: $($dbErrorReasons.Count) (may overlap with skipped)"
                Write-Verbose "Total databases restored: $restoredCount (may overlap with errors post-restore"

                #Produce a returnable report of custom PS objects
                #------------------------------------------------        
                foreach($db in $databases)
                {
                    $subOperation = "Summarizing for database - $db"
                    Write-Verbose $subOperation

                    #These variables are only used for the purpose of this report
                    $DatabaseName = $null
                    $Status = $null
                    $ReasonRestored = $null
                    $ReasonSkipped = $null
                    $ReasonError = $null
                    $ThisBackupTime = $null
                    $ThisBackupFileName = $null
                    $ThisBackupFileExists = $null
                    $PrevRestoreDateTime = $null
                    $PrevRestoredBackupStartDateTime = $null
                    $PrevRestoredBackupEndDateTime = $null
                    $PrevRestoredSourceDatabaseName = $null
                    $PrevRestoredBackupFileName = $null

                    # Statuses ------------------------            
                    $DatabaseName = $db
                    if ($dbRestoreReasons.ContainsKey($db))
                    {
                        $ReasonRestored = $dbRestoreReasons[$db]
                    }
                    if ($dbSkipReasons.ContainsKey($db))
                    {
                        $ReasonSkipped = $dbSkipReasons[$db]
                    }
                    if ($dbErrorReasons.ContainsKey($db))
                    {
                        $ReasonError = $dbErrorReasons[$db]
                    }

                    if (($Status -eq $null) -and $ReasonError) {$Status = 'ERROR'}
                    if (($Status -eq $null) -and $ReasonSkipped) {$Status = 'SKIPPED'}
                    if (($Status -eq $null) -and $ReasonRestored) {$Status = 'SUCCESSFUL'}
                    if (($Status -eq $null)) {$Status = 'ABORTED'}

                    # Source backup info ------------------------            
                    if ($dbSourceInfo.ContainsKey($db))
                    {
                        $ThisBackupTime = $dbSourceInfo[$db].LastBackupDateTime
                        $ThisBackupFileName = $dbSourceInfo[$db].LastBackupFileName
                        $ThisBackupFileExists = $dbSourceInfo[$db].BackupFileExists
                    }

                    # Prior target restore info ------------------------            
                    if ($dbTargetInfo.ContainsKey($db))
                    {
                        $PrevRestoreDateTime = $dbTargetInfo[$db].LastRestoreDateTime
                        $PrevRestoredBackupStartDateTime = $dbTargetInfo[$db].SourceBackupStartDateTime
                        $PrevRestoredBackupEndDateTime = $dbTargetInfo[$db].SourceBackupEndDateTime
                        $PrevRestoredSourceDatabaseName = $dbTargetInfo[$db].SourceDatabaseName
                        $PrevRestoredBackupFileName = $dbTargetInfo[$db].SourceBackupFileName
                    }

                    #This is what gets returned to the caller
                    New-Object PSObject -Property @{
                                        DatabaseName = $DatabaseName
                                        Status = $Status
                                        ReasonRestored = $ReasonRestored
                                        ReasonSkipped = $ReasonSkipped
                                        ReasonError = $ReasonError
                                        ThisBackupTime = $ThisBackupTime
                                        ThisBackupFileName = $ThisBackupFileName
                                        ThisBackupFileExists = $ThisBackupFileExists
                                        PrevRestoreDateTime = $PrevRestoreDateTime
                                        PrevRestoredBackupStartDateTime = $PrevRestoredBackupStartDateTime
                                        PrevRestoredBackupEndDateTime = $PrevRestoredBackupEndDateTime
                                        PrevRestoredSourceDatabaseName = $PrevRestoredSourceDatabaseName
                                        PrevRestoredBackupFileName = $PrevRestoredBackupFileName
                                    }
                } #foreach($db in $databases)

            } #if (-not $fatalError)

        } #try       
        catch
        {                
            $error = $_.Exception

            Write-Verbose "ERROR: $($error). Requested halting processing upon error! Stopping immediately."
            Write-Error "ERROR: When performing $operation & suboperation $subOperation - $($error)"
        }
        finally
        {
            Write-Verbose "----------------------------------------"
            Write-Verbose "Restore operation completed. Please check statuses."
            Write-Verbose "----------------------------------------"        

        } #try

    } #End block
}

The permissions script that I used to script out database permissions is at SQLCentral.com

http://www.sqlservercentral.com/scripts/Security/71562/

The usage examples are shown at the top of the script. Once you run in the cmdlet, you can also get help to see the sytax and usage

PS C:\Users\MyId> Get-Help Restore-SQLDatabase

NAME
    Restore-SQLDatabase

SYNOPSIS
    Given a source server and target server, restores backups of given databases from source to target

SYNTAX
    Restore-SQLDatabase [-Database] <String[]> [-SourceSQLServer]  [-TargetSQLServer]  [[-PermissionsScript] ] [[-force]] 
    [[-StopOnError]] []

DESCRIPTION
    Detects the backup location of given databases on source server and restores to target

RELATED LINKS

REMARKS
    To see the examples, type: "get-help Restore-SQLDatabase -examples".
    For more information, type: "get-help Restore-SQLDatabase -detailed".
    For technical information, type: "get-help Restore-SQLDatabase -full".

PS C:\Users\MyId>

Have fun cloning your SQL Server databases!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s