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.
No related posts.
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 [...]
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?
To kill connections to the database to get exclusive access before restoring add this line:
$server.KillAllprocesses($dbname)
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?
This is pure gold. Excellent. This has automated refresh of PROD to TEST to DEV. Outstanding.
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