A Month of PowerShell – Day 22 (Restores)

2013-02-22 Wayne Sheffield No comments

Welcome to Day 22 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Yesterday we backed up all of our databases. However, what good is a backup if it can’t be restored? Now we’ll restore all those backups into a test database. This script will restore the most recent full backup for each database (into a database named “Verify”), moving all files to a separate directory to avoid trying to restore on top of the existing files. It will then restore the most recent differential backup, and then (if the database is not in the simple recovery model) all transaction log backups since the later of the full / differential backups. This script does assume that if the latest differential backup is more recent that the latest full, that it belongs to that full backup, and the script uses the paths where the databases were backed up to in yesterday’s backup script.

#clear variables
$Server     = $null

#Assign variables
$Instance   = "localhost\SQL2008"
$VerifyDB   = 'Verify'

#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"

# get the server
$Server = New-Object ("$SMO.Server") "$Instance"

# Restoring to a new database changes the $Server.Databases collection, raising an error.
# So, create an empty array to hold the current list of databases and loop through that.
$Databases = @()
# Add each database to this array
ForEach ($db in $Server.Databases)
{
    IF ($db.Status -eq 'Normal' -and $db.IsMirroringEnabled -eq $false -and `
        $db.Name -ne 'tempdb' -and $db.Name -ne $VerifyDB)
    {
        $Databases = $Databases + $db
    }
}

ForEach ($db in $Databases)
{
    # Get the location to store the data files at for this verify database
    $Verify = $Server.Settings.DefaultFile
    IF (!($Verify.EndsWith('\'))) {$Verify = $Verify + '\'}
    $Verify = $Verify + "$($VerifyDB)_DBFiles\"

    # Ensure that the directory exists
    IF (!(Test-Path -Path $Verify)) {New-Item $Verify -Type Directory}

    $dir = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Full\"
    # Get the most recent full backup
    $Full = $Null
    IF (Test-Path -Path $dir)
    {
        $Full = Get-ChildItem $dir |`
            Sort-Object LastWriteTime -Descending |`
            Select-Object -First 1 |`
            Select-Object Name, FullName, LastWriteTime
    }

    IF ($Full)
    {
        # Restore the full backup
        $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Full.FullName, 'File')
        $Restore = New-Object ("$SMO.Restore")
        $Restore.Checksum = $True
        $Restore.Devices.Add($BackupDevice)
        $Restore.Database = "$VerifyDB"
        $Restore.NoRecovery = $True
        $Restore.ReplaceDatabase = $True

        #Relocate the files so that they don't stomp on the existing db files
        ForEach ($FileGroup in $DB.FileGroups)
        {
            ForEach ($File in $FileGroup.Files)
            {
            $FileName = Split-Path $File.FileName -Leaf #Get just the filename
            $FileName = $Verify + $FileName
            $NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName)
            $Restore.RelocateFiles.Add($NewFileLoc) | Out-Null
            }
        }

        ForEach ($File in $DB.LogFiles)
        {
            $FileName = Split-Path $File.FileName -Leaf #Get just the filename
            $FileName = $Verify + $FileName
            $NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName)
            $Restore.RelocateFiles.Add($NewFileLoc) | Out-Null
        }
        $RelocateFiles = $Restore.RelocateFiles
        Write-Host "Restoring Full backup file $($Full.FullName) to $VerifyDB database"
        $Restore.SqlRestore($Server)

        $LastWriteTime = $Full.LastWriteTime

        # Get the most recent differential backup
        $Diff = $null
        $TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Diff\"
        IF (Test-Path -Path $TestPath)
        {
            $Diff = Get-ChildItem $TestPath |`
                Sort-Object LastWriteTime -Descending |`
                Select-Object -First 1 |`
                Select-Object Name, FullName, LastWriteTime

            # if the latest diff was before the latest full, don't do anything with it.
            IF ($Diff.LastWriteTime -LT $Full.LastWriteTime) {$Diff = $null}
            ELSE {$LastWriteTime = $Diff.LastWriteTime}
        }

        # If there is one,
        IF ($Diff)
        {
            $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Diff.FullName, 'File')
            $Restore = New-Object ("$SMO.Restore")
            $Restore.Checksum = $True
            $Restore.Database = "$VerifyDB"
            $Restore.Devices.Add($BackupDevice)
            $Restore.NoRecovery = $True
            ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null}
            Write-Host "Restoring Differential backup file $($Diff.FullName) to $VerifyDB database"
            $Restore.SqlRestore($Server)
        }

        IF ($DB.RecoveryModel -ne "Simple")
        {
            # Restore all transaction logs written since the last full/diff backup
            $TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Log\"
            IF (Test-Path -Path $TestPath)
            {
                ForEach ($File in Get-ChildItem $TestPath |`
                            Where {$_.LastWriteTime -ge $LastWriteTime} |`
                            Sort-Object LastWriteTime |`
                            Select-Object Name, FullName )
                {
                    $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($File.FullName, 'File')
                    $Restore = New-Object ("$SMO.Restore")
                    $Restore.Checksum = $True
                    $Restore.Database = "$VerifyDB"
                    $Restore.Devices.Add($BackupDevice)
                    $Restore.NoRecovery = $True
                    ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null}
                    Write-Host "Restoring Transaction Log backup file $($File.FullName) to $VerifyDB database"
                    $Restore.SqlRestore($Server)
                }
            }
        }
        # Bring the database online
        $Restore.NoRecovery = $False
        Write-Host "Bringing $VerifyDB database online"
        $Restore.SqlRestore($Server)
        Write-Host "Test Restore of the [$($db.name)] database passed"

        # This would be a good time to do a checkdb!
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

