Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

MS SQL Server Backups & Restore: Steps explained

, 1 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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. BackupAs you know there are 3 recovery models supported in MS SQL server.Simple Automatically clear the log file. Only allows  recov

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.

SQL Backup life cycle

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).

Crashing Points

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kelum W. Ganegoda
Software Developer (Senior) Brandix Lanka Pvt Ltd.
Sri Lanka Sri Lanka
I’ve started my career in 2001 with Microsoft .net ver 1.0. I’m a MCSD for .net.
 
Currently, I’m working for Sri Lanka’s largest apparel exporter as a Software Engineer. All projects in .net, MS Sql Server, Biztalk Server, WCF and WPF. And also, I’m developing components to the ERP. In addition to that, I’ve involved to create architecture of ERP integration.
Follow on   Twitter

Comments and Discussions

 
Questionsql database recovery Pinmemberdouglasslayton11-Nov-14 23:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 1 Nov 2011
Article Copyright 2011 by Kelum W. Ganegoda
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid