Press "Enter" to skip to content

Category: Backups

Generating Database Restore Scripts

Published 2017-06-30 by Kevin Feasel

David Fowler announces sp_RestoreScript:

I’m sure we’ve all been there, we need to restore a database to 11:34am, four days ago. What’s the first step?  To go off hunting down the relevant full and differential backups and then figure out what transaction logs we need to play in and in what order.  Well here at SQL Undercover we’ve got the solution to all your restoration woes, the latest addition to the Undercover Toolbox, sp_RestoreScript.

sp_RestoreScript will do all the hard work for you, it’ll hunt down all the backup files that you need to restore your database to a specific time and present you with a nice, simple script to run based on the parameters that you give it.

Click through for the script, which includes great ASCII art.

Comments closed

Checking Backup Encryption Size Differences

Published 2017-06-15 by Kevin Feasel

Tracy Boggiano has a script to check whether your backup file sizes are larger or smaller when they’re encrypted:

I had a recent project to enable backup encryption on all our servers.  Then question from the storage team came up will this required additional space.  Well by then I had already enabled in all our test servers so I wrote a query that would compare the average size of backups before encryption to after encryption.  Keep in mind we do keep only two weeks of history in our backup tables so this is a fair comparison.  If you don’t have maintenance tasks to clean up your backup history then you should have backup_start_time to the where clauses to get more accurate numbers and setup a maintenance tasks to keep your msdb backup history in check.

Unfortunately, Tracy leaves us in suspense regarding whether they did increase.

Comments closed

Restoring A BACPAC File

Published 2017-06-02 by Kevin Feasel

Steve Jones shows how to restore a database saved in .bacpac format:

I needed to get the WideWorldImporters sample database for a project and noticed that there was a BACPAC available. I downloaded it and needed to restore this as a database. At least, that’s what many people would think.

However, if you go to the restore dialog, and select Device and then pick your location, there’s no filter for a .bacpac. In fact, if you choose one, it won’t restore. You’ll get the “no backupset selected” error.

Read on for a step-by-step guide showing how to do this.

Comments closed

Smarter Differential Backups

Published 2017-06-02 by Kevin Feasel

Dennes Torres shows us how we can use a new column in an old DMV to make our full vs differential backup processes smarter:

What are the possibilities with this new field ? We are now able to check how many extents have changed since last full backup and decide if a full backup is really needed or we can live with a differential backup, achieving smarter backup plans.

Change our full backup jobs to first check this field and decide if the backup will be full or differential can save space and maintenance time with databases that aren’t updated so often.

Read on to learn more about this new column, which will be available in SQL Server 2017.

Comments closed

Smart Transaction Log Backup Stats

Published 2017-05-30 by Kevin Feasel

Parikshit Savjani explains how you can use a new DMV to create smart transaction log backups:

In sys.dm_db_log_stats, you will find a new column log_since_last_log_backup_mb which can be used in your backup script to trigger a transaction log backup when log generated since last backup exceeds a threshold value. With smart transaction log backup, the transaction log backup size would be consistent and predictable avoiding autogrows from transactional burst activity on the database. The resulting pattern from the transaction log backup would be similar to below.

The new sys.dm_db_log_stats DMV looks to be quite useful.

Comments closed

Availability Group Backup Preferences

Published 2017-05-30 by Kevin Feasel

Shaun Stuart points out that the Backup Preferences tab of the Availability Group Properties for an AG is a little tricky:

The default, and the way my AG was configured, was Prefer Secondary. As the image shows, this means backups will be made on the secondary, unless the secondary is unavailable, in which case, they will be made on the primary.

There are a couple of things to note when you use this setting:

  1. Full backups made on the secondary are Copy Only backups. This means they won’t reset the differential bitmap and your differentials will continue to increase in size until a full backup is made on the primary.

  2. Differential backups cannot be made on the secondary.

  3. Transaction log backups can be made on the secondary and they do clear the log, so your log file will not continue to grow.

Read on for more details.

Comments closed

Why Restores Can Be Slow

Published 2017-05-19 by Kevin Feasel

Paul Randal explains why a database restoration tends to be slower than backing that database up:

Here’s a list of things you can do to make restoring a full backup go faster:

  • Ensure that instant file initialization is enabled on the SQL Server instance performing the restore operation, to avoid spending time zero-initializing any data files that must be created. This can save hours of downtime for very large data files.

  • If possible, restore over the existing database – don’t delete the existing files. This avoids having to create and potentially zero initialize the files completely, especially the log file. Be very careful when considering this step, as the existing database will be irretrievably destroyed once the restore starts to overwrite it.

  • Consider backup compression, which can speed up both backup and restore operations, and save disk space and storage costs.

It’s a straightforward explanation, and Paul provides a few more tips for speeding up restorations.

Comments closed

Apache Solr Backup And Recovery

Published 2017-05-17 by Kevin Feasel

Hrishikesh Gadre shows how to back up indexes in Apache Solr:

The backup mechanism allows an administrator to create a physically separate copy of index files and configuration metadata for a Solr collection. Any subsequent change to a Solr collection state (e.g. removing documents, deleting index files or changing collection configuration) has no impact on the state of this backup. As part of disaster recovery, the restore operation creates a new Solr collection and initializes it to the state represented by a Solr collection backup.

It’s probably safest to treat data in Solr as secondary data, in the sense that you should be able to rebuild the entire data set from scratch instead of Solr being a primary data store.  I’m not a big fan of the author using the term “disaster recovery” instead of just “recovery” or “backup restoration” (as they’re different concepts), but it’s worth the read.

Comments closed

Azure MySQL Backups

Published 2017-05-16 by Kevin Feasel

Grant Fritchey focuses on an area where Azure’s MySQL Platform as a Service offering really makes sense:

Why Is MySQL Platform as a Service Important?

I am going to answer this question. There are a lot of advantages to creating, using and developing against data storage within a PaaS offering. One of the biggest for me is backups. Microsoft is automatically taking backups of the MySQL databases you create within Azure. These are real, full backups. Microsoft validates the backups. As I write this, you’ll have the ability to restore your entire database, to any point in time, at intervals of five minutes, over a 35 day preceding period. By programming against a MySQL database within Azure, you are gaining protection of the information you’re storing within your database, and you don’t have to do anything to benefit from this. It’s all part of the service.

Read the whole thing.

Comments closed

Diagnosing Database Restore Wait Times

Published 2017-04-24 by Kevin Feasel

Bob Ward notes that the “100 percent processed” message doesn’t mean everything is quite finished yet in a database restoration:

Notice the “100 percent…” message has detailed about “bytes processed”. Since my data is around 13Mb this tells me that the progress indicators are all about the data transferred step of RESTORE. Notice the time gap in the messages to “Waiting for Log zeroing…” and “Log Zeroing is complete”. That gap in time is around 2 minutes. Exactly the time it took between the the 100% Complete message in the SSMS Window and the final restore message.

From this evidence I can conclude that having a transaction log file size that takes a long time to initialize can be a possible cause of seeing the behavior of getting the 100% Complete Message quickly but the overall RESTORE takes longer to complete.

There’s a lot worth reading packed into this post, as you’d expect from Bob.  Read the whole thing.

Comments closed