Click here to Skip to main content
13,046,281 members (85,258 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

8K views
2 bookmarked
Posted 1 Nov 2011

Microsoft SQL Server Backups & Restore: Steps Explained

, 1 Nov 2011
Rate this:
Please Sign up or sign in to vote.
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:

SQL Backup life cycle

Figure 1

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

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

Crashing Points

Figure 2

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

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.

You may also be interested in...

Pro

Comments and Discussions

 
Questionsql database recovery Pin
douglasslayton11-Nov-14 22:55
memberdouglasslayton11-Nov-14 22:55 

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

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

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