MS 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 are things to be known to recover the database to specific point-in-time.

Backup

As you know there are 3 recovery models supported in MS 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 above types can be depicted in following way.

Figure 1

You can see in the Figure 1, Full backup will take all the data from start to the end(let 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 takes 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. In a little later, I will explaining about restoring of database which will helps you to design the backup strategy.

Here is the T-SQL statements for

1. Full DB backup
   

   USE [Master]

   GO

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

   GO

2. Differential DB backup
   
   
   
   

   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
   
   
   
   

   USE [Master]

   GO

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

   GO

Recovery

Lets consider two crashing scenarios, which are shown in following figure(Figure 2).

Figure 2

Scenario 1: Crashing point 1

Lets 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 is your next backup schedule where to take the L3 and/or D2, in the middle of T3 and Tr a disaster happen. 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 to do 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 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.