#clear variables

$Server = $null

#Assign variables

$Instance = "localhost\SQL2008"

$VerifyDB = 'Verify'

#Assign the SMO class to a variable

$SMO = "Microsoft.SqlServer.Management.Smo"

# get the server

$Server = New-Object ("$SMO.Server") "$Instance"

# Restoring to a new database changes the $Server.Databases collection, raising an error.

# So, create an empty array to hold the current list of databases and loop through that.

$Databases = @()

# Add each database to this array

ForEach ($db in $Server.Databases)

{

IF ($db.Status -eq 'Normal' -and $db.IsMirroringEnabled -eq $false -and `

$db.Name -ne 'tempdb' -and $db.Name -ne $VerifyDB)

{

$Databases = $Databases + $db

}

ForEach ($db in $Databases)

{

# Get the location to store the data files at for this verify database

$Verify = $Server.Settings.DefaultFile

IF (!($Verify.EndsWith('\'))) {$Verify = $Verify + '\'}

$Verify = $Verify + "$($VerifyDB)_DBFiles\"

# Ensure that the directory exists

IF (!(Test-Path -Path $Verify)) {New-Item $Verify -Type Directory}

$dir = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Full\"

# Get the most recent full backup

$Full = $Null

IF (Test-Path -Path $dir)

{

$Full = Get-ChildItem $dir |`

Sort-Object LastWriteTime -Descending |`

Select-Object -First 1 |`

Select-Object Name, FullName, LastWriteTime

}

IF ($Full)

{

# Restore the full backup

$BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Full.FullName, 'File')

$Restore = New-Object ("$SMO.Restore")

$Restore.Checksum = $True

$Restore.Devices.Add($BackupDevice)

$Restore.Database = "$VerifyDB"

$Restore.NoRecovery = $True

$Restore.ReplaceDatabase = $True

#Relocate the files so that they don't stomp on the existing db files

ForEach ($FileGroup in $DB.FileGroups)

{

ForEach ($File in $FileGroup.Files)

{

$FileName = Split-Path $File.FileName -Leaf #Get just the filename

$FileName = $Verify + $FileName

$NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName)

$Restore.RelocateFiles.Add($NewFileLoc) | Out-Null

}

ForEach ($File in $DB.LogFiles)

{

$FileName = Split-Path $File.FileName -Leaf #Get just the filename

$FileName = $Verify + $FileName

$NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName)

$Restore.RelocateFiles.Add($NewFileLoc) | Out-Null

}

$RelocateFiles = $Restore.RelocateFiles

Write-Host "Restoring Full backup file $($Full.FullName) to $VerifyDB database"

$Restore.SqlRestore($Server)

$LastWriteTime = $Full.LastWriteTime

# Get the most recent differential backup

$Diff = $null

$TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Diff\"

IF (Test-Path -Path $TestPath)

{

$Diff = Get-ChildItem $TestPath |`

Sort-Object LastWriteTime -Descending |`

Select-Object -First 1 |`

Select-Object Name, FullName, LastWriteTime

# if the latest diff was before the latest full, don't do anything with it.

IF ($Diff.LastWriteTime -LT $Full.LastWriteTime) {$Diff = $null}

ELSE {$LastWriteTime = $Diff.LastWriteTime}

}

# If there is one,

IF ($Diff)

{

$BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Diff.FullName, 'File')

$Restore = New-Object ("$SMO.Restore")

$Restore.Checksum = $True

$Restore.Database = "$VerifyDB"

$Restore.Devices.Add($BackupDevice)

$Restore.NoRecovery = $True

ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null}

Write-Host "Restoring Differential backup file $($Diff.FullName) to $VerifyDB database"

$Restore.SqlRestore($Server)

}

IF ($DB.RecoveryModel -ne "Simple")

{

# Restore all transaction logs written since the last full/diff backup

$TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Log\"

IF (Test-Path -Path $TestPath)

{

ForEach ($File in Get-ChildItem $TestPath |`

Where {$_.LastWriteTime -ge $LastWriteTime} |`

Sort-Object LastWriteTime |`

Select-Object Name, FullName )

{

$BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($File.FullName, 'File')

$Restore = New-Object ("$SMO.Restore")

$Restore.Checksum = $True

$Restore.Database = "$VerifyDB"

$Restore.Devices.Add($BackupDevice)

$Restore.NoRecovery = $True

ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null}

Write-Host "Restoring Transaction Log backup file $($File.FullName) to $VerifyDB database"

$Restore.SqlRestore($Server)

}

# Bring the database online

$Restore.NoRecovery = $False

Write-Host "Bringing $VerifyDB database online"

$Restore.SqlRestore($Server)

Write-Host "Test Restore of the [$($db.name)] database passed"

# This would be a good time to do a checkdb!

}

If you are utilizing SMO from SQL Server 2012, a new method has been added: Restore-SqlDatabase. This cmdlet has parameters to control all aspects of the restore.

PowerShell none

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Blogroll

A Month of PowerShell – Day 22 (Restores)

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Tags

Blogroll

A Month of PowerShell – Day 22 (Restores)

Share this:

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply