We’ve had backup encryption out of the box since SQL Server 2014, yet I’ve rarely seen it used. In an age where we’re moving more and more things to the cloud including those backup files, backup encryption is becoming more and more necessary. Sure we have transport encryption and your cloud provider of choice most probably has an option for data at rest encryption but why leave any room for error? If you encrypt your backups on site before they leave you remove any margin of chance for potentially un-encrypted backups being stored somewhere.

One thing I have found is the documentation around this is a little bit disjointed and scattered over several different topics. This post is going to demo a full end to end solution of encrypting a backup on your source server and restoring it on your destination server along with some of the issues you may face on the way…

If you want to follow along you’ll need two different instances of SQL Server, I’m using SQL Server 2017 but the below should work on anything from 2014 onwards…

Source Server

On our source server, let’s create a new sample database with a couple of rows of data to test with…

CREATE DATABASE BackupEncryptionDemo
GO
CREATE TABLE BackupEncryptionDemo.dbo.Test(Id INT IDENTITY, Blah NVARCHAR(10))
INSERT INTO BackupEncryptionDemo.dbo.Test(Blah) VALUES('Testing')
INSERT INTO BackupEncryptionDemo.dbo.Test(Blah) VALUES('Testing2')

In order to encrypt a backup of this database we need either a certificate or an asymmetric key, I’m going to be using Certificates for the sake of this demo. When you create a certificate SQL Server encrypts it with a MASTER KEY before it gets stored so we’ll first need to create one of those…

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(MasterKeyEncryptionPassword123)'

This key is then used to encrypt our certificate for storage…

CREATE CERTIFICATE SuperSafeBackupCertificate 
WITH SUBJECT ='Backup Encryption Certificate For Database1 and Database2'

Armed with our SuperSafe certificate we can now backup a database with encryption…

BACKUP DATABASE BackupEncryptionDemo 
   TO DISK = 'C:\keys\DatabaseBackup.bak'
   WITH ENCRYPTION(
      ALGORITHM = AES_256, 
      SERVER CERTIFICATE = SuperSafeBackupCertificate
   )

Notice the helpful warning reminding us that we’ve not backed up our certificate. I cannot stress how important this is! If we lose that certificate then we won’t be able to restore any of our backups. The below TSQL will backup the certificate and a private key for its encryption, both of these files need to be put in a safe place where they will not be lost. The combination of these files and the password specified is all that’s needed to decrypt our backups so they need to be kept safe and in a real-world scenario should not be kept in the same place as the database backups…

BACKUP CERTIFICATE SuperSafeBackupCertificate 
   TO FILE = 'C:\keys\SuperSafeBackupCertificate.cer'
   WITH PRIVATE KEY(
      FILE='C:\keys\SuperSAfeBackupCertificate.ppk', 
      ENCRYPTION BY PASSWORD ='(PasswordToEncryptPrivateKey123)'
   )

If we then run another backup there will be no warnings…

BACKUP DATABASE BackupEncryptionDemo 
   TO DISK = 'C:\keys\DatabaseBackup2.bak'
   WITH ENCRYPTION(
      ALGORITHM = AES_256, 
      SERVER CERTIFICATE = SuperSafeBackupCertificate
   )

Now on to our first gotcha! If you run the above backup a second time you’ll get the following error…

Encrypted backups cannot append existing media sets like non-encrypted backups can, so you’ll need to write each one to a new set by specifying a different filename.

Destination Server

Now we have our encrypted backup, let’s try to restore it on our second server…

RESTORE DATABASE BackupEncryptionDemo 
   FROM DISK = N'C:\Keys\DatabaseBackup.bak' 
   WITH 
      MOVE N'BackupEncryptionDemo' TO N'D:\Data\EncryptionDemo.mdf', 
      MOVE N'BackupEncryptionDemo_log' TO N'D:\Data\EncryptionDemo_log.ldf'

We can’t restore it because it was encrypted with a certificate that we don’t yet have on this server and without this certificate the backup can’t be decrypted.

As before we can’t store any certificates without a master key so let’s get that created…

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(DestinationMasterKeyEncryptionPassword1234)'

Now lets see if we can restore that certificate backup we made on our new server…

CREATE CERTIFICATE SuperSafeBackupCertificate 
   FROM FILE = 'C:\Keys\SuperSafeBackupCertificate.cer'

At this point, depending on your credentials there is a good chance you will see an error similar to this…

