New Backup Behavior in SQL 2014

Comments: 1 Comment
Published on: April 1, 2014

As has been well publicized, today is the official Release To Manufacturing date for SQL Server 2014.  You can read more about all of that here.

Something that hasn’t received much word is a new feature that is a game changer.  I’m not referring to the advancements with the In-Memory OLTP (aka Hekaton).  The real game changer in my opinion is the way backups will be treated in 2014.

encryptionSQL Server 2014 brings the added functionality of encryption to the database backups.  This is a significant improvement to securing data at rest.  This functionality applies to databases that have been TDE enabled as well as those that are not TDE enabled.  This functionality also applies to backups that are compressed and backups that are not compressed.

The beauty of this functionality is that all backups will be encrypted now by default.  What this means is that you need not configure anything on your end to make it happen.  Despite it being enabled by default, you can change the encryption method should you choose.

Another interesting note with this new default behavior is that all of your database backups will fail out of the box.  You might ask why.  Well, there are some pre-requisites that must be met in order for the encrypted backup to succeed.

Here are those pre-reqs.

  1. Create a Database Master Key for the master database.
  2. Create a certificate or asymmetric Key to use for backup encryption.

If you have not created your DMK, your backups will fail and you will be none the wiser until you try to restore that backup.  That is really the way you want to conduct your duties as a DBA, right?  You guessed it, the backup shows that it succeeds yet it does nothing.

As you move forward with your SQL 2014 implementation, ensure you create those DMKs and ensure your backups are safe.

Oh and in case you haven’t noticed, pay attention to today’s date.


Is your LOG backed up?

You have been doing a fantastic job of late.  You have all of your databases being backed up on a regular schedule.  Now you get an alert that your transaction log just keeps growing.  Why is that?

You decide to do a little investigation and find that you have your recovery model set to full and you are performing full backups.  That should cover it right?  No, that is not right!

What is this?  Now you have to do something more?  Yes, that is correct.  Performing full backups is not always enough to recover your critical data.  You need to know the recovery requirements for the database / server in question.  But since you have the recovery model set to full, let’s just talk about what else you should be doing.

The first step should be to run a quick script to determine what databases you have in full recovery and which of those databases do not have a LOG backup.  You see, when a database is in full recovery, you should also backup your transaction log on a regular schedule too.  That schedule is to be determined as a part of the second step (and I will only talk about the first two and only briefly about the second step).

In that first step, you can query your msdb database to help generate a report of which databases have had a transaction log backup.  That should be easy enough to do.  Despite the ease, it should not lessen the importance by any degree.  Here is the script that I wrote recently to help determine which databases were in need of a log backup.

[codesyntax lang="tsql"]


You will likely notice that I am querying both sys.databases out of the master database as well as dbo.backupset out of msdb.  Look more closely and you will see the employ of a Left Outer Join with two conditions on the Join.  In this case, both conditions are required to produce the Outer Join effect that I was seeking.  Had I used a script like the following:

[codesyntax lang="tsql"]


You would see a considerably different result set.  The reason for this different result set is tied to the predicate used and the Join conditions.  And when one examines the execution plan, the difference becomes a little more evident.

That WHERE predicate converted our Left Outer Join to an Inner Join.  Now, if I had only wanted to return results for databases that had log backups, that might be fine.  I want to report on all databases and find not only the last log backup for a database, but I also want to find if a log backup is missing.  Therefore, I need to ensure that both conditions are declared as part of my Left Outer Join.

Running a query such as this will now provide us with some knowledge as to the database recovery models as well as which databases have had a log backup, have not had a log backup, and which do not need a log backup (based on recovery model).

Armed with this information, it is imperative to do a bit more digging now.  Knowing what the business deems as an acceptable data loss is important.  Find that out and then create transaction log backups accordingly so the business can recover appropriately in the event of a disaster.

Backup History

Categories: Corner, News, Professional, Scripts, SSC
Comments: 5 Comments
Published on: November 30, 2012

This is a quick rewrite of the script I just posted a few days ago.  The script had a few flaws if trying to run it on a SQL 2005 box.  I have updated it to run against SQL 2005 and up.

This version is done using Dynamic SQL to help achieve some of the functionality that I wanted but while allowing the flexibility of the different SQL Versions.

You can see the old script here, shown as a part of investigating why differential backups were failing to restore.

[codesyntax lang="tsql"]


Running the above code, I get the following for one of my SQL instances.

Differential Restore Fail

Categories: Corner, News, Professional, SSC, SSSOLV
Comments: 8 Comments
Published on: November 28, 2012

We have all heard that backup is not complete until you test it by restoring it.  We also know that part of a DR plan involves the testing of those backups through restores.

I recently had the opportunity to run through a test scenario and ran into a substantial failure.  It was nearly catastrophic due to the size of the database, size of the backups and location of the DR site where we were testing the recovery.  Let’s just fast forward to all of the fun rather than bore you with all of the minutia.

I began the test with the restore of the full database backup.  This restore took in excess of 16 hours.  Once complete, the next phase was to restore the latest differential and then log backups from there.

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This is where the fun began.  I sat there pondering this for a bit.  How could this be the case?  I verified that the Full backup was only run once via the regular schedule.  I even ran the following to script to check.

[codesyntax lang="tsql"]


This code returned only the one full backup that I knew and expected should be there from within the past few days.  So I tried that differential restore again.  I also tried a new differential just in case.  All of that was futile.  Then I looked back to my script and removed the following line

[codesyntax lang="tsql"]


Tada – that revealed the problem.  Suddenly I had a new full backup appear for each day since the full backup that was taken through the regularly scheduled means.  Each new backup had the following nomenclature for the backup path.

[codesyntax lang="tsql"]


And then it became apparent what was happening.  I had been excluding backups with paths following that type of naming convention due to CommVault in the past.  In this case I knew that CommVault was not a part of the picture.  I did know however that SAN snapshots were being taken on a nightly basis.

This was the aha moment – SAN snapshotting was causing a full backup to be registered in SQL Server.  And it makes perfect sense when looking at the backup history and sizes for each of the differentials that had been taken.  The differential backups were almost always the same size.  If you are running SAN Snapshots, you may want to reconsider your differential backup plan.

This script (just the revised version of the above) will help list the Full and Differential backups that have been taken.

[codesyntax lang="tsql"]


page 1 of 1

July 2014
« May    


Welcome , today is Wednesday, July 23, 2014