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 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.

Day 8 – Why is Restore-DbaDatabase like it is? – 31 days of dbatools backup and restores

Why is Restore-DbaDatabase like it is?

At first glance Restore-DbaDatabase looks like a slow lumberig complex beast. In reality it’s not that bad.

It’s the result of design decisions I took in wanting a solid versatile command that could cope with everything that people would want from it.

In this post, we’ll go through the main decisions/points of contention one by one

Day 7 – Backup up to Azure – 31 days of dbatools backup and restore

Day 7 – Backing up to Azure

Backing up to Azure? Why would you want to do that if you’re not running Azure SQL Database/Managed Instance?

There’s a couple of reasons, which you may or may not agree with:

  • Reliable storage
  • Off site storage
  • Scalable storage
  • Cheap(ish) storage

All of those of course are debatable depdending on your circumstances. But if you’re starting from scratch and don’t already have a on premises storage infrastructure then it’s quicker to start there and build up. And if you’re running SQL Server inside an Azure VM do you want to be piping data back down to the home network or paying for another VM to act as a backup target?

The main reason I like Azure storage when I can use it is number 2 – Offsite storage.

My backups will survive a major incident, say a failed Data Centre or a major fire. OK, those could be mediated with a secondary Data Centre, but that’s an extra level of expense that your organisation might not be able to justify.

And there are disasters that may affect all storage in your organisation. Ransomware being the one that springs to mind. If your SQL Server backups get encrypted by someone other than yourself, then they are worthless. You have NO way of recovering without paying the money. It has happened!

Companies who think they have good security have found that ransomware can still propogate onto their backup servers as the software is written to exploit any weakness in NTLM or SMB it can fine. However, I’ve yet head of one that can break into SQL Server and get hold of a credential which is what would need to happen for it to even know about SQL Server backups stored in Azure.

And if you’re really paranoid (you’re probably a DBA, so there’s a good chance) you can even use an Azure Function to move all new backups to another Azure Storage account when they finish, so then theres no tracable link anywhere on premises.

Setting up Azure for SQL Server Backups

Now, I’m going to cheat here a little point you at a post I’ve already made that covers setting up Azure Blob storage for SQL Server Backups

If you want to use the Azure Portal – Creating Azure Blob Storage Account for SQL Server Backup and Restore, using the Azure Portal

If you want to use PowerShell – Creating Azure Blob Storage Account for SQL Server Backup and Restore, using PowerShell

As I say in the above posts, I’d really really recommend you go with Shared Access Signatures. Better security granularity, easier to work with and they’re the future.

Once you’ve created your Azure Blob storage account and the SQL Server credential, you can use Backup-DbaDatabase to backup to Azure.

When backing up to Azure you are limited to a subset of parameters, this purely because Azure Blob storage won’t support things like MaxTransferSize. The allowed paramters are:

  • CopyOnly
  • Type
  • CompressBackup
  • Checksum
  • Verify
  • AzureCredential
  • ReplaceInName
  • CreateFolder
  • OutputScriptOnly
  • EncrytionAlgorithm
  • EncryptionCertificate

When using a Shared Access key you can also use “FileCount”

Backing up to Azure with Backup-DbaDatabase

There are 2 ways the command can be used to backup to Azure, depending on whether you’re using Access Keys or Shared Access Signatures.

With Access keys you use this format:

    Backup-DbaDatabase -SqlInstance server1\instance -Database db1 -AzureBaseUrl https://dbatoolsaz.blob.core.windows.net/azbackups/ -AzureCredential myAzureCred

Where myAzureCred is the name of the credential on server1\instance that holds the Azure Access keys. When using the older Access Keys style you cannot stripe backups, so can only provide one URL

With a Shared Access Signature you use this format:

    Backup-DbaDatabase -SqlInstance server1\instance -Database db1 -AzureBaseUrl https://dbatoolsaz.blob.core.windows.net/azbackups/

This will work as Backup-DbaDatabase will look for a credential named ‘https://dbatoolsaz.blob.core.windows.net/azbackups/’ on instance ‘server1\instance’

With Shared Access Signature credentials you can stripe files, either within the same container:

    Backup-DbaDatabase -SqlInstance server1\instance -Database db1 -AzureBaseUrl https://dbatoolsaz.blob.core.windows.net/azbackups/ -FileCount 3

or by specifying multiple AzureBaseUrl values:

    Backup-DbaDatabase -SqlInstance server1\instance -Database db1 -AzureBaseUrl https://dbatoolsaz.blob.core.windows.net/azbackups/,https://azdbatools.blob.core.windows.net/azbackups/ 

Assuming you have valid ‘https://dbatoolsaz.blob.core.windows.net/azbackups/’ and ‘https://azdbatools.blob.core.windows.net/azbackups/’ in the SQL instance performing the backup.

Conclusion

Backing up your SQL instances to Azure can be a good option if your organisation is just starting out, and by having backups off site takes some of the risks during a Disaster Recovery scenario can be minimised. Plus it’s a great way to migrate database up to Azure, in fact it’s Copy-DbaDatabase works.

For more ideas on how to use dbatools to migrate SQL Server databases to Azure have a look at the book dbatools in a month of lunches

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

Day 6 – Backup-DbaDatabase in scripts – 31 days of dbatools Backup and Restores

Day 6 – Backup-DbaDatabase in Scripts

One of the great things with dbatools is that you can pull all of the different commands together to make some really powerful scripts for yourself.

Backup-DbaDatabase is no exception. In today’s post I’m going to show you a couple of things you can do with it, and hopefully inspire you to come up with some ideas for your own usage

Page 2 of 18

Powered by WordPress & Theme by Anders Norén