Restoring a database to a point in time

In this recipe, we will use the different backup files we have to restore to a point in time.

Getting ready

In this recipe, we will use the AdventureWorks2008R2 database. You can also substitute this with your preferred database on your development environment.

The AdventureWorks2008R2 database has a single filegroup that contains a single datafile. We will restore this database to another SQL Server instance at a different point in time using three different backup files from three different backup types:

• Full backup

• Differential backup

• Transaction log backup

We can create these three types of backups on the AdventureWorks2008R2 database using PowerShell as illustrated in previous recipes. If you are fairly comfortable with T-SQL, this can also be done with T-SQL backup commands.

To help us verify if our point-in-time restore worked as expected, create a timestamped table before taking any type of backup. Alternatively, create a table and insert a timestamped...