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

DUMP TRAN <Dbname> WITH TRUNCATE_ONLY & MSSQL 2008

To Shrink the database log file I’ve used to,

   1:  

   2: DUMP TRAN <DatabaseName> WITH TRUNCATE_ONLY

   3: GO

   4:  

   5: USE <DatabaseName>

   6: DBCC SHRINKFILE (<DatabaseName>_log, 100);

   7: GO

   8:  

This is not work in SQL 2008, as DUMP is discontinued. Otherwise we have to use BACKUP.

To shrink the log file in SQL 2008,

1. Back up the database in “SIMPLE” recovery mode.
2. then run above line no 6.

Refer this (http://msdn.microsoft.com/en-us/library/ms144262.aspx) for discontinued functionalities in SQL 2008.

RANK() and alternate Sub Query in T-SQL

Consider, we have data table called Orders_Header which hold order header details.

As you can see, PO number(PONo)  ‘0050426995’ is duplicating. Because this table containing PO amendments too. If you notice, DownloadData is in order, meaning first version of PO received on 2010-06-26 17:07:04.910, rest of them subsequently.
The requirement is we want to get following output.

Normally we can write a sub query for this and get the output.

SELECT PONo,

(

SELECT COUNT(PONo)+1 -- to omit zero

FROM ORDERS_Header WHERE PONo=oh.PONo AND DownloadDate< oh.DownloadDate

) AS Revision,

DownloadDate

FROM ORDERS_Header oh

ORDER BY PONO,DownloadDate

By running this query, you can see the following Actual Execution Plan.





From MS-SQL 2005 onwards this can be easily achieved by RANK() function. 

SElECT PONo, 

RANK() OVER(PARTITION BY PONo ORDER BY DownloadDate) AS Revision ,DownloadDate 

FROM dbo.ORDERS_Header

ORDER BY PONO,DownloadDate

Check its Actual Execution Plan and note the difference.

Restoring Deferential backups in SQL Server (T-SQL)

In my previous post I demonstrated, how to restore deferential backups with SQL Management Studio. In this post, I'll show the same thing with T-SQL.

1. Select the Master database.
   USE Master
   GO
2. Restore the latest full back up with REPLACE and RECOVERY option
   RESTORE DATABASE RESTORE_COSTSHEET
   FROM DISK = 'e:\backup_201007282300.bak'
   WITH REPLACE,NORECOVERY;
3. Restore all deferential backups except last one
   RESTORE DATABASE RESTORE_COSTSHEET
   FROM DISK = 'e:\201006110800.incr'
   WITH NORECOVERY;
4. Restoration of Last back up file should be like this.
   RESTORE DATABASE RESTORE_COSTSHEET
   FROM DISK = 'e:\201006111200.incr'
   WITH RECOVERY;

Restoring Deferential backups in SQL Server

Did you have ever restored a SQL data base? If you are a developer probably not. With this post I'm going to show how to restore deferential backups step by step.

1. One important thing, to restore deferential backups, you need to have latest full backup and subsequent deferential backups.
   
   Go to the SQL Server Management Studio, and select "Restore Database..." option by right clicking on Databases node in the Object Explorer. Follow the steps as shown in following screen shot.



Figure 1

2. Select "Options" from the left of the dialog and select the following option.



Figure 2

3. Now, note your data base's status has been changed to "Restoring".



Figure 3

4. Restore the number of subsequent deferential backups, except the last one, only with "RESTORE WITH NORECOVERY" option on.
5. Restore last backup with default values of the option dialog box. That is with "RESTORE WITH RECOVERY" option. And also note the database status has changed back to normal.

Considerations:

• This demonstration was done with SQL Server 2005 with SP2
• Adhere necessary procedures with you company if you are going touch live data base.