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!

How to move the Available Storage group in a Cluster

I’m working on a fully automatic silent installation of an SQL 2008 Failover cluster.  This include a lot of testing, believe me Knipogende emoticon After each uninstall, I always had to move the available storage to the correct node to start my installation.  I found it very annoying that this is not possible in the Failover Cluster Manager tool.  As you can see below,when you click on the storage or on a specific disk, there is no option available to move the Available Storage group to another node.

Storage1

Storage2

So the only option that I had was rebooting the server so that the storage group did a failover Verwarde emoticon
It takes a lot of time to reboot so there must be a faster way.

And there is one! Pretty simple in fact…

Just open a command prompt and execute the command below where <Nodename> is the name of the server where you want to move your storage.

cluster group “Available Storage” /MOVE:<Nodename>

Conclusion:The cluster command has more options available than the failover cluster manager. So, if you want to perform a certain action on your cluster and it’s not available in the failover cluster manager, don’t forget to check the cluster command.  Maybe you’ll find the solution there.