Category: SQL Server Backup

This article will explain what the following terms mean:

• RTO (Recovery Time Objective),
• RPO (Recovery Point Objective),
• ERT (Estimated Recovery Time).

The terms ERT, RTO, RPO are used in IT’s “Business Continuity and Disaster Recovery policy”, and basically they are time characteristics (measured in seconds, hours etc.) of a disaster recovery of a data system.

In the article RT (Recovery Time) and RP (Recovery Point) definitions are used for defining RTO, RPO, and ERT. This approach may lead to a new understanding of the terms and further discussions.

Read More

SQL Server allows to make backups and restore not only the entire database but also a single filegroup or even a separate file.  This is called a file backup. This type of backup contains all data from one or more files or filegroups.

File backups are often used to increase the recovery process speed by restoring only damaged files, without restoring the rest of the database, if necessary. Assume there is a database that contains several files. Each file is stored on a separate disk and suddenly one disk fails. In this case the recovery process will not take more time because there is no need to restore the entire database, it will be enough to restore only the file from the failed disk. Read More

Point-in-time recovery allows to restore a database into a state it was in any point of time. This type of recovery is applicable only to databases that run under the full or bulk-logged recovery model. If the database runs under the bulk-logged recovery model and transaction log backup contains bulk-logged changes, the recovery to a point-in-time is not possible.

To understand how point-in-time recovery works let’s review the use case with a sample database (under the full recovery model) where a full backup is made every 24 hours and a differential database backup that is created every six hours, and transaction log backups are created every hour. Please see the picture below:
Read More

A database backup process is an integral part of a recovery system. All backups must be operable so that they can be restored at any time. It is recommended to make regular database backups and the majority of SQL Server users actually follow this recommendation. But how can you be sure that you will be able to restore the database from the backups? Sometimes backups can be corrupted. This is why it is useful to test database backups for their integrity. Of course, the best check for any backup is a restore to a server, but there are some other, less radical ways of doing it. Let’s explore CHECKSUM and VERIFYONLY options. Read More

Read More

The peculiarity of partial backup is that it does not contain read-only filegroups. So it’s a good choice if your database has a read-only filegroup and there is no need to backup it all of the time. Though all recovery models in SQL Server support partial backup, it was primarily designed for use under the simple recovery model as it makes backup of huge databases, containing one or more read-only filegroups, more flexible.

A partial backup can be applied both to full or differential backups, though it is not applicable to transaction log backup. As far as differential partial backup is concerned, only the data extents that have changed in the filegroups since the previous partial backup are recorded into it. Please note that differential partial backups can be applied only with partial backups. Read More

Every record in SQL Server transaction log has its own log sequence number or LSN. Log sequence numbers are ordered in such a way that changes related to the record with greater LSN occur after the changes related to records with smaller LSN. Each backup that is set in SQL Server has FirstLSN and LastLSN values. To find out all backup’s LSN values you need to look at its header using the following syntax:

RESTORE HEADERONLY FROM DISK = 'full.bak' 

Read More