Database backups are generated as part of the business continuity planning procedure. Disasters with server or its database are a common scenario that takes place every now and then lead to the loss of an enormous amount of data. In order to sustain the same, administrative ensure backup plans as part of their administrative roles apart from handling the server. There are different ways to restore a SQL Server database, which have been discussed in the segment below for a better understanding of the concept and fulfilment of different user needs.

Discussing Different Ways to Restore A SQL Server Database

Servers deal with not just one but multiple databases which requires their proper maintenance in an automated manner. Automated backup procedure puts least amount of responsibilities on an administrators shoulder.

Following is a diagram represents the methods of restoring SQL DB in multiple ways, which has been further explained individually for a better understanding.

The diagram represents 3 types of SQL backups:

• Full DB backup – F’x’
• Differential backup – D’x’
• Transaction log backup – T’x’

In this diagram, X defines an incrementing number that is associated to a point-in-time when a particular backup was taken. Meanwhile, the prefixed characters (D, F, and T) represent the type of backup procedures whereas; the point-in-time (p) represents the point in time when a backup took place.

For Instance First Full Backup F1 has taken at P1 point-in-time whereas, the second one, i.e. F2 took place at a later point-in-time which is P9. Similarly, rest of the backup procedures has been specified in the same way.

NOTE: In the figure shown above, P13 is a point-in-time that represents a committed-transaction taking place. However, the backup of transaction log hasn’t taken place until P14. Therefore, recovering to point-in-time P13 becomes a little complicated in order to simplify which method 3 of database restoration has been discussed in the segment below.

Simplifying the Restoration of SQL Server Database

Discussed below are top three different methods of database restoration that can be implemented in respective circumstances to fulfill certain restoration needs.

Method 1: Restoring to the Point in Time – P8

Differential backups increase by successive addition, i.e. the last differential backup after a full database backup consists of all the changes taken place in the database. Therefore, only one can be restored once the full database backup restoration.

Method 2: Restore to Point in Time – P10

Just in case the F2 full backup goes missing, it is still possible for an SQL Administrator to perform a restore with the help of F1. Only difference is that a combination of differential and transaction backup will have to be used to reach P10.

A full database backup does not act as a break to the chain of a transaction log backup. Therefore, restoration of transaction logs can be performed for previously taken place full database backups.

The most important factor for having a full database backup is to have the best of Recovery Time Objective (RTO). The representation here only shows a few transact log backups but a lot more are involved in reality. Thus, in such a case, the restoration of an enormous amount of transaction log backup is excessively time consuming and can result in largely affecting the RTO.

Method 3: Restore to Point in Time – P13

In here, we are discussing situations pointing out the restoration of exact point in time. For instance, transactions need to be restored to a point in time P13 however; the log backup is only available at P14.

DBA are not given exact point in times in real-time situations, which is what complicates the entire situation a lot more. In case a bug happens to take place in a code updating the entire table without a WHERE clause, the DBA is told just to simply restore the database to a point right before the update took place without any specifications made about the point in time.

The only way to tackle a situation like this is by using a transaction log backup with a STOPAT clause specified.

NOTE: Performing a point in time recovery is only permitted to transaction log backups.

Therefore, the restore technique to be used here is by utilizing an UNDO file so that you can restore a Server database in STANDBY mode and mention the STOPAT clause.

TIP: The trick is to know that you are allowed to carry out a RESTORE from the very same transaction log DB repeatedly until the desired results are achieved.

Conclusion:

Some of the possible different ways to restore a SQL Server database in predefined conditions have been discussed. These set of methods can be used as tips to handle similar situations for restoring a database in disaster conditions to a previous state without loss. In addition, you can also restore database with NORECOVERY option.