Database Backup Encryption

Starting from SQL 2014, we can encrypt the database backups without encrypting the actual database files. Prior to SQL 2014, the only way to encrypt the database backups using native SQL options is by enabling Transparent Database Encryption(TDE) on the database – which basically encrypts the database files at rest and this encrypts the backups as well. Obviously, enabling TDE is a bit overkill if you want to encrypt just the backups. This issue is addressed in SQL 2014 where backups can be encrypted without enabling TDE.

Below is a simple step by step procedure to encrypt backups in SQL 2014 and later.

1. Create Master Key.
2. Create Certificate.
3. Backup the Certificate and Private Key.
4. Use the Certificate to backup the database.
5. Create Certificate on the target server.
6. Restore the database on the target server.

--Step 1:
Create Master key Encryption by Password ='SQL2016Master'
--Step 2:
Create Certificate CW_SQL With Subject='Certificate'
--Step 3:
Backup certificate CW_SQL to file ='C:\Temp\CW_SQL.Cert'
with Private Key
(
File = 'C:\Temp\CW_SQL_PrivateKey.pvt',
Encryption by password='SQLBackup'
)

--Step 4:
BACKUP DATABASE [TestDB] TO DISK = N'C:\Temp\TestDB.bak' WITH FORMAT, INIT, MEDIANAME = N'TestDB Backup',
NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [CW_SQL]), STATS = 10, CHECKSUM

--Step 5: on target server
Create certificate CW_SQL from file ='C:\Temp\CW_SQL.Cert'
with Private Key
(
File = 'C:\Temp\CW_SQL_PrivateKey.pvt',
Decryption by password='SQLBackup'
)

--Step 6: On target server
Restore Database TestDB from Disk='C:\Temp\TestDB.bak' with Recovery

When creating the certificate on the target server, the certificate name is not important – you can create the certificate with any name but the thumb print is what will be validated before restoring the database. So, as long as the certificate is created from the same cert\private key source combination even with a different name, it will work.

By default, all certificates created are valid for one year unless explicitly mentioned in the create certificate statement. While expired certificate cannot be used to backup the database, it can still be used to restore the database.
This also means expired certificates can be restored(created) should they be needed to restore the database.In fact, expired certificates will not be shown in the UI dropdown to encrypt the backups.

Without creating the certificate first, none of the restore arguments work to get the details on the backup file. So, commands like restore headeronly, restore verifyonly, restore filelistonly do not work without the certificate.

Also, along with backing up the certificate and private key, you should also backup the private key encryption password, otherwise the certificate cannot be recreated on the target server.

Query to find when last full backup was taken

The below query returns last time a full backup was taken on the database.

select database_name as [Database],a.name [BackupName],user_name as [BackupTakenBy],
backup_start_date,backup_finish_date,backup_size as [BackupSize],is_copy_only,
has_backup_checksums,b.physical_device_name as [BackupLocation]
from msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
where backup_set_id in
(select max(backup_set_id) from msdb.dbo.backupset where type='D' and is_copy_only in (1,0)
group by database_name)

If you do not want to consider copy_only backups, replace “is_copy_only in (1,0)” with “is_copy_only in (0)”.

Transaction Log Backups

Question:Does the transaction log backup contain the data from the last log backup or from the oldest active transaction?
Answer:Transaction Log backup contains log data from the last log backup.That’s why when we need to restore all the log backups in sequential order.Let’s verify using the below script.
--Session 1
Create database Dblog
go

--make sure database is in fully recovery
use DBLog
go
create table TableLog(sno int primary key identity(1,1),sname varchar(20),scity varchar(8000))
Go
begin tran
insert into TableLog values('sqlwhsiper',replicate('a',8000))

--do not commit yet.
--Commit
--sesssion 2

Backup database dblog to disk ='E:\Backup\dblog_FB.bak' with init

Backup log dblog to disk ='E:\Backup\dblog_log1.bak' with init

use DbLog
go
insert into TableLog values('sqlwhisper',replicate('a',8000))
go 10000

Backup log dblog to disk ='E:\Backup\dblog_log2.bak' with init

--Session 1. Commit the open transaction in session 1.
Commit

Backup log dblog to disk ='E:\Backup\dblog_log3.bak' with init

