SQL Server Backup Types

Full

Full backups in SQL Server take a complete picture of the entire database.  To restore the entire database all you need is just the full backup.

Differential

Differential backups are a copy of all the changes to the database since the last full backup.  To restore a differential, you must first restore the full backup, then apply a single differential backup.

Transaction Log

Transaction log backups contain a backup of all of the changes since the last transaction log backup.  In order to restore a database to a point in time, transaction logs are usually your best option, but the do require more work.

The way to get a database restore of a transaction log is to do the following:

1.  Apply the last full backup prior to the point in time.

2. If you are using differential backups, apply the last differential backup prior to the point in time.

3.  Apply all the transaction logs in the right order from the last full (or last differential if using differential backups).

Here is an example of a backup restore script created with the Database Health Reports backup advisor:

use [master]; ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE AdventureWorks2012    FROM DISK = 'C:\Backups\AdventureWorks2012_09_03_010003_9840657.bak'    WITH REPLACE, NORECOVERY; RESTORE DATABASE AdventureWorks2012    FROM DISK = 'C:\Backups\AdventureWorks2012_09_03_123006_2581403.dif'    WITH NORECOVERY; RESTORE DATABASE AdventureWorks2012    FROM DISK = 'C:\Backups\AdventureWorks2012_09_03_160006_7308461.trn'    WITH NORECOVERY; RESTORE DATABASE AdventureWorks2012    FROM DISK = 'C:\Backups\AdventureWorks2012_09_03_200003_0942710.trn'    WITH NORECOVERY; RESTORE DATABASE AdventureWorks2012 WITH RECOVERY; ALTER DATABASE AdventureWorks2012 SET MULTI_USER;