This is because the NTFS permissions SQL Server put on the certificate and private key backup don’t give access to the service account your destination server is running under. To fix this open a Command Prompt window as Administrator and run the following command, replacing the username (MSSQLSERVER) with the account your server is running under and point it at the directory the backup keys are stored in…

icacls c:\Keys /grant MSSQLSERVER:(GR) /T

This will have now granted our SQL Server account read access to these files so let’s try restoring that certificate again…

CREATE CERTIFICATE SuperSafeBackupCertificate 
   FROM FILE = 'C:\Keys\SuperSafeBackupCertificate.cer'

That time it should go through with no error, so we now have our certificate and master key all setup, Let’s try restoring that backup again…

RESTORE DATABASE BackupEncryptionDemo 
   FROM DISK = N'C:\Keys\DatabaseBackup.bak' 
   WITH 
      MOVE N'BackupEncryptionDemo' TO N'D:\Data\EncryptionDemo.mdf', 
      MOVE N'BackupEncryptionDemo_log' TO N'D:\Data\EncryptionDemo_log.ldf'

Still no luck, the restore failed because the keys we restored are corrupt. This is because when we restored the certificate we didn’t specify our private key and password file to decrypt it, let’s drop the certificate we restored and try again…

DROP CERTIFICATE SuperSafeBackupCertificate
GO

CREATE CERTIFICATE SuperSafeBackupCertificate 
   FROM FILE = 'C:\Keys\SuperSafeBackupCertificate.cer'
   WITH PRIVATE KEY(
      FILE ='C:\Keys\SuperSAfeBackupCertificate.ppk', 
      DECRYPTION BY PASSWORD='test'
   )

Oops, we specified our password as ‘test’ when actually the password we specified when we backed up the private key was ‘(PasswordToEncryptPrivateKey123)’. We’re getting close now…

CREATE CERTIFICATE SuperSafeBackupCertificate 
   FROM FILE = 'C:\Keys\SuperSafeBackupCertificate.cer'
   WITH PRIVATE KEY(
      FILE ='C:\Keys\SuperSAfeBackupCertificate.ppk', 
      DECRYPTION BY PASSWORD='(PasswordToEncryptPrivateKey123)'
   )

We’ve now successfully restored our certificate, let’s try that database restore one last time!

RESTORE DATABASE BackupEncryptionDemo 
   FROM DISK = N'C:\Keys\DatabaseBackup.bak' 
   WITH 
      MOVE N'BackupEncryptionDemo' TO N'D:\Data\EncryptionDemo.mdf', 
      MOVE N'BackupEncryptionDemo_log' TO N'D:\Data\EncryptionDemo_log.ldf'

Bingo!!!

As one final check let’s query our only table

SELECT * FROM BackupEncryptionDemo.dbo.Test

OK so the title is a bit of a bold statement but bear with me, I’ve been burned by this too many times and the above is a rule I now follow for the reasons outlined below…

This doesn’t mean I never SELECT * because I totally do use it for ad-hoc development queries I’m just against it for us inside production systems. In this post I’m going to go over a number of the potentially unexpected issues it can cause, as with most of my posts the examples can be followed by downloading the Stack Overflow Database.

Disclaimer, the below demos were all run on SQL Server 2017 and your output may vary depending on version due to differences in statistics and optimizer choices.

Setup

Now imagine we have a requirement to report the Id,DisplayName and Location of all users who have a last accessed date between 2008-01-01 and 2008-01-31…

CREATE PROCEDURE LastAccessedReport
AS
DECLARE @StartDate DATETIME = '20080801'
DECLARE @EndDate DATETIME = '20080831'
SELECT 
   Id,
   DisplayName,
   Location
FROM
   [Users]
WHERE
   LastAccessDate BETWEEN @StartDate AND @EndDate 
ORDER BY LastAccessDate   

Now let’s create another stored procedure that does the same thing but uses SELECT *

CREATE PROCEDURE LastAccessedReportSelectStar
AS
DECLARE @StartDate DATETIME = '20080801'
DECLARE @EndDate DATETIME = '20080831'
SELECT 
   *
FROM
   [Users]
WHERE
   LastAccessDate BETWEEN @StartDate AND @EndDate 
ORDER BY LastAccessDate   

Let’s also help ourselves a little here and create an index for our report…

CREATE INDEX ndx_users_lastaccessdate_include_display_location 
   ON dbo.Users(LastAccessDate)
   INCLUDE(DisplayName,Location)

This index fully covers our non SELECT * query (There is already a clustered index on ID so we don’t need to put that on this index, for more info about this see Waiter Waiter There’s an Index in my Index).

Now onto the problem scenarios…

Increased IO

The obvious one here is the more we bring back from our queries the more IO we need to perform. It’s easy to try to write this off with “What’s a couple of extra fields?”, however let’s actually take a look at that. If we turn on statistics IO and run our non SELECT * query…

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
EXEC LastAccessedReport

(42 rows affected) Table ‘Users’. Scan count 1, logical reads 4

Pretty snappy with very few pages read. Let’s then look at our SELECT * version

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
EXEC LastAccessedReportSelectStar

(42 rows affected) Table ‘Users’. Scan count 1, logical reads 44530

4 reads vs 44530, given that a read is an 8kb Page we’ve gone from reading 32kb to 347MB! Looking at the plans we can see this is because SQL Server didn’t use our index and instead did a full scan of the clustered index, it did this because all the key lookups back to fields not in the index would have been more expensive than just scanning the whole table.

Indexes Not Being Used

The above example was a bit of a cheap shot as I deliberately created an index for the non SELECT * version so let’s now create another for SELECT *…

CREATE INDEX ndx_users_lastaccessdate_include_everything 
   ON dbo.Users(LastAccessDate)
   INCLUDE(
	AboutMe, Age, CreationDate, DisplayName,
	DownVotes, EmailHash, Location, Reputation,
	UpVotes, Views, WebsiteUrl, AccountId)

We now have another complete copy of our table just to cover this SELECT * query, in comparison this index is 347MB vs the 115MB of our other index.

If we now run our SELECT * procedure again…

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
EXEC LastAccessedReportSelectStar

(42 rows affected) Table ‘Users’. Scan count 1, logical reads 4

We can see we’ve now got using a seek on our new index rather than running a full scan over the clustered index, as a result of that the read count is now the same as the non SELECT * version (SQL Server reads 8KB at a time so the other version is returning some empty space in it’s pages hence both only needing 4 reads to return different amounts of data). However, this has come at a cost of a much more expensive index to maintain and store.

Now lets someone adds a flag to disable a user, how much damage can a single BIT field do?

 
ALTER TABLE [Users] ADD [Enabled] BIT 

Let’s now run each of our queries again and see how this affects them…

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
EXEC LastAccessedReport

(42 rows affected) Table ‘Users’. Scan count 1, logical reads 4

All still looks good here, 4 reads and we’re still using our index. Now for the SELECT * version…

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
EXEC LastAccessedReportSelectStar

(42 rows affected) Table ‘Users’. Scan count 1, logical reads 44530

Uh Oh, our finely tuned SELECT * index is no longer being used! All someone did was add a single BIT field and we’ve jumped back to reading 44530 pages! 32kb to 347MB just because a BIT field was added! The point to make here is that no matter how you design your index for a SELECT * query it’s going to be fragile and any small addition to the table schema could cause it to no longer be used, not only that but we’re still paying the cost to maintain the now unused index.

Expensive Lookups

Now let’s look at a scenario where adding a field will still allow our index to be used but with a different side effect. Let’s make a small modification to our procedure to return the results ordered by LastAccessDate, this is enough of a change that the optimizer will want to use our index even though it doesn’t contain the new BIT field we added in the previous step…

ALTER PROCEDURE LastAccessedReport
AS
DECLARE @StartDate DATETIME = '20080801'
DECLARE @EndDate DATETIME = '20080831'
SELECT 
   Id,
   DisplayName,
   Location
FROM
   [Users]
WHERE
   LastAccessDate BETWEEN @StartDate AND @EndDate 
ORDER BY LastAccessDate   

GO

ALTER PROCEDURE LastAccessedReportSelectStar
AS
DECLARE @StartDate DATETIME = '20080801'
DECLARE @EndDate DATETIME = '20080831'
SELECT 
   *
FROM
   [Users]
WHERE
   LastAccessDate BETWEEN @StartDate AND @EndDate 
ORDER BY LastAccessDate   

The non SELECT * query still does the exact same amount of reads. The SELECT * version however is slightly different…

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
EXEC LastAccessedReportSelectStar

Table ‘Users’. Scan count 1, logical reads 141

We’re now back to using our index and reads have massively dropped however we’re still well above that 4 reads we were getting before because for each row we’re having to do a lookup back to the clustered index to get our new field. Now 141 pages isn’t massive and this probably won’t notice in execution times but magnify that across more rows and potentially much bigger fields that we don’t need and this can become very costly.

Sorts and Memory Grants

Sorts are one of the most memory intensive operations your SQL Server can do, one thing that’s easy to miss is when you sort a dataset you’re moving all the fields in the row, the more fields in the row the more memory the sort needs. Let’s modify our queries slightly to sort by a non indexed field forcing a sort operation…

ALTER PROCEDURE LastAccessedReport
AS
DECLARE @StartDate DATETIME = '20080801'
DECLARE @EndDate DATETIME = '20080831'
SELECT 
   Id,
   DisplayName,
   Location
FROM
   [Users]
WHERE
   LastAccessDate BETWEEN @StartDate AND @EndDate 
ORDER BY Location   
GO
ALTER PROCEDURE LastAccessedReportSelectStar
AS
DECLARE @StartDate DATETIME = '20080801'
DECLARE @EndDate DATETIME = '20080831'
SELECT 
   *
FROM
   [Users]
WHERE
   LastAccessDate BETWEEN @StartDate AND @EndDate 
ORDER BY Location   

Neither of these queries are particularly optimized now as both are sorting by a field that is not indexed but let’s just look at how much worse one is than the other…

BCC DROPCLEANBUFFERS
SET STATISTICS IO ON
EXEC LastAccessedReport

Our non SELECT * query has taken 100MB of memory to perform that sort. Now to compare the SELECT * version…

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
EXEC LastAccessedReportSelectStar

We’re back to using a full clustered index scan as it’s now considered cheaper than the key lookups because we’re no longer getting the benefit of the index’s sort order. However, if we look at the memory…

Our query has asked for 2.1GB of memory of which has only been granted 1.2GB, we need a lot more space to sort the data now we’ve got all the extra fields that we don’t even need. This will get worse with every field that gets added to the table and can become crazy with larger fields. I’ve seen requests for over 60GB in some fairly extreme cases where a more sensibly written version of the same query needed less than 20MB.

The exact same logic is also true for things like Hash Matches if you start joining these tables.

Queries Can Be Harder To Debug/Read

When you use SELECT * in a query with several tables joined it becomes very hard to distinguish what fields are coming from what tables. As I start joining tables I make a point to prefix every field with it’s table name to make debugging and working with it later easier e.g…

SELECT
   Table1.FieldA,
   Table1.FieldB,
   Table2.FieldA
FROM
   Table1
   INNER JOIN Table2 ON Table1.Id = Table2.Table1Id
WHERE
   Table1.FieldA = 'Test'   

Hard To Remove

Having looked at the problems it’s fairly easy to stop using it going forwards but removing SELECT * from existing queries is a lot more difficult as at this point you likely won’t know what fields are being used by the calling application.

Benefits

The only benefit I’ve ever heard argued for using SELECT * is it’s less to type/maintain in the code. Given the bad things that can come out of this, I’ll pick the extra work up front every time. That’s not to say it’s never safe, if you’ve got a table you know will never change and you always want all the fields then go ahead, I just think these situations are almost non-existent, and we can never predict what might happen down the line.

Exceptions

Because there are always exceptions right? I’d still try to avoid it all together but if you really insist on using it then the below are some situations where it is unlikely to bite you…

• When querying a CTE that has already limited the fields
• From a Table Variable or Temp table where you’ve already limited the data
• As part of ETL Warehousing processes where you always want all the data
• Anything else similar to above where a limit has either already been applied or you know you’ll always need all the data

I thought I’d use this post to round up some of my other posts into a list of often overlooked/unknown TSQL features/behaviors that can be really powerful in the right situations. I may even try to keep it up to date going forwards…

• Pivoting
  • PIVOT and UNPIVOT Explained
  • Dynamic PIVOT
• Aggregating
  • Advanced Aggregations Part 1 : GROUPING SETS
  • Advanced Aggregations Part 2 : ROLLUP and CUBE
  • Advanced Aggregations Part 3 : GROUPING/GROUPING_ID
• Utility Functions
  • 2012 : IIF and CHOOSE
  • 2016 : DROP IF EXISTS, STRING_SPLIT, STRING_ESCAPE, FORMATMESSAGE and MORE!
  • 2017 : STRING_AGG
  • 2017 : TRANSLATE
• Behavior
  • NULL Handling Explained
• Development
  • Error Handling In Depth
  • Debugging With RAISEERROR Instead of PRINT
• Window Functions
  • Window Functions in Action
• Graph Data
  • Introduction To Graph Data
• Techniques
  • Layering Calculations With APPLY