Manage Backups and Restore

An organization´s data is one of its most important assets. Planning for backups is easy when your database is very small. They complete quickly and don't take up much storage space. But when your database is mature and grow to gigabytes or terabytes in size, then backups become an entirely different beast.

Develop a backup strategy

A correct backup strategy helps ensure that you incur minimal or no data loss, developing this correct backup strategy is probably the most important task you will have as database administrator. Do not make assumptions about what your organization requires, but instead engage the relevant stakeholders within your organization.

Design a backup strategy

To design a disaster recovery plan your organization's management or business stakeholders will need to define the following three main requirements:

  • Recovery Time Objective (RTO) The RTO defines the maximum allowable downtime following a disaster incident. the goal for the database administrator is to restore normal business operations within this time frame.

  • Recovery Point Objective (RPO) The RPO defines the maximum acceptable amount of data loss following disaster incident. The RPO is commonly expressed in minutes.

  • Recovery Level Objective (RLO) The RLO defines granularity of the data needs to be restored following disaster incident. For SQL Server it could be SQL Server instance, group of database, a database, a set of tables, or a table.

Backup Operations

  • Full A full backup captures everything in the database.

  • Differential A differential backup captures everything in the database since the last full backup.

  • Incremental An incremental backup captures everything in the database since the last incremental backup. In SQL Server it is called a log backup. You need to perform a full backup before you can perform log backups.

To design a backup strategy, you need to take into account a number of factors including:

  • The size of your database A larger database will take longer to backup.

  • The structure of your database files A database that consist of a single primary datafile will be difficult to back up within an appropriate maintenance window as it gets larger.

  • The speed/throughout of the network and storage subsystems involved The underlying hardware and networking infrastructure can substantially impact the duration your backup and restore operations. this will in turn impact your RTO.

Configure database recovery models

In the Simple Recovery Model, the transaction log is automatically purged and kept to a small file size. Because of this, you can't make log backups or use some high availability features such as Always On or Database Mirroring. Most importantly though, in the Simple Recovery Model, you cannot restore your database to an arbitrary point in time. This means that should you need to restore, your database will only be as current as the last full or differential backup, and data loss is a real possibility.

The Full Recovery Model, completely logs every transaction that occurs on the database. By having this information, a backup procedure can arbitrarily choose a point in time and restore the database to the way that it was at that point. It does this, by first restoring a full backup, then replaying the changes recorded in the transaction log, step by step, to modify the data up to the point in time desired. Of course, to do this, the transaction logs, will need to be backed up, in addition to the full database back up.

Recovery Model called Bulk-Logged. This model is similar to the Full Recovery Model, in that a transaction log is kept, but certain transactions regarding, bulk-loading of data are only minimally recorded. This makes the bulk data imports perform quicker and keeps the file size of the transaction log down, but does not support the point in time recovery of the data.

Exercise Zone 1

-- create a new database



USE RecoveryModelDB;


-- set the recovery model in SSMS

-- review the recovery model with T-SQL

SELECT name, recovery_model_desc

FROM sys.databases

WHERE name = 'RecoveryModelDB';


SELECT name, recovery_model_desc

FROM sys.databases

WHERE name = 'model';