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.

Advertisements

13 comments

  • Awesome scripts, Donabel! I needed these to run .NET projects on Amazon EC2. Many thanks,

    Nick

    Like

  • NIce Scripts. But how to disconnect all users before the restore process ? I am getting restore failure because of existing connections to the database.

    Like

  • Jose A. Hernandez

    This is simply not working for me. I’m trying to do a single restore of a database backup file. It is being restored to a different SQL server than the one the original backup file came from. I get the following in the console when the scripts tries to run the command: $smoRestore.SqlRestore($server)


    Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'dwrtsqltst01'. "
    At D:AdminSQLRestore.ps1:104 char:23
    + $smoRestore.SqlRestore <<<< ($server)

    Any ideas. I’m executing this code on the SQL server where the database is being restored at.

    Like

  • Absolutely great script! This has really come in handy for me. Thanks so much for sharing it. Can I make a couple of suggestions for Restore to New Database?
    1) When determining the Data and file locations you might want to use $server.DefaultFile and $server.DefaultLog. Using $server.Information.MasterDBPath and $server.Information.MasterDBLogPath points to the location of the files for ‘master’ and not necessarily the location of ‘user’ databases.
    2) Consider using $DataFiles = $restore.ReadFileList($server) to get the actual Logical file names. Attempting to restore to a constructed logical name, which may be incorrect, results in a restore error.

    All in all, still a very helpful script!

    Like

  • I had some problems with the way you got the logical name. I used instead:


    $smoRestoreDetails = $smoRestore.ReadFileList($targetServer)
    $sourceDb = $smoRestoreDetails.Rows[0]["LogicalName"]

    Like

  • Pingback: Blog Post: PowerShell: Restoring a whole heap of SQL Databases | IT.beta

  • How do you obtain restore details when the backup files have been copied from another server? I’m getting an error on the line
    $smoRestoreDetails = $smoRestore.ReadBackupHeader($server). I think because object ‘$server’ is created from the current server but that won’t know anything about a backup file from another server will it?

    Like

  • To kill connections to the database to get exclusive access before restoring add this line:

    $server.KillAllprocesses($dbname)

    Like

  • I’m looking for a script that can be pointed to a directory that contains a full backup, perhaps a differential backup as well and many transaction log backups and do a recovery to a point in time or to the end of the logs. If a differential backup is involved then the use of the logs could be limited to after the differential backup.

    Has anyone given this a go?

    Like

  • This is pure gold. Excellent. This has automated refresh of PROD to TEST to DEV. Outstanding.

    Like

  • Very helpful!
    for the logical name this works:

    # Get the file list from backup file
    $dbFileList = $smoRestore.ReadFileList($server)
    $smoRestoreFile.LogicalFileName = $dbFileList.Select(“Type = ‘D'”)[0].LogicalName

    $smoRestoreLog.LogicalFileName = $dbFileList.Select(“Type = ‘L'”)[0].LogicalName

    Thanks

    Like

  • I’m new at this, but trying to use the script. When I try to run it from powershell I get the “Restore with “1” argument(s): Restore failed for Server ‘X’.” And if I look at the error object, it says that the physical file name for both .mdf and .ldf might be incorrect. But I have checked and it should be the correct.

    Anyone have any guesses of what might be the problem?

    Like

  • Great script. Helped me a lot however I’m not seeing the restore progress every 10%. Has anyone got this working?

    Like

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