Restore a SQL Database Using SQL Server Management Objects (SMO) and PowerShell

At some point in your career with SQL, there may come a time to perform a restore of the database (and maybe transaction logs depending on your situation). Given that we covered how to back up a SQL database, we now need to take a look at performing a restore of the database to recover data which was deleted due to some craziness with a sysadmin.

What you will see in the following code examples will be some familiar things that I used when it came to performing a backup of the SQL database. So don’t be surprised if you see something and are thinking that this looks just like what I did with a backup!

So with that, lets load up those assemblies!

#region SQL Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, 
Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Smo, 
Version=10.0.0.0, Culture=neutral,PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, 
Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, 
Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, 
Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
#endregion SQL Assemblies

Now we can connect to a SQL server and verify the connection is good by checking out the object and ensuring it actually has data in it.

#SQL Connection
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server `
-ArgumentList 'boe-pc\shire'
$sqlServer

image

I also want to make it easier to get some specific types that will be used with the restores.

#Frequently used types
$Action = 'Microsoft.SqlServer.Management.Smo.RestoreActionType' -as [type]
$DeviceType = 'Microsoft.SqlServer.Management.Smo.DeviceType' -as [type]

The next step is to determine the backup directory on the SQL server. This way I have a good idea as to where the files are at.

#Find backup directory
$backupDir = $sqlServer.BackupDirectory

image

Perfect, now we can see what exists in the backup directory and can use one of those files in the restore process.

image

Let’s find a table to ‘accidently’ wipe out. This one (Store) looks good to me.

$sqlServer.Databases['AdventureWorks'].Tables | Where {
    $_.Name -eq 'Store'
} | Format-Table Name, ID,State -AutoSize

image

Now time to kiss it goodbye!

($sqlServer.Databases['AdventureWorks'].Tables | Where {
    $_.Name -eq 'Store'
}).Drop()

image

I actually had issues dropping this due to a Foreign Key Constraints on this table. In order to accomplish this, I had to run the following TSQL statement:

ALTER TABLE Sales.Customer DROP CONSTRAINT FK_Customer_Store_StoreID

Double check that the table has been removed.

image

Now lets get the full paths to each of the backup files.

$DBBackup = Get-Item ("{0}\AdventureWorks_20131009.bak" -f $backupDir)
$TLogBackup = Get-Item ("{0}\AdventureWorks_20131009.trn" -f $backupDir)

Note: If running this against a remote SQL server, you will first need the UNC path to actually get the files, but then you must revert back to a local path as the restore process will throw an error if specifying UNC paths.

Now I will create a SMO.Restore object and reference some extra properties that will be used in the restoral of the database.

$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
    Action = $Action::Database
    Database = 'AdventureWorks'
    ReplaceDatabase = $True
    NoRecovery = $False
}

 

Notice in this case that I set NoRecovery to $False. This means that after I restore this database file, it will be ready for use and will not allow me to restore the transaction log backup.

Like before with the backup, I have to created a device object that will be used as part of the restoral process.

$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $DBBackup.fullname,'File'
$restore.Devices.Add($Device)

Now it is time for the restore. I actually had an error occur during my restore attempt stating that the database was still in use. To get around this, I made sure to kill all processes to that database.

$sqlServer.KillAllProcesses('AdventureWorks')

And now I can make the restore happen!

$restore.SQLRestore($sqlServer)

I need to refresh the list of tables and then I can verify if the Store table exists again.

$sqlServer.Databases['AdventureWorks'].Tables.Refresh()
$sqlServer.Databases['AdventureWorks'].Tables | Where {
    $_.Name -eq 'DatabaseLog'
} | Format-Table Name, ID,State -AutoSize

image

Now I will repeat this process to restore the database but instead set the NoRecovery property to $True to allow the restoral of the transaction log backup.

#Find backup directory
$backupDir = $sqlServer.BackupDirectory
# If working remotely, get UNC path to locate file; but then revert back to local path as errors may occur during restore!
$DBBackup = Get-Item ("{0}\AdventureWorks_20131009.bak" -f $backupDir)
$TLogBackup = Get-Item ("{0}\AdventureWorks_20131009_tlog.trn" -f $backupDir)

## Database Restore
#Create the Restore object
# http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx
$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
    Action = $Action::Database
    Database = 'AdventureWorks'
    ReplaceDatabase = $True
    NoRecovery = $True
}

#Create the Device that will be used for Restore
# http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backupdeviceitem.aspx
$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $DBBackup.fullname,'File'
$restore.Devices.Add($Device)
$restore.SQLRestore($sqlServer)

Now the AdventureWorks database is in a ‘Restoring’ State. In order to get an accurate representation of the database, I use the following line of code to refresh the object.

$sqlServer.Databases['AdventureWorks'].Refresh()

image

image

This is where we can now proceed with the transaction log restore and making sure that the NoRecovery property is set to $False.

## T-Log Restore
$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
    Action = $Action::Log
    Database = 'AdventureWorks'
    ReplaceDatabase = $True
    NoRecovery = $False
}

#Create the Device that will be used for Restore
$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $TLogBackup.fullname,$DeviceType::File
$restore.Devices.Add($Device)
$restore.SQLRestore($sqlServer)

And the database is now back and available to use!

image

image

There you have it! I was able to do a restore of my database as well as performing a restore on both a database and its transaction log backup without much issue at all!

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell, SQL and tagged , , , , . Bookmark the permalink.

One Response to Restore a SQL Database Using SQL Server Management Objects (SMO) and PowerShell

  1. Stefan says:

    Thanks Boe
    I have looked at a lot of examples of restoring SQL databases, and your post was easy and clear to understand. And it worked!

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