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.
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
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.
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.
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.
Now press OK again, to start your restore. A restore plan is automatically generated and your database is restored till the requested time.
How cool is that!
To perform a page restore,I first need to have a corrupt database 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.
Let’s fix this database!
Right click on your DB, select Tasks – Restore – Page
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.
Just click on the OK button to start the page restore
When I check my database again with DBCC CHECKDB I see that the damaged page has been fixed
I think those 2 new features will make the life of the DBA just a little bit easier.