At some point in your career with SQL, there may come a time to perform a restore of the database (and maybe transaction logs depending on your situation). Given that we covered how to back up a SQL database, we now need to take a look at performing a restore of the database to recover data which was deleted due to some craziness with a sysadmin.
What you will see in the following code examples will be some familiar things that I used when it came to performing a backup of the SQL database. So don’t be surprised if you see something and are thinking that this looks just like what I did with a backup!
So with that, lets load up those assemblies!
#region SQL Assemblies add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral,PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop #endregion SQL Assemblies
Now we can connect to a SQL server and verify the connection is good by checking out the object and ensuring it actually has data in it.
#SQL Connection $sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server ` -ArgumentList 'boe-pc\shire' $sqlServer
I also want to make it easier to get some specific types that will be used with the restores.
#Frequently used types $Action = 'Microsoft.SqlServer.Management.Smo.RestoreActionType' -as [type] $DeviceType = 'Microsoft.SqlServer.Management.Smo.DeviceType' -as [type]
The next step is to determine the backup directory on the SQL server. This way I have a good idea as to where the files are at.
#Find backup directory $backupDir = $sqlServer.BackupDirectory
Perfect, now we can see what exists in the backup directory and can use one of those files in the restore process.
Let’s find a table to ‘accidently’ wipe out. This one (Store) looks good to me.
$sqlServer.Databases['AdventureWorks'].Tables | Where {
$_.Name -eq 'Store'
} | Format-Table Name, ID,State -AutoSize
Now time to kiss it goodbye!
($sqlServer.Databases['AdventureWorks'].Tables | Where {
$_.Name -eq 'Store'
}).Drop()
I actually had issues dropping this due to a Foreign Key Constraints on this table. In order to accomplish this, I had to run the following TSQL statement:
ALTER TABLE Sales.Customer DROP CONSTRAINT FK_Customer_Store_StoreID
Double check that the table has been removed.
Now lets get the full paths to each of the backup files.
$DBBackup = Get-Item ("{0}\AdventureWorks_20131009.bak" -f $backupDir)
$TLogBackup = Get-Item ("{0}\AdventureWorks_20131009.trn" -f $backupDir)
Note: If running this against a remote SQL server, you will first need the UNC path to actually get the files, but then you must revert back to a local path as the restore process will throw an error if specifying UNC paths.
Now I will create a SMO.Restore object and reference some extra properties that will be used in the restoral of the database.
$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
Action = $Action::Database
Database = 'AdventureWorks'
ReplaceDatabase = $True
NoRecovery = $False
}
Notice in this case that I set NoRecovery to $False. This means that after I restore this database file, it will be ready for use and will not allow me to restore the transaction log backup.
Like before with the backup, I have to created a device object that will be used as part of the restoral process.
$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $DBBackup.fullname,'File' $restore.Devices.Add($Device)
Now it is time for the restore. I actually had an error occur during my restore attempt stating that the database was still in use. To get around this, I made sure to kill all processes to that database.
$sqlServer.KillAllProcesses('AdventureWorks')
And now I can make the restore happen!
$restore.SQLRestore($sqlServer)
I need to refresh the list of tables and then I can verify if the Store table exists again.
$sqlServer.Databases['AdventureWorks'].Tables.Refresh()
$sqlServer.Databases['AdventureWorks'].Tables | Where {
$_.Name -eq 'DatabaseLog'
} | Format-Table Name, ID,State -AutoSize
Now I will repeat this process to restore the database but instead set the NoRecovery property to $True to allow the restoral of the transaction log backup.
#Find backup directory
$backupDir = $sqlServer.BackupDirectory
# If working remotely, get UNC path to locate file; but then revert back to local path as errors may occur during restore!
$DBBackup = Get-Item ("{0}\AdventureWorks_20131009.bak" -f $backupDir)
$TLogBackup = Get-Item ("{0}\AdventureWorks_20131009_tlog.trn" -f $backupDir)
## Database Restore
#Create the Restore object
# http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx
$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
Action = $Action::Database
Database = 'AdventureWorks'
ReplaceDatabase = $True
NoRecovery = $True
}
#Create the Device that will be used for Restore
# http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backupdeviceitem.aspx
$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $DBBackup.fullname,'File'
$restore.Devices.Add($Device)
$restore.SQLRestore($sqlServer)
Now the AdventureWorks database is in a ‘Restoring’ State. In order to get an accurate representation of the database, I use the following line of code to refresh the object.
$sqlServer.Databases['AdventureWorks'].Refresh()
This is where we can now proceed with the transaction log restore and making sure that the NoRecovery property is set to $False.
## T-Log Restore
$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
Action = $Action::Log
Database = 'AdventureWorks'
ReplaceDatabase = $True
NoRecovery = $False
}
#Create the Device that will be used for Restore
$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $TLogBackup.fullname,$DeviceType::File
$restore.Devices.Add($Device)
$restore.SQLRestore($sqlServer)
And the database is now back and available to use!
There you have it! I was able to do a restore of my database as well as performing a restore on both a database and its transaction log backup without much issue at all!
Thanks Boe
I have looked at a lot of examples of restoring SQL databases, and your post was easy and clear to understand. And it worked!