Microsoft SQL Server Backups & Restore: Steps Explained

Yes I know, there are lots of articles and posts about this topic. But I want to show what things are to be known to recover the database to specific point-in-time.

Backup

As you know, there are 3 recovery models supported in Microsoft SQL server.

1. Simple
   • Automatically clear the log file
   • Only allows recover to end of a backup
   • No log backups
2. Full
   • Requires log backups
   • No work is lost, if data (.mdf) file get damaged/lost
   • Can recover to a specific point-in-time
3. Bulk-logged
   • Requires log backups
   • Use minimal logging for bulk operations, thereby reducing the log space
   • Point-in-time recovery is not supported

In a mission critical environment, Full recovery model is the most recommended. In this post, I’m paying attention to this model and simply explain point-in-time recovery.

From your SQL server database, you can take 3 back up types:

1. Full
2. Differential
3. Transaction Log

In Full recovery model, applicability of the above types can be depicted in the following way:

In Figure 1, you can see that Full backup will take all the data from start to the end (let's say T1). On the other hand, Differential backup will take care of data from last successful Full backup (always) to the end. Whereas, Transaction Log back up will incrementally take data in subsequent attempts from where the last successful transaction log was taken to the end of the log. By considering this, you can create a backup strategy for your database. It has to be more serious when you are dealing with large and mission critical database. A little later, I will explain about restoring of database which will help you to design the backup strategy.

Here is the T-SQL statement for:

1. Full DB backup
   SQL

   USE [Master]
   GO
   BACKUP DATABASE [TST] TO DISK=N'C:\TST.bak' WITH NAME=N'TST FULL BAK';
   GO

2. Differential DB backup
   SQL

   USE [Master]
   GO
   BACKUP DATABASE [TST] TO DISK=N'C:\TST-Diff.bak' WITH DIFFERENTIAL, NAME=N'TST DIFF BAK';
   GO

3. Transaction Log backup
   SQL

   USE [Master]
   GO
   BACKUP LOG [TST] TO DISK=N'C:\TST-Log.trn' WITH NAME='TST LOG BACK';
   GO

Recovery

Let's consider two crashing scenarios, which are shown in the following figure:

Scenario 1: Crashing Point 1

Let's assume, data disc crashes on time T3. If you already took Full backup (F1), and Logs backups (L1,L2) you can easily recover the database by restoring F1, L1 & L2 respectively.

USE [MASTER]
RESTORE DATABASE [TST] FROM DISK=N'C:\TST.bak' WITH REPLACE, NORECOVERY;
GO
 
RESTORE LOG [TST] FROM DISK=N'C:\TST-Log.trn' WITH NORECOVERY;
GO
 
RESTORE LOG [TST] FROM DISK=N'C:\TST-Log2.trn' WITH RECOVERY;
GO

Scenario 2: Crashing Point 2

Let Tr be your next backup schedule where to take the L3 and/or D2, in the middle of T3 and Tr, a disaster happens. Still, you can recover the database to-the-point as long as your Log file didn’t corrupt. This option is known as “Tail Log Back up”, which is available from SQL 2005 onwards. The T-SQL statement does this:

USE [Master]
GO
-- Backup the tail of the log, and leave the databsae in restoring mode.
BACKUP LOG [TST] TO DISK=N'C:\TST-TailLog.trn' WITH  NO_TRUNCATE, NORECOVERY;
GO

Then, restore F1, L1, L2 and TailLog backup respectively.

USE [MASTER]
RESTORE DATABASE [TST] FROM DISK=N'C:\TST.bak' WITH REPLACE, NORECOVERY;
GO
 
RESTORE LOG [TST] FROM DISK=N'C:\TST-Log.trn' WITH NORECOVERY;
GO
 
RESTORE LOG [TST] FROM DISK=N'C:\TST-Log2.trn' WITH NORECOVERY;
GO
 
-- The tail log backup
RESTORE LOG [TST] FROM DISK=N'C:\TST-TailLog.trn' WITH RECOVERY;
GO

Hope this will help you a lot.

References

1. http://msdn.microsoft.com/en-us/library/ff848768.aspx (last access on 2011-10-27 5:16PM)
2. SQL Session conducted by Preethiviraj Kulasingham (http://preethiviraj.blogspot.com/) on 2011-10-22 at Brandix Lanka