70-457 Reviewer #11 Configuring and Maintaining a Backup Strategy

This topic is the second part for lesson Manage Data which covers 20% of the examination.

Understanding Backup Types

SQL Server 2012 supports the following backup types.

Full database backups – this type of backup includes all database objects, system tables and data. Transactions that occur during the backup are also recorded. Full database backup allows you to restore the database as it was during the time of the backup.

Differential backups – this type of backup backs up data that has since changed from the last full backup. You use this type of backup in conjunction with the full backup. Differential backups back up data that changed since the last full backup, even if there has been a subsequent differential backup.

Transaction log backups – this type of backup records the changes that have occurred since the previous transaction log backup and truncates the transaction log. Transaction log backups function incrementally rather than differentially, hence during the restore process, you must do the restoration in sequence.

File and filegroup backups – For large database with multiple files and filegroups, you can perform a file and filegroup backups if you need to backup individual database files and filegroups. You need to perform a transaction log backup after you have done a file and filegroup backup.

Copy-only backups – this type of backup is similar to a full database backup or transaction log backup but do not affect the backup sequence.

In your backup strategy, you can plan to combine more than one backup types. In my case, I implemented a strategy of full backup daily, morning and midday differential backup and transaction log backup every 15 minutes. When implementing a backup plan, you must also take into consideration the method you will use to restore your database.

Using Backup Compression

Backup compression reduces the amount of the space required to store a backup, but the CPU cost during compression is high. You can reduce the load in the CPU via the Resource Governor and limit the CPU usage.

Backup compression is disabled by default. You can configure the backup compression at the instance level. In the Server Properties, go to Database Settings and check the checkbox for Compress backup.

Server Properties

Understanding Recovery Models

Database recovery models determine what your backup plan is and the strategy you implement to meet your Recovery Point Objective (RPO). RPO determines how much data loss your organisation can afford in the event of failure. The Recovery Time Objective (RTO) specifies a maximum time however it can take to restore data to the RPO.

SQL Server 2012 supports the following recovery model.

Simple – No transaction log backups are taken. You can restore data from recent database backup only.

Full – Full recover requires you to take transaction log backups and full backups. Depending on the type of failure, you can recover to the point of the full backup or the last point of transaction log backup.

Bulk-logged – This is a special type of recovery model that minimizes transaction log activity during bulk operations. You cannot perform point-in-time recovery when using this model.

To configure the recovery model for your database, go to Options page on the database properties and select the appropriate recover model for you. See screenshot below.

Recovery Model

Alternatively, you can run the ALTER DATABASE statement with SET RECOVERY option.

All system database uses the simple recovery model by default. You can plan to backup your system databases on regular basis but not required to be as frequent as your full backup for your user database. It is important to backup the master and msdb system database regularly.

Backup for Mirrored Database

If you have mirroring session in place, you can back up only the principal database. When you are performing a backup of the principal database you cannot use the option BACKUP LOG WITH NORECOVERY.

Backup for Replicated Database

You should include the following in your backup strategy

  • master, msdb and publication database at the publisher instance
  • master, msdb and distribution database at the distributor instance
  • master, msdb and subscription database at the subscriber instance

For your snapshot and transactional replication, you should set the sync with the Backup option on the publication and distribution database. This will ensure that the transactions in the publication database transaction log are not truncated until they have been backed up at the distribution database.

Viewing Backup History

Backup events are written to the SQL Server log from the backup source. You can view this by going to the SQL Server log file viewer. Alternatively you can run the query below to check if your backup jobs were successful.

SELECT bup.name, bup.type, bup.database_name, bup.backup_start_date, bup.backup_end_date
FROM msdb.dbo.backupset bup
ORDER BY bup.backup_start_date DESC

I will cover lesson for restoring SQL Server Database in my next post. Happy weekend!

Credits to Peter Kudlacz for featured image.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: