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.
BackupAs you know there are 3 recovery models supported in MS SQL server.
- Automatically clear the log file.
- Only allows recover to end of a backup.
- No log backups
- Requires log backups
- No work is lost, if data (.mdf) file get damaged/lost
- Can recover to a specific point-in-time.
- 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.
- Transaction Log
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
- Full DB backup
- Differential DB backup
- Transaction Log backup
Lets consider two crashing scenarios, which are shown in following figure(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.
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,
Then restore F1,L1,L2 and TailLog backup respectively.
Hope this help you a lot.
- http://msdn.microsoft.com/en-us/library/ff848768.aspx (last access on 2011-10-27 5:16PM)
- SQL Session conducted by Preethiviraj Kulasingham(http://preethiviraj.blogspot.com/) on 2011-10-22 at Brandix Lanka.