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.