Restore Database Enhancements in SQL 2012

In SQL 2012, Microsoft has introduced some nice restore database enhancements.
The major enhancements are:

  • Point-in-time restore has now a visual timeline that allows you to quickly select the target time and perform your restore.
  • Page Restore worked already in SQL 2008 (R2) and SQL 2005 but it has now a nice user interface. It allows you to check your database for corrupt pages and restore them from a good backup file.

In this blog, I’ll give you an overview how to use these 2 new features.

Point-in-time restore

In the Object Browser of your SSMS, right click on Databases and select “Restore Database”
In this example, I will perform a restore of the Adventureworks2008R2 database.  I selected Device to get my backup files.  Just press the […] button

Restore1

In the locate backup file window, I select all the backup files (Full backups and Transaction Logs) that have been made.  To create the backups, I just created a simple Maintenance Plan.
Click on OK.

Restore2

Now all the backup sets are in the list (this is not new…).  As you can see, there is a new button called “Timeline”.  Click on it to open the timeline interface.

Restore3

Now, you can choose to restore to the last backup taken or choose a specific date and time.  With the timeline, you can scroll to the restore time that you want. On the timeline you can also see what types of backups will be used to perform the restore. Once you selected the correct time, just press the OK button.

Restore4

Now press OK again, to start your restore. A restore plan is automatically generated and your database is restored till the requested time.

Restore5

Restore6

How cool is that!

Page Restore

To perform a page restore,I first need to have a corrupt database Knipogende emoticon and you also need to have a GOOD backup file,which means, without the corrupt page.
As you can see below, I did a DBCC checkdb and my database is indeed corrupt.

RestorePage1

Let’s fix this database!
Right click on your DB, select Tasks – Restore – Page

RestorePage2

In the Restore Page window, the database is selected and the Pages grid is automatically showing the  damaged pages. You can also run DBCC CHECKDB, by clicking on the button “Check Database Pages”, to find out if there are more damaged pages in the database. You also need to set the location for the Tail-Log backup file. The Backup sets grid shows you all the backups that can be used to fix your pages.

RestorePage4

RestorePage3

Just click on the OK button to start the page restore

RestorePage5

When I check my database again with DBCC CHECKDB I see that the damaged page has been fixed Emoticon met brede lach

RestorePage6

I think those 2 new features will make the life of the DBA just a little bit easier.

Have fun!

Leave a Reply

Your email address will not be published. Required fields are marked *