SQL Server PowerShell : How to Restore SQL Server Databases Using SMO and PowerShell

A follow-up to my previous post: How to Restore SQL Server Databases Using SMO and PowerShell

In this post I will show you how you can restore your database to :

1. the existing database (same database name)
2. a different database (different database name, different mdf and ldf)

As with the previous post, to do a SQL Server restore in SQL Server, you will need to use the SMO SqlBackup method. In SQL Server 2008, you will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, you will get the following error:

Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded.

Other assemblies you may want to load are:

[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
[/powershell]

Restore to existing database (overwriting existing database)

[ps collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
#============================================================
# Restore a Database using PowerShell and SQL Server SMO
# Restore to the same database, overwrite existing db
# Donabel Santos
#============================================================

#clear screen
cls

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

#get backup file
#you can also use PowerShell to query the last backup file based on the timestamp
#I’ll save that enhancement for later
$backupFile = "C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBackuptest_db_20090531153233.bak"

#we will query the db name from the backup file later

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#settings for restore
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"

#show every 10% progress
$smoRestore.PercentCompleteNotification = 10;

$smoRestore.Devices.Add($backupDevice)

#read db name from the backup file’s backup header
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

#display database name
"Database Name from Backup Header : " + $smoRestoreDetails.Rows[0]["DatabaseName"]

$smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"]

#restore
$smoRestore.SqlRestore($server)

"Done"

[/ps]

Restore to a new database

[ps collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
#============================================================
# Restore a Database using PowerShell and SQL Server SMO
# Restore to the a new database name, specifying new mdf and ldf
# Donabel Santos
#============================================================

#clear screen
cls

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$backupFile = ‘C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBackuptest_db_20090531153233.bak’

#we will query the database name from the backup header later
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#restore settings
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
$smoRestorePercentCompleteNotification = 10;
$smoRestore.Devices.Add($backupDevice)

#get database name from backup file
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

#display database name
"Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["DatabaseName"]

#give a new database name
$smoRestore.Database =$smoRestoreDetails.Rows[0]["DatabaseName"] + "_Copy"

#specify new data and log files (mdf and ldf)
$smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

#the logical file names should be the logical filename stored in the backup media
$smoRestoreFile.LogicalFileName = $smoRestoreDetails.Rows[0]["DatabaseName"]
$smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "" + $smoRestore.Database + "_Data.mdf"
$smoRestoreLog.LogicalFileName = $smoRestoreDetails.Rows[0]["DatabaseName"] + "_Log"
$smoRestoreLog.PhysicalFileName = $server.Information.MasterDBLogPath + "" + $smoRestore.Database + "_Log.ldf"
$smoRestore.RelocateFiles.Add($smoRestoreFile)
$smoRestore.RelocateFiles.Add($smoRestoreLog)

#restore database
$smoRestore.SqlRestore($server)
[/ps]

In the next posts, I will show how to do basic DDL and DML with PowerShell and SQL Server.