Understanding SQL Server Media Set, Media Family and Backup Set

I am sure that you have been taking backups of SQL Server databases and you know that backups can be taken either to a disk or a Windows Azure Blob Storage service. However, many of us are unaware about some terms used with backup operations, such as Media Set, Media Family and Backup Set. Let's try to understand these with an example.

Media Set

This is the main container that holds backup sets using backup devices. Generally we take a backup of the database without mentioning the name of Media Set, hence the device we used is attached to a media set that has no name. Remember, Media Name is optional There is no harm of not mentioning the name for Media Set but it is always advisable to name it because it helps us to identify the correct backup sets.

Media Family

This refers the device (single non-mirrored device or set of mirrored devices) used for holding the backup in the Media Set. If Media Set has one device, then Media Set contains one Media Family. If Media Set has two devices (say that you take a backup to two disk files), then it contains two Media Families. Each Media Family in the Media Set is assigned with a number called Media Sequence Number.

Backup Set

Backup Set is the content of the database backup we take to the Media Set. If you take a backup of your Sales Database, then it is the Backup Set in the Media Set. If Media Set contains one Media Family (means one device), content is written to it. If it contains two Media Families (or more), content is distributed among families (devices), working similar to stripe set.

Media Header

Each and every Media Family in the Media Set has a header that holds information such as Name, Unique identification, Sequence number, etc. This header remains until the Media is reformatted.

Let's take a backup of a database and see how this works. I have a database called Marketing and I am taking a backup of my database to two files. Note that I have mentioned the Media Name which is the Media Set Name. As mentioned above, it is not a must but it is always better to name it.

Here the TSQL code for backing up;

USE master;
GO

BACKUP DATABASE Marketing 
TO DISK = N'D:\Backup\Marketing\BackupDevice01.bak'
 ,  DISK = N'D:\Backup\Marketing\BackupDevice02.bak' 
WITH NOFORMAT, NOINIT,  MEDIANAME = N'MarketingMediaSet01'
GO

Here is the way of doing the same with GUI.

Just like the way described above, you can click on the OK button and take the backup of one of your databases. Since I have used two files, my backup is distributed (approximately 50% per each) among two files. Let's query some tables in the msdb database to see how Media Set, Media Families and Backup Sets have been created.

USE msdb;
GO

SELECT * FROM dbo.backupmediaset WHERE name = 'MarketingMediaSet01';
SELECT * FROM dbo.backupmediafamily WHERE media_set_id = 35;
SELECT * FROM dbo.backupset WHERE media_set_id = 35;

If I did not mentioned the name for Media Set, I will see blank for name of the Media Set. As you see, Media Set named MarketingMediaSet01 is created and two Media Families have been created for each device I used. I took only one backup, hence there is only one Backup Set for this media set.

This is what I used for explaining the same in my workshops;

If I take another backup of Marketing Database again to the same without overwriting (WITH NOINIT), I will see an additional Backup Set under same Media Set.

I can illustrate the Media Set now as below;

Let's take one more backup to the same Media Set. This time, I am going to take a backup of different database, which is Adventure Works.

USE master;
GO

BACKUP DATABASE AdventureWorks2014 
TO DISK = N'D:\Backup\Marketing\BackupDevice01.bak'
 ,  DISK = N'D:\Backup\Marketing\BackupDevice02.bak' 
WITH NOFORMAT, NOINIT,  MEDIANAME = N'MarketingMediaSet01'
GO

I can take it without any issue and I can see another Backup Set added to my Media Set.

Here is the illustration for it now;

You may ask that it is possible to take a backup of another database (or same) without mentioning the Media Name. Yes, it is possible, you can take a backup to same devices without adding the Media Set name but it will be added to the same Media Set.

However, if you try to use the same devices with a new Media Name, you will get the following error because it is not possible to attach these devices to another Media Set.

Msg 4037, Level 16, State 1, Line 1
The user-specified MEDIANAME "NewMediaSet" does not match the MEDIANAME 
"MarketingMediaSet01" of the device "D:\Backup\Marketing\BackupDevice01.bak".
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

SQL Server 2016 Partial backup operation on a READONLY database backs up all file groups

While I was writing a code on backing up operation for SQL Server 2016, I noticed that there is an issue with partial backup. As per documentations, when we take a partial backup using READ_WRITE_FILEGROUPS option, it backs up;

• only PRIMARY, all READ/WRITE file groups and mentioned READONLY file groups.
• only PRIMARY if the database is READONLY.

Read more on this at: https://msdn.microsoft.com/en-us/library/ms191539.aspx

However, if you perform this operation with SQL Server 2016 RTM, it backs up;

• all file groups; PRIMARY, all READ/WRITE file groups including READONLY file groups - which is NOT the expected behavior.

You can apply the latest cumulative update (I applied CU2), then it backs up;

