Yesterday we looked at using BackupHistory from Get-DbaDbBackupHistory to speed up database restores by pulling the information from SQL Server.
However, this isn’t always available. Perhaps when you’ve a hard security split between the production box and test environments, or when you’ve completely lost the original SQL Server instance in a distaster of some sorts
Today we’ll look at how you can save your backup history to disk, or recreate it from backup files ahead of time
Today we’re going to look at what underpins a lot of the work of Restore-DbaDatabase, backup history.
At the basic level it is just what it sounds like, a history of all the backups taken of a SQL Server database. Getting that history and and turning it into a restore plan is a more complex business.
All the examples in the last 14 days have gotten the required backup history from the backup files passed in to them, either via Get-ChildItem or the Path parameter. When this happens a sub function of Restore-DbaDatabase scans every file passed, if it’s a SQL Server backup file it’s headers are read and the information passed back, if it’s not it’s ignored
The results are returned as an array of BackupHistory objects. BackupHistory is dbatools own internal object for backup history. On Day 2 of this series (Backup-DbaDatabase Basics) we saw what’s contained in a BackupHistory object, but it’s worth repeating here:
There’s a lot of information stored in there. A lot of it is mainly of use for the dbatools functions, but other pieces of information are handy to have access to for planning and monitoring. We created the BackupHistory object to make moving this information between dbatools functions using the pipeline. You’ll see it cropping up a lot betweent Backup-DbaDatabase, Get-DbaDbBackupHistory and Restore-DbaDatabase, and it’s used deep inside things like Copy-DbaDatabase and Start-DbaMigration.
Scanning a large number of SQL Server backup files can take a long time. If you’re taking a full database backup on a Sunday night, a differential every 12 hours and a log backup every 20 minutes, then to restore the database to Thursday at 15:35 you’re going to need to scan at least 280 files. This can take a while even on a fast machine.
Today we’re going to look at getting that information from your SQL Server Instance. Tomorrow we’ll be looking at ways of improving the speed, when all you’ve got is the files
If you’ve not done much work with SQL Server restores, a little bit of background may help with todays post
The are 2 phases to what we commonly call a SQL Server databae restore:
Restore the data to the disk
Recover the database
The first phase is the one we normally think of, writing the SQL Server data back onto the disks.
During the recovery phase SQL Server ensures that everything in the database is consistent and that all transactions that were open at the point the database is restored to are rolled back.
All of the previous Restore-DbaDatabase examples in this series haveve been restoring the database, and then recovering it as well so it’s been hidden inside the function.
However there are a couple of scenarios when you don’t want the recovery phase to fire.
The first is when you are going to want to restore more backups to bring the database further forward in time. This might be in a disaster scenario where you’ve lost some of your backup storage. You could restore what you have while your Storage team are pulling the missing backups from tape.
The second is when you’re not sure when you want to stop the restore. So you restore to a specific point in time, put the database into standby mode, and then if needed you apply more backups.
So far in this series, we’ve just been restoring to the latest point in time that is in the backups. But often you want to restore to a particular point in time. Maybe just before someone ran that TRUNCATE they shouldn’t have or because you need to look into an error in more detail
So here’s a little secret, if you dig into the Restore-DbaDatabase source code you’ll we always do a Point in Time restore, Just that the default point in time is 1 year ahead. This helps to keep the logic cleaner throughout the Restore-DbaDatabase pipeline
But we can do a bit more than that. Restore-DbaDatabase offers 2 types of Point in Time restores:
Restore to a specific Point in Time
Restore to a specific marked Transaction
These 2 options cover almost any reason why you’d want to restore to somewhere other than the end of the backup chain. Examples are below the fold
Moving and Renaming files with Restore-DbaDatabase
Yesterday we looked at renaming databases while restoring them. The observant may have wondered how we can avoid filename conflicts as well. We’ll be covering that today
There are quiet a number of reasons why you’d want to change filenames or paths for data file while you’re restoring them:
Avoid a filename conflict
Original FilePaths don’t exist on target server
Different disk layouts
Just because you’ve never liked the naming schema
We’ll be going through a lot of examples today so this will be quite a long post.
One caveat. Due to SQL Server’s restore not supporting it we can only change the Physical name of the data file during the restore. To change the logical name you need to use the Rename-DbaDatabse function after the restore has completed.
Yesterday we looked at the simplest of options for restoring SQL Server backups with Restore-DbaDatabase. We saw that it’s simple and easy to restore a single database or as many as you want to.
But what happens if you want to restore a database with a different name? Maybe you want to restore Production as Development so it’s obvious which database is which (hands up all those who’ve not been sure which database named Production is really development (no lying, it’ll be most of you.))
As it’s such a common request of course we built it in and made it as simple as possible.
Finally we’re going to start using Restore-DbaDatabase after talking about it for a couple of days.
Given all the options that Restore-DbaDatabase offers, we’ll start off simple and build up the complexity of the commands over the next couple of days. We’ll also cover what’s going under the hood of the command, if you’ve got a basic understanding of that a lot of other options make more sense
At the basic level what does a Restore-DbaDatabase run do during a simple restore:
Connect to a SQL Server Instance
Scan 1 or more backup file
Work out whan can be restored from these files
Begin restoring the database(s) to the SQL Server instance
Continue restores until it runs out of backups to restore
Open the database
There’s a fair amount going on under the hood, even for a ‘simple’ restore. Let’s work through those as we build up our first Restore-DbaDatabase command.
Like all tools, Restore-DbaDatabase isn’t able to do everything that everyone wants it to. Certainly, at the moment I’d like it to write it’s own blog posts and fetch me a cold beer, but that doesn’t happen
A lot of the below isn’t complaining about people asking for features. If we can do it, we will, and we’re keen to make this work for as many people in as many situations as possible
But quite a few requests over the years have been non starters for a number of reasons.