In SQL server, there are three main types of backups. Full database backup, differential database backup, and transaction log backup. Although there are many other backup types available but these three are the most commonly used backups. Generally, the backup strategy put in place by most DBAs is Weekly full database backup, daily differential database backup, and frequent log backups. When a disaster strikes, A DBA is required to do maximum possible recovery by leveraging backup strategy in place and sometimes he is required to recover the databases to a certain point in time.
In this lab, we will create similar scenarios. We will assume that the backup strategy in place is Full database backup every Sunday at 01:00 AM, Differential database backup every day (except Sunday) at 01:00 AM and Log backups every 30 minutes. We will simulate disasters and then we will see how to do a recovery to the point of failure and to some specific point in times.
Exercise 1: Recovery to the point of failure
Exercise 2: Point-In-Time Recovery to a specific date/time within the transaction log backup
Exercise 3: Point-In-Time Recovery to a specific transaction mark inserted in the transaction log
Exercise 4: Point-In-Time Recovery to an LSN