• only PRIMARY and all READ/WRITE file groups - which is still NOT the expected behavior.

Here is the code I tested, you also can run the same and see;

USE master;
GO
-- creating the database
CREATE DATABASE [TestDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDatabase_Data_1', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data1.mdf' 
 , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 

 FILEGROUP [FileGroup_Main] 
( NAME = N'TestDatabase_Dat_2', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data2.ndf' 
 , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 
 FILEGROUP [FileGroup_History] 
( NAME = N'TestDatabase_Dat_3', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data3.ndf' 
 , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 
( NAME = N'TestDatabase_Log', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Log.ldf' 
 , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

-- creating tables and inserting records
USE [TestDatabase]
GO

CREATE TABLE [dbo].[Configuration]
(
 [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [ConfigurationName] [varchar](100) NOT NULL,
 [ConfigurationValue] [varchar](100) NOT NULL,
 [Settings] char(4000) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Sales]
(
 [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [SalesDate] [date] NOT NULL,
 [Amount] [decimal](16, 2) NOT NULL,
 [Settings] char(4000) NOT NULL
) ON [FileGroup_Main]
GO

CREATE TABLE [dbo].[Sales_History]
(
 [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [SalesDate] [date] NOT NULL,
 [Amount] [decimal](16, 2) NOT NULL,
 [Settings] char(4000) NOT NULL
) ON [FileGroup_History]
GO

-- Insert set of records
INSERT INTO dbo.Configuration (ConfigurationName, ConfigurationValue, Settings) VALUES ('A', '1', 'a');
GO 5000
INSERT INTO dbo.Sales (SalesDate, Amount, Settings) VALUES ('01/01/2016', 10000, 'a');
GO 5000
INSERT INTO dbo.Sales_History (SalesDate, Amount, Settings) VALUES ('01/01/2010', 10000, 'a');
GO 5000


USE master;
GO

-- Make one FG as read-only
ALTER DATABASE TestDatabase MODIFY FILEGROUP [FileGroup_History] READ_ONLY;
GO

-- take a full database backup
-- this takes all three files
-- backup size is 63 MB
BACKUP DATABASE TestDatabase 
TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup01.bak'
WITH INIT
GO

-- take a backup with READ_WRITE_FILEGROUPS
-- this takes only TestDatabase_Data_1 and TestDatabase_Dat_2
-- backup size is 43 MB
BACKUP DATABASE TestDatabase READ_WRITE_FILEGROUPS
TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup02.bak'
WITH INIT
GO

-- Make the database as read-only
ALTER DATABASE [TestDatabase] SET  READ_ONLY 
GO

-- take a backup with READ_WRITE_FILEGROUPS
-- as per MSDN, this should take only PRIMARY
-- But this takes all three files with RTM and exlude read-only files with CU2
-- backup size is 63 MB
BACKUP DATABASE TestDatabase READ_WRITE_FILEGROUPS
TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup03.bak'
WITH INIT
GO

Result before applying CU2;

Processed 2840 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_3' on file 1.

Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.

BACKUP DATABASE successfully processed 7898 pages in 0.275 seconds (224.357 MB/sec).

Processed 2840 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.

Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.

BACKUP DATABASE...FILE= successfully processed 5370 pages in 0.191 seconds (219.611 MB/sec).

Processed 2840 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_3' on file 1.

Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.

BACKUP DATABASE...FILE= successfully processed 7898 pages in 0.271 seconds (227.674 MB/sec).

Result after applying CU2.

Processed 2872 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.
Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.
Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_3' on file 1.
Processed 3 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.
BACKUP DATABASE successfully processed 7931 pages in 0.289 seconds (214.379 MB/sec).
Processed 2872 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.
Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.
Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.
BACKUP DATABASE...FILE= successfully processed 5402 pages in 0.202 seconds (208.890 MB/sec).
Processed 2872 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.
Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.
Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.
BACKUP DATABASE...FILE= successfully processed 5402 pages in 0.205 seconds (205.852 MB/sec).

Be aware on this, this may be the expected behavior of SQL Server 2016 though I see it as an issue, working on it, I will update the post if I hear something new on it.

A nonrecoverable I/O error occurred on file Backup to URL received an exception

My previous post discussed SQL Server Backup to URL, this is an error related to it.

​A nonrecoverable I/O error occurred on file "https://dinesqlclassicstorage.blob.core.windows.net/classisstroagecontainer/Sales_20160512.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (404) Not Found..

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

As usual, did a search but did not find a good solution but figured out a possible reason for this. The reason could be, using a Azure Classic Storage. If we try to use the newest storage, this error does not occur. However, I cannot exactly say that this is the only reason for this error. There can be many other reasons but try with new storage and see if you experience the same.

SQL Server Backup to URL

Although this was introduced with SQL Server 2012, as per my experience, the usage of it is very low because it is unknown to many or do not use this facility to have a good backup strategy. When maintaining an important database, an implementation of a backup strategy is a must and it should be based on RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

Backup strategy is a part of Disaster Recovery solution. If you worry about the space for holding the backup, protecting backups from disasters, and securing your backups, one of the best options that can be used is Backup to URL.

Setting it up is very simple. You need to make sure that you or your company has an account with Azure and you have a storage created. Make sure a container is created in your storage for holding backups.

First, you need to create a Credential in your SQL Server instance for connecting with your Azure Storage. You can either use TSQL or GUI given under Security section.

Once the credential is created, it can be used for backing up your database to Azure Storage. Again you can use either TSQL or GUI.

BACKUP DATABASE Sales
TO URL = 'https://dinesqlstorage.blob.core.windows.net/sqlbackups/Sales_20160512.bak' 
      WITH CREDENTIAL = 'AzureCredential' 
     ,COMPRESSION
     ,STATS = 5;​

Once backed up, if you check your storage, you should see the file;

Note that most of the options that are available with general backup statement are available with backup to URL too. However some of the significant differences are;

• Appending to an existing backup file is not possible (INT | NOINT is ignored)
• MIRROR TO option is not supported
• Generally backup device name is allowed with 259 characters. This option needs 36 characters for azure URL and leaving 223 characters to specify name of the account, container and backup name.

[Guest Post] Database disaster prevention by Olek Nilson

This post is written by Olek Nilson.

Database disaster prevention is one of the greatest pains in life of every DBA. Data from database are changing daily, gradually expanding the size of the latter. Of course, there is no perfect way to prevent database corruption, but there are some practical steps that can help you to bring the risk to minimum.

It’s important to say a few words about Microsoft Corporation. In spite of the fact that SQL Server is 100% Microsoft product, the company does not provide any means to protect your database, meaning that database protection completely up to the user.

Below we would like to discuss cases that can trigger database disaster.

One of the most popular cases that can potentially cause database corruption is hardware failure, for example, incorrect work of Disk, CPU or Memory Module.

Another thing that can corrupt your database is Antivirus software. Please note that after your antivirus software is installed, all SQL Server Database Files should be excluded from being scanned by the Antivirus software.

The next issue that can potentially damage your database is a desire to save free space on the local disk. Based on my personal experience, there were some users who stored all SQL Server database files in zip or 7zip files to save some space on their disks. Ultimately, these actions lead to a database failure. If you desperately need free space, you can use third party tool to make database backups with compression and store them online.

Another important thing that will help you to prevent database disaster is setting up the latest updates for SQL Server and Windows. Otherwise it will cause incorrect working of your shift.  

And do not forget about the case with a power failure that may lead to all databases being corrupted.

It is not an exhaustive list of failures that can destroy your database. So what shall we do to prevent database disasters and minimize the risk? You have to remember that there is no perfect way or method that will ensure 100% protection for your database. However, the best thing that can help you to minimize the risk of the database corruption is to make scheduled database backups. Database backups cannot prevent the corruption, but they can help you to restore the database with minimal losses.   

It is recommended that all experienced DBAs should make regular database backups. This leads to a question  – How often should I make backups or what backup strategies should I employ? Before answering this question, please consider yet another question - How much data can you afford to lose? Working as a DBA, I have had a great many databases to restore and I can say that the more quality backups you have the less data you will eventually lose. 

Presently there are three ways to make database backups:

1. Using T-SQL commands:
   
   If you prefer using T-SQL command you have to keep in mind that you need to make database backups all the time. Even you have to create a schedule and carefully stick to it. Perhaps, make backups with the help of T-SQL commands are comfortable if the database is not large and grows slowly. But in the most cases changes in the database are taking place all day and night, so how do make backups at night? Of course, to solve this issue you can find in the Web scripts for making schedule backups.
   
   So, if you make the decision to make database backups with the help of T-SQL use the following commands:
   
   

   -- To make a full database backup use the following command:
   BACKUP DATABASE AdventureWorks TO DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
   
   -- Use the following command to make a differential backup:
   BACKUP DATABASE AdventureWorks TO DISK = 'Z:\SQLServerBackups\AdventureWorksDiff.bak' WITH DIFFERENTIAL
   
   -- The following command will help you to make a transaction log backup:
   BACKUP LOG AdventureWorks TO DISK = 'Z:\SQLServerBackups\AdventureWorksLog.bak'
   

2. With the help of SSMS (SQL Server Management Studio):
   
   Using SSMS for making backups is a little bit easier then make backups with the help of T-SQL commands. You don't need to write commands every time when you are going to make a backup it is enough to make a couple simple steps, which we described below. It has to be admitted that you can also make scheduled backups with the help of SSMS, but it will take more efforts from you.
   
   To make a backup please:
   a. Right-click on the database you want to backup
   b. Select “Tasks”, then “Back up…”
   c. Choose “Backup type"
   d. Add backup destination
   e. Click “OK”
3. Or use third party tools; You can choose such enterprise solutions as SQL Backup Pro from RedGate, something similar to SqlBackupAndFtp, or try out Sqlbak.com if you prefer SAAS model.

In my opinion, this is the best way for making scheduled database backups. Here everything you need is to create the job for the scheduled backups and run the application. The third party tool will make backups according to the schedule.

 So, if your databases are growing fast and there are many changes occurring every hour, it would be better to choose “maniacal” backup plan, for example, it is a Full database backup every 24 hours, a Differential backup every three hours and make transaction log backups every 15 minutes. Yes, perhaps it will take more time to restore your database should failure occur, but the percentage of data loss will be minimal.

Encrypting backups and restoring encrypted backups

Your applications cannot be survived without database backups!

Database backup is a fundamental requirement and it is the key task among maintenance tasks. It protects both application and data against almost all failures and it is our duty to protect it. Not only that, it should be well secured because it holds all information related to your organization, hence you should take all possible actions for protecting it from unauthorized access. One method we use for protecting our backups is using an off-site storage, making sure that we have them at then event of a disaster. In addition to that, it can be more secured applying encryption. This post speaks about encrypting SQL Server database backups and restoring them.

SQ:L Server backup encryption uses algorithms such as AES 128, AES 192, AES 256, and Triple DES. And when encrypting, it needs either a certificate or asymmetric key for encrypting data. Here are the steps for preparing the instance and taking a backup.

1. Create the database master key in the master database if it is not created before. This key is a symmetric key and it will be used for protecting all other keys and certificates.
   
   

   -- creating the master key if not exist
   USE master;
   GO
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
   GO
   

2. Backup the master key created and keep it in a secure location. This is required if the database going to be restored to a different instance.
   
   

   -- Back up the database master key
   OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd';
   BACKUP MASTER KEY TO FILE = 'D:\SecuredLocation\master.key'
    ENCRYPTION BY PASSWORD = 'Ma$terPa$$w0rd';
   GO
   

3. Create a certificate or asymmetric key for encrypting backups.
   
   

   -- Create a certificate
   CREATE CERTIFICATE AdventureWorksBackupCertificate
    WITH SUBJECT = 'Backup Encryption Certificate';
   GO
   

4. Backup the certificate or asymmetric key and keep it in a secure location. This is required if the database going to be restored to a different instance.
   
   

   BACKUP CERTIFICATE AdventureWorksBackupCertificate 
   TO FILE = 'D:\SecuredLocation\AdventureWorksBackupCertificate.cer'
   WITH PRIVATE KEY ( FILE = 'D:\SecuredLocation\AdventureWorksBackupCertificate.key' ,
        ENCRYPTION BY PASSWORD = 'CertificatePa$$w0rd');
   GO
   

5. Perform the back up operation using the certificate or asymmetric key created, mentioning the algorithm for encryption.
   
   

   -- Take the initial backup
   BACKUP DATABASE AdventureWorks2014
    TO DISK = 'D:\BackupLocation\AdventureWorks2014.bak'
   WITH INIT, ENCRYPTION( ALGORITHM = AES_128,
     SERVER CERTIFICATE = AdventureWorksBackupCertificate)
   ;
   GO
   

Here are the steps for restoring the backup to the same instance.

1. Restore the backup just like restoring non-encrypted backup. No special steps need to perform as long as master database key and certificate are exist.
   
   

   -- Same Server - restore as a normal backup
   RESTORE DATABASE AdventureWorks2014
    FROM DISK = 'D:\BackupLocation\AdventureWorks2014.bak'
   WITH REPLACE, NORECOVERY;
   GO
   

Here are the steps for restoring the backup to a different instance.

1. Create a master key if it is not exist. This does not need to be the same. You can use the backup of the master key taken if it is a recovery from a complete failure.
   
   

   -- create the master key if not exist
   USE master;
   GO
   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
   

2. Create the certificate or asymmetric key from the backup taken from original instance. Name should be same.
   
   

   -- create the certificate in the new instance
   -- from backup files
   CREATE CERTIFICATE AdventureWorksBackupCertificate
   FROM FILE = 'D:\NewServerSecuredLocation\AdventureWorksBackupCertificate.cer'
   WITH PRIVATE KEY (
   DECRYPTION BY PASSWORD = 'CertificatePa$$w0rd',
   FILE = 'D:\NewServerSecuredLocation\AdventureWorksBackupCertificate.key');
   GO
   

3. Perform the restoration as a normal restoration.
   
   

   RESTORE DATABASE AdventureWorks2014
    FROM DISK = 'D:\NewServerBackupLocation\AdventureWorks2014.bak';
   GO