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:
[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
Restore to existing database (overwriting existing database)
#============================================================
# 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 Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test_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"
Restore to a new database
#============================================================
# 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 Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test_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)
In the next posts, I will show how to do basic DDL and DML with PowerShell and SQL Server.
Related posts:
- SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell ...
- SQL Server PowerShell : How to Audit Your SQL Server Instance Properties Using PowerShell and SMO ...
- SQL Server PowerShell : Search for SQL Server Objects Using PowerShell ...
- SQL Server PowerShell : How to View your SSRS Reports (rdl) Using PowerShell and ReportViewer ...
- SQL Server PowerShell : Basics – Connecting to SQL Server ...
- SQL Server PowerShell : How to List SQL Server Services using PowerShell ...
Awesome scripts, Donabel! I needed these to run .NET projects on Amazon EC2. Many thanks,
Nick
NIce Scripts. But how to disconnect all users before the restore process ? I am getting restore failure because of existing connections to the database.
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:\Admin\SQLRestore.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.
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!
I had some problems with the way you got the logical name. I used instead:
$smoRestoreDetails = $smoRestore.ReadFileList($targetServer)
$sourceDb = $smoRestoreDetails.Rows[0]["LogicalName"]
[...] about 500 databases from their production environment to a development/staging environment. He found this script that should theoretically get the job done but he was running into a few proble…. The problems he was encountering were mostly related to different drive mappings and [...]