xp_delete_file to delete older backups

We can use xp_delete_file command to delete older backup files. The command has a parameter to delete files from subfolders as well.
However, it cannot delete files from nested subfolders, but only one level down.

Example: The below code deletes backup files older than 180 days.

declare @backupdeletedate datetime = dateadd(day,-180,getdate())
declare @backuplocation varchar(200) = 'E:\temp\backups'
declare @fileextension varchar(10)='bak'
declare @deletesubfolder tinyint =1 --0 = no subfolders
declare @filetype tinyint=0 --1 = report files

EXECUTE master.dbo.xp_delete_file @filetype,@backuplocation,@fileextension,@backupdeletedate,@deletesubfolder

Advertisement

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.

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.

Database Restore Report

It is very typical to frequently have a copy of production database restored on to DEV\QA\TEST Instances, so as to work with more real time data. The below query helps in figuring out details such as when the database was restored,source server of restored database, user who restored it, which backup file was used to restore… etc.

Also, this query is useful to generate a report of all backup files that were restored along with other details while testing your backups by physically restoring them.

NOTE: At this point, the script works correctly if your backup types are Full, Log, Differential.Also, it is a good practice to delete unwanted backup history from MSDB database. So, if the backup\restore history data got purged, we might not see results from some databases.

Select A.destination_database_name as [Restored_Database_Name],
A.user_name as [RestoredBy_User_Name],
A.Restore_Date,A.replace as [Replace],
Case When A.restore_type='D' then 'Full'
When A.restore_type='I' then 'Differential'
When A.restore_type='L' then 'Log' End as [Restore_Type],
B.database_name as [Backup_DatabaseName],
B.database_creation_date as [Backup_DB_Creation_Date],
B.server_name as [Backup_ServerName],
B.user_name as [Backuped_by_User],
B.backup_start_date as [Backup_DB_Start_Date],
B.backup_finish_date as [Backup_DB_Finish_Date],
Case When B.[Type]='D' then 'Full'
When B.[Type]='I' then 'Differential'
When B.[Type]='L' then 'Log' End as [Restore_Type],
D.physical_device_name as [BackupFile],Position as [Backup_File_Position],
D.family_sequence_number as [Split_Backup_Sequence]
from msdb.dbo.restorehistory A
INNER JOIN msdb.dbo.backupset B on A.backup_set_id=B.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily D on D.media_set_id=B.media_set_id
INNER JOIN (Select Max(restore_history_id) as restore_history_id ,
Destination_Database_Name
from msdb.dbo.RestoreHistory
where Restore_Type='D'
Group by destination_database_name) C on C.destination_database_name=A.destination_database_name
and A.restore_history_id >=C.restore_history_id

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.

FileGroup Backup – Simple Recovery

–Create a Test Database; Add a Secondary FG and add file to the FG.
Create Database FooBarDB
GO
Alter Database FooBarDB Set Recovery Simple
Alter Database FooBarDB Add FileGroup Secondary
GO
ALTER DATABASE [FooBarDB] ADD FILE
( NAME = N'FooBarDB2', FILENAME = N'E:\DATA\FooBarDB2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Secondary]
GO
–Create a table on the Secondary FG;Insert some data and Switch it Read only mode.
use FooBarDB
Go
Create Table Stest(sno int,Sname varchar(20)) on [Secondary]
Insert into STest(sno,sname) Values(1,'Filegroup')
GO
Use Master
ALTER DATABASE [FooBarDB] MODIFY FILEGROUP [Secondary] READONLY

–Backup Secondary FG.Since it is Read only FileGroup, it can be backed up and restored by itself unlike Read_Write FileGroups.
Backup Database FooBarDB FILEGROUP = N'Secondary' to Disk ='E:\DATA\FooBarDB_Secondary_FG.bak'with init
–Create table in Primary FG and In simple Recovery, all the Read_Write_FileGroups have to be backed up together.
use FooBarDB
Go
Create Table StestPrimary(sno int,Sname varchar(20)) on [Primary]
Insert into StestPrimary(sno,sname) Values(1,'FGPrime')
–In Simple Recovery, While taking FileGroup backup, all Read Write FG should be backed up.Individual Read_write Filegroups cannot be backed up.
Use Master
GO
Backup Database FooBarDB Read_Write_FileGroups to Disk ='E:\DATA\FooBarDB_RWFG_FG.bak' with init

—Restore the Read Write FileGroups Backup. Read Write Filegroups needs to be restored together in Simple Recovery.

Restore Database FooBarDB_RS From Disk='E:\DATA\FooBarDB_RWFG_FG.bak' WITH FILE = 1,
MOVE N'FooBarDB' TO N'E:\DATA\FooBarDB_RS.mdf',
MOVE N'FooBarDB_log' TO N'E:\DATA\FooBarDB_RS_0.LDF',
MOVE N'FooBarDB2' TO N'E:\DATA\FooBarDB_RS_1.ndf',recovery

–can access data in tables present on Primary FG.
select * from FooBarDB_RS.dbo.StestPrimary
–cannot access data in tables present on Secondary FG as it has not been yet restored.
select * from FooBarDB_RS.dbo.Stest
–Restore the Secondary FileGroup
Restore Database FooBarDB_RS From Disk='E:\DATA\FooBarDB_Secondary_FG.bak'

–can access data in tables present on Secondary FG now.
select * from FooBarDB_RS.dbo.Stest

FileGroup Backup – Full Recovery

–Create a Test Database; Add a Secondary FG and add file to the FG.
Create Database FooBarDB
GO
Alter Database FooBarDB Set Recovery Full
Alter Database FooBarDB Add FileGroup Secondary
GO
ALTER DATABASE [FooBarDB] ADD FILE
( NAME = N'FooBarDB2', FILENAME = N'E:\DATA\FooBarDB2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Secondary]
GO

–Create a table on the Secondary FG;Insert some data and Switch it Read only mode.
use FooBarDB
Go
Create Table Stest(sno int,Sname varchar(20)) on [Secondary]
Insert into STest(sno,sname) Values(1,’Filegroup’)
GO
Use Master
ALTER DATABASE [FooBarDB] MODIFY FILEGROUP [Secondary] READONLY

–Backup Secondary FG
Backup Database FooBarDB FILEGROUP = N’Secondary’ to Disk =’E:\DATA\FooBarDB_Secondary_FG.bak’
–Create table in Primary FG and Backup Primary FG and Log. Log Backup needed for FG level restoration in Full recovery.
use FooBarDB
Go
Create Table StestPrimary(sno int,Sname varchar(20)) on [Primary]
Insert into StestPrimary(sno,sname) Values(1,’FGPrime’)

Use Master
GO
Backup Database FooBarDB FILEGROUP = N’PRIMARY’ to Disk =’E:\DATA\FooBarDB_Primary_FG2.bak’
Backup Log FooBarDB to Disk =’E:\DATA\FooBarDB_log.bak’

–Restore the Primary FG. Keyword “PARTIAL” makes the data in the Primary FG accessible.

Restore Database FooBarDB_RS From Disk=’E:\DATA\FooBarDB_Primary_FG2.bak’ WITH FILE = 1,
MOVE N’FooBarDB’ TO N’E:\DATA\FooBarDB_RS.mdf’,
MOVE N’FooBarDB_log’ TO N’E:\DATA\FooBarDB_RS_0.LDF’,
MOVE N’FooBarDB2′ TO N’E:\DATA\FooBarDB_RS_1.ndf’,
Norecovery,Partial

Restore Log FooBarDB_RS From Disk =’E:\DATA\FooBarDB_log.bak’
–can access data in tables present on Primary FG.
select * from FooBarDB_RS.dbo.StestPrimary
–cannot access data in tables present on Secondary FG as it has not been yet restored.
select * from FooBarDB_RS.dbo.Stest
–Restore the Secondary FileGroup
Restore Database FooBarDB_RS From Disk=’E:\DATA\FooBarDB_Secondary_FG.bak’

–can access data in tables present on Secondary FG now.
select * from FooBarDB_RS.dbo.Stest