Musings of a Data professional

Stuart Moore

Author: Stuart Moore Page 2 of 18

Nottingham based SQL Server DBA who dabbles with many other technologies. 15+ years of experience with databases and still keen to learn and explore.

Day 18 – Ola Hallengren maintnance solutions and Restore-DbaDatabase – 31 days of dbatools backup and restores

Restoring Ola Hallengren maintenance solutions backups

At dbatools we’re great fans of Ola Hallengren’s maintenance solution for dealing with backups and index maintenance. We even have a command to install and update his solution for you – Install-DbaMaintenanceSolution

If you’re using Ola’s solution then we can make some assumptions about your backups, which means we can offer a few more options.

If you execute the following:

Restore-DbaDatabase -SqlInstance Server1\instance -Path \\backups\files$\db1 -MaintenanceSolution

The we know you’re pointing us at a backup folder created by Ola’s solution. This means we know already to restore down into the folder. This would be the equivalent of

    Restore-DbaDatabase -SqlInstance Server1\instance -Path \\backups\files$\db1 -DirectoryRecurse

which isn’t too big a saving. But if you’re wanting to skip certain types of restores this can really speed things up:

    Restore-DbaDatabase -SqlInstance Server1\instance -Path \\backups\files$\db1 -MaintenanceSolution -IgnoreLogBackup

which doesn’t look much different to

    Restore-DbaDatabase -SqlInstance Server1\instance -Path \\backups\files$\db1 -DirectoryRecurse -IgnoreLogBackup

But there’s quite a performance improvement from the former over the latter. This comes about because we know that Ola’s solution lays out the folder like this:

Server
   |---Database
      |--- FULL
      |--- DIFF
      |--- LOG

Because of this with the first command we can just skip the log folder! With the second version we still have to scan all the backup headers to be sure which files are full backups, differential backups and log backups. With Ola’s solution we know already. And IgnoreDiffBackup works in the same way

You can of course configure whichever backup tool you use to put your files into the same file structure Ola uses. As long as you’re confident that you only have the right type of backups in each folder it will work.

Conclusions

Ola’s maintenance scripts are great, I use them exclusively at work and shudder when I come across an ancient maintenance place when something crawls out of the woodwork. This little switches can improve your performance if you know where you want to restore to.

All posts in this series can be found at 31 Days of dbatools Backup and Restores

Day 17 – dbatools Restore-DbaDatabase and Azure – 31 days of dbatools backup and restores

Restoring with Azure with dbatools

All our talk about restores so far has been talking about restoring to on premises SQL Server instances, from on premises filestores. How well does dbatools Restore-DbaDatabae work with Azure?

Very well in fact. In today’s post we’ll look at the options for restoring to and from Azure databases.

Day 16 – Saving and recreating BackupHistory – 31 days of dbatools backup and restores

Recreating Backup History

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

Day 15 – dbatools and Backup History – 31 days of dbatools backup and restores

Backup History

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:

Example of data held in dbatools BackupHistory object

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

Day 14 – Restoring a Sql Server database without recovery – 31 days of dbatools backup and restores

Restoring a SQL Server database without recovery

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:

  1. Restore the data to the disk
  2. 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.

Day 13 – Restore-DbaDatabase and Point in Time Restores – 31 days of dbatools backup and restores

Restore-DbaDatabase and Point in Time restores

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

Day 12 – Moving and renaming files with Restore-DbaDatabase – 31 days of dbatools backup and restores

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.

Let’s look at some examples:

Day 11 – Renaming Databases during restore – 31 days of dbatools backup and restores

Renaming Databases during restore

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.

There are 2 ways we can rename databases:

  • For a single database restore
  • For batch database restores

Day 10 – Simple Restores – 31 days of dbatools backup and restores

Simple Restores

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:

  1. Connect to a SQL Server Instance
  2. Scan 1 or more backup file
  3. Work out whan can be restored from these files
  4. Begin restoring the database(s) to the SQL Server instance
  5. Continue restores until it runs out of backups to restore
  6. 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.

Day 9 – Limitations of Restore-DbaDatabase – 31 days of backup and restores

Limitations of Restore-DbaDatabase

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.

Page 2 of 18

Powered by WordPress & Theme by Anders Norén