Jul 26, 2010

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.
  2. Figure 1
  3. Select "Options" from the left of the dialog and select the following option.
  4. Figure 2
  5. Now, note your data base's status has been changed to "Restoring".
  6. Figure 3
  7. Restore the number of subsequent deferential backups, except the last one, only with "RESTORE WITH NORECOVERY" option on.
  8. 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.

No comments: