Restore a SQL Database

This script will restore a SQL database from a backup file using the SQL Managed Objects API.

I use this, along with the SharePoint Detach/Attach Site Collection Content Database code, to perform regular database restorations in DEV or TEST environments, from PROD backup files.

[EDITS]
2012-08-28 - Updated $tSqlRestore.DatabaseName to use a predetermined database name instead of whatever the backup had... this is necessary if the database name from the backup does not match its destination.
Language:
PowerShell
Keywords:
SQL, SMO
Code Snippet

# Variables
$tSqlServer       =  "SqlInstanceNameHere" 
$tDataBase_Name   =  "DatabaseNameHere" 
$tSqlRestore_File = "C:\[BACKUPS]\WSS_Content - 2011-09-15.bak"

# Load the SQL assemblies

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

# Prepare SQL connection
$tSqlConnStrBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$tSqlConnStrBuilder["Server"] = 
$tSqlServer
$tSqlConnStrBuilder["Initial Catalog"] = "Master"
$tSqlConnStrBuilder["Integrated Security"] = $true
$tSqlConnStrBuilder["Timeout"] = 0

$tSqlConn = New-Object System.Data.SqlClient.SqlConnection($tSqlConnStrBuilder.ConnectionString)

# Take the database offline (drops any remaining connections)
$tSqlCmd = New-Object System.Data.SqlClient.SqlCommand
$tSqlCmd.Connection = $tSqlConn
$tSqlCmd.CommandType = [System.Data.CommandType]::Text
$tSqlCmd.CommandText = [System.String]::Format("ALTER DATABASE [{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE", $tContentDB_Name)
$tSqlCmd.Connection.Open()
$tSqlCmd.ExecuteNonQuery() | Out-Null
$tSqlCmd.Connection.Close()

# Bring the database back online
$tSqlCmd = New-Object System.Data.SqlClient.SqlCommand
$tSqlCmd.Connection = $tSqlConn
$tSqlCmd.CommandType = [System.Data.CommandType]::Text
$tSqlCmd.CommandText = [System.String]::Format("ALTER DATABASE [{0}] SET ONLINE", $tDatabase_Name)
$tSqlCmd.Connection.Open()
$tSqlCmd.ExecuteNonQuery() | Out-Null
$tSqlCmd.Connection.Close()

# Restore the Database from backup

# DO NOT USE T-SQL... the connection times out, and the database is left in a 'Restoring...' state.

#$tSqlCmd = New-Object System.Data.SqlClient.SqlCommand
#$tSqlCmd.Connection = $tSqlConn
#$tSqlCmd.CommandType = [System.Data.CommandType]::Text
#$tSqlCmd.CommandText = [System.String]::Format("RESTORE DATABASE [{0}] FROM DISK = N'{1}' WITH FILE = 1, MOVE N'{0}' TO N'{2}', MOVE N'{0}_log' TO N'{3}', NOUNLOAD, STATS = 10", 
#                                               $tContentDB_Name,
#                                               "C:\[BACKUPS]\WSS_Content - 2011-09-15.bak",
#                                               "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WSS_Content.mdf",
#                                               "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WSS_Content.LDF")
#$tSqlCmd.Connection.Open()
#$tSqlCmd.ExecuteNonQuery() | Out-Null
#$tSqlCmd.Connection.Close()

$tSqlRestore_Device = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem($tSqlRestore_File, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)


$tSqlServer
 = New-Object Microsoft.SqlServer.Management.Smo.Server($tSqlServer)

$tSqlRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$tSqlRestore.NoRecovery = $false
$tSqlRestore.ReplaceDatabase = $true
$tSqlRestore.Database = $tDatabase_Name                                                 # Set the Database Name to restore
$tSqlRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database  # Restore the SQL Database
$tSqlRestore.PercentCompleteNotification =                                            # Fire the PercentComplete event every 1%
$tSqlRestore.Devices.Add($tSqlRestore_Device)                                           # Attach the backup file
$tSqlRestoreDetails = $tSqlRestore.ReadBackupHeader($tSqlServer)                        # Read the backup's contents


$tSqlRestore_percentEventHandler
 = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler]    { Write-Progress -id 1 -activity"Restoring Database" -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent)) }

$tSqlRestore.add_PercentComplete($tSqlRestore_percentEventHandler)

$tSqlRestore.add_Complete($tSqlRestore_completedEventHandler)

 

Write-Output([System.String]::Format("Database restore from [{0}] is STARTING", $tSqlRestore_File))

Write-Progress -id 1 -activity "Restoring Database" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))

$tSqlRestore.SqlRestore($tSqlServer)

Write-Progress -id 1 -activity "Restoring Database" -status "Complete" -Completed

Write-Output([System.String]::Format("Database restore from [{0}] is COMPLETE", $tSqlRestore_File))


Created 2012-05-07
comments powered by Disqus
Login