--Read the transaction log backup .The second log backup does not contain any reference to the active transaction in the first backup file
SELECT *
FROM
fn_dump_dblog (
NULL, NULL, N'DISK', 1, N'E:\Backup\dblog_log2.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where [operation] like '%INSERT%'
GO

--Read the transaction log backup 3.The third log backup contains any reference to the active transaction in the first backup file saying it was commited.
SELECT *
FROM
fn_dump_dblog (
NULL, NULL, N'DISK', 1, N'E:\Backup\dblog_log3.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where [operation] like '%INSERT%'
GO

--clean up
use master
go
drop database dblog

How much time does Restore VerifyOnly take?

While,it is important to verify the database backups by doing a complete restore,it might not be possible to do that in every scenario. So, in such cases, the next best thing is to do “Restore VerifyOnly”. Restore VerifyOnly does not actually restore the database but will validate the check sum, if enabled on the backup and restore some of the data page headers and will try to make sure the backup is readable and restorable as much as possible but cannot be guaranteed.

For some of our backup jobs, we have “Restore VerifyOnly” set up in the same job step as the backup. This means that SQL Agent job step runs till the database backup is taken and restore verify process has completed. Now, if we want to have a break down on how much time was spent to backup the database and verify the backup individually, we need to subtract the database backup time from the job run time to get the time taken to perform restore verify. All the backup information is available in MSDB database in backupset table. We can use msdb.dbo.backupset table to calculate the total time the backup has taken.

The backup job in this case is Full Backup job that runs once a day. So, it is easier to join the database backup with sql agent job info on the date column.

declare @databasename varchar(200)
set @databasename =
mydatabase
declare @JobName varchar(200)
set @JobName=
mybackupjobname

Select A.database_name as [DatabaseName],B.Name as [JobName],B.run_date as [Job_RunDate],B.Run_time as [Job_RunTime],
B.[JobRunTimeINMins] as [TotalJobRunTime_In_Mins],BackupSize_In_GB,[BackupTime_In_Mins],[BackupType],backup_start_date,backup_finish_date,
(JobRunTimeINMins -[BackupTime_In_Mins]) as RestoreVerify_Time_In_Mins
from
(select top 100 percent database_name,cast(backup_size/1024/1024/1024 as decimal(8,3)) as BackupSize_In_GB,Case WHEN type='D' then 'FULL' WHEN type='L' then 'Log' When type='I' then 'Diferential' End as [BackupType],
backup_start_date,backup_finish_date, datediff(minute,backup_start_date,backup_finish_date) as [BackupTime_In_Mins]
from msdb.dbo.backupset where database_name= @databasename and type='D' and Backup_start_date >=dateadd(month,-6,getdate())
order by backup_set_id desc) A INNER JOIN
(select top 100 percent B.Name,A.run_date,A.Run_time,
(cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),1,2)*3600 as int)+
Cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),3,2)*60 as int)+
Cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),5,2) as int))/60 as [JobRunTimeINMins]
from msdb.dbo.sysjobhistory A INNER JOIN msdb.dbo.sysjobs B on A.job_id=B.job_id
where step_id=0 and B.name=@JobName
order by instance_ID desc) B on B.run_date=convert(varchar(8),A.backup_start_date,112)
order by run_date desc

Deleting Old Backup Information from MSDB

MSDB is a system database which stores information on backup history,sql jobs,alerts, database email, job history …etc.With regards to backups,It stores all the backup information unless you have some delete routine to delete the information from the backup tables. Generally, there will be a retention policy which defines how long old backup files are to be retained. This can be anywhere between couple of weeks to years depending on the requirement. Once you determine your retention requirement,you might not need backup information in MSDB beyond your retention policy, as you do not have the files, this information does not really help you. Also, this will keep MSDB database size in check and will also result in smaller MSDB backup file.

So, to delete this information, there are bunch of backup tables where the information is stored. The following pic shows the corresponding tables that store backup information.
MSDB Backup Diagram

Now, Create the below procedure that deletes the data from the above tables.

If Exists(Select 1 from sys.procedures where name='usp_DeleteBackupInformation')
Begin
Drop Procedure usp_DeleteBackupInformation
End
GO
Create Procedure usp_DeleteBackupInformation(@Purgedate datetime)
as
Begin
Set nocount on
Begin Try
Begin Tran

--deletes backupfile info
Delete A from msdb.dbo.backupfile A INNER JOIN msdb.dbo.backupset B
on A.backup_set_id=B.backup_set_id Where backup_finish_date < @PurgeDate

--deletes backupfilegroup info
Delete A from msdb.dbo.backupfilegroup A INNER JOIN msdb.dbo.backupset B
on A.backup_set_id=B.backup_set_id Where backup_finish_date < @PurgeDate

--deletes restorefile info
Delete A from msdb.dbo.restorefile A INNER JOIN msdb.dbo.restorehistory B
on A.restore_history_id=b.restore_history_id INNER JOIN msdb.dbo.backupset C
on C.backup_set_id=B.backup_set_id Where backup_finish_date < @PurgeDate

--deletes restorefilegroup info
Delete A from msdb.dbo.restorefilegroup A INNER JOIN msdb.dbo.restorehistory B
on A.restore_history_id=b.restore_history_id INNER JOIN msdb.dbo.backupset C
on C.backup_set_id=B.backup_set_id Where backup_finish_date < @PurgeDate

--deletes restorehistory info
Delete A from msdb.dbo.restorehistory A INNER JOIN msdb.dbo.backupset B
on A.backup_set_id=B.backup_set_id Where backup_finish_date < @PurgeDate

--delete backupset info
Delete from msdb.dbo.backupset where backup_finish_date < @PurgeDate

--deletes backupmedia info
Delete A from msdb.dbo.backupmediafamily A where media_set_id not in
(Select distinct media_set_id from msdb.dbo.backupset)

--deletes backupmediaset info
Delete A from msdb.dbo.backupmediaset A where media_set_id not in
(Select distinct media_set_id from msdb.dbo.backupset)

Commit
End Try
Begin Catch
Select ERROR_NUMBER as ErrorNumber, ERROR_MESSAGE() as ErrorMessage,
ERROR_SEVERITY() as ErrorSeverity
Rollback
End Catch
End

Example : usp_DeleteBackupInformation '20070101'
In the above example, it will delete all the backup information prior to Jan 1st 2007.

Restoring MSDB Database

One of the primary reasons to take backups is to protect from accidentally data deletion and data corruption.

But, what happens when a SQL Server job is accidentally deleted? MSDB database contains all the information about backups,SQL Server Agent Jobs,Alerts,Operators..etc. So, the deleted job can be recovered by restoring MSDB database.

To restore the MSDB database, the server does not need to be in Single User Mode.To restore Master database, the server needs to be in single user mode and I explained it in this blog.

So, to restore MSDB, make sure you can get exclusive access on the database.Remember, MSDB is a system DB and is inherently used for many purposes such as maintaining job history,job step logs,notifications,database mail, alerts..etc. So, it may be better to stop SQL Server Agent while restoring MSDB database.To restore MSDB database:

Use Master
GO
Restore Database MSDB from disk='E:\SQL_Backup\Msdb.bak' with replace

One other alternative is to set the MSDB database in read only mode(so as, no new data is written but applications can continue to read data) and restore MSDB database as user database and then stop the SQL Server Service account and copy data and log files to where MSDB files are located and rename them as MSDB files and restart the SQL Service. This might not be possible in all cases as it involves restarting the SQL service.

StandBy option while restoring database

I think Standby option during Restore is very good option especially for testing backups.Testing backup by doing a full restore is always recommended. Of course, the RTO’s and RPO’s should define the backup schedule but it is typical to take several log,differential backups between Full backups.

So, while restoring your databases for validation, if you do not use “STANDBY” option, you will have to restore all your backups up to point in time, every single time.

The StandBy option gives the ability to restore the next successive backup on top of the existing database without having to restore all the backups. You will be able to apply over the existing one as the database will be in Read-Only Mode.

When using STANDBY option,there will be a file created during this process and typically contains all the in-flight(uncommitted) transactions since the earliest active transaction when log backup was started and when the next backup is applied over the database, it will check with the transactions in the standby file and either apply or rollback transactions accordingly.


Create Database CDC
Alter Database CDC Set Recovery FULL
Go
Use [CDC]
Create Table Stest(sno int identity(1,1),Sname varchar(20) default ‘Pollo’)
Insert into Stest(sname) Default values
Backup Database CDC to Disk =’E:\SSMS2012\CDCFB.bak’ with Init
Insert into Stest(sname) Default values
Backup Log CDC to Disk =’E:\SSMS2012\CDC_Log1.bak’ with Init
Insert into Stest(sname) Default values
Backup Database CDC to Disk =’E:\SSMS2012\CDCFB2.bak’ with Init

RESTORE DATABASE [CDC2] FROM DISK = N’E:\SSMS2012\CDCFB.bak’ WITH FILE = 1,
MOVE N’CDC’ TO N’E:\MSSSQL11.EXP2012\CDC2.mdf’,
MOVE N’CDC_log’ TO N’E:\MSSSQL11.EXP2012\Log\CDC2_1.ldf’, NORECOVERY, NOUNLOAD, STATS = 10
GO

RESTORE DATABASE [CDC2] FROM DISK = N’E:\SSMS2012\CDC_Log1.bak’
WITH STANDBY = N’E:\MSSSQL11.EXP2012\ROLLBACK_UNDO_CDC_log2.BAK’

RESTORE DATABASE [CDC2] FROM DISK = N’E:\SSMS2012\CDCFB2.bak’
WITH STANDBY = N’E:\MSSSQL11.EXP2012\ROLLBACK_UNDO_CDC2.BAK’

UPDATE: If the purpose of doing the restores is only to validate the backups,then we can leave the databases in ‘no recovery’ mode and still be able to restore other successive backups on top of it. Also, the backup ‘checksums’ are still validated even when leaving the DB in ‘No Recovery’ mode.
However, to run DBCC Checks such as DBCC CHECKDB, the DB is required to be in either online or stand by mode. I got this tip after posting a question on automating test restores on twitter #SQLHelp