Restoring a Database-SQL Server DENALI SSMS

Today i was playing with SSMS in Denali and found few Very Very Interesting enhancements with Restore Task. Let me show you what i’m saying….

Let’s play with Adventureworks Database for demo purpose. FYI I’ve a folder on my D$ where i placed all my Full, Diff and T-Log backups for this database.
First, let’s see what SQL Server 2008R2SP1 SSMS offers us when we try to do a point in restore on the same instance.
In SSMS go to TASKS and Restore and Database as shown below.

By default it chooses from Database where it automatically picks the Backups(Full/Diff and Log) accordingly what it needs to perform a Restore to most recent possible point in time as shown below.

Perfect, this is awesome….But, what if we choose from device, where we’ve to select our Backup files manually..Let’s see what 2008R2SP1 offers to us.

Ummm…Basically your are missing an option where you can select an entire folder and It automatically picks the required individual backup files as per the LSN’s…..So, we’ve to pick all the individual backup files required manually.

Let’s see Point In Time Restores(Recovery) for SQL 2008R2 SP1:
I’m choosing from Database for ease in demo purposes. I’ve chosen 11.00 AM in the first scenario, and it basically chooses the required as shown below.

This is So Nice…..But again if you’ve to choose from device, you’ve to point to individual Back up files manually(Which is Okay Most of the times, but irritating in few cases..It all depends).

Now, Let’s see what DENALI SSMS can do for us in Restore operations
At the very first glance…..You can see, Now we can do PAGE Restores using SSMS as shown below. Upto 2008R2 we’ve to use T-SQL Commands to Restore a single page.

Note: I was creating a Maintenance Plan for Backups and I found one interesting enhancement where we can select “which account” do you want to run this Plan under( As per the below Image).

In SQL Server 2008R2, we didn’t had this option. We’ve to go to Agent Jobs and change the Job owner and Execution Context. One more interesting thing to note.

Okay..Let’s actually do the Point in time restore from SSMS in Denali. I RC’d on the database which i want to perform a Restore ->Tasks restore -> Database. Let’s see what exactly DENALI is doing for us in this process.

See the highlighted areas in the above screenshot. Greatest feature of all is…..Now DENALI is smart enough to take a Tail Log Backup automatically, which should be applied as a the very last backup in our restore sequence. Huhuuuuuuuuuuuu…..I love it..Simply Superb!!We also have a Button at the bottom right to verify our Backup Media:)

Also, observe in the above screenshot I’ve chosen the source as Database. Let’s see what it has if we choose Device.

Now we can choose a folder which has all our Backup files:) Once you click OK..actually it is automatically choosing only the required backup files and ignoring the rest, which greatly saves our time.

Let’s see what Timeline option offers to us:

Oh My God…I can’t express how impressed I’m with this little graphic which is pretty self explanatory in all terms. Kudos to Microsoft!! Great Job guys. Lot better than few Third Party tools our there which are available to us!

Try Changing the timelines and you can see the above graphic changes accordingly!

Okay! Now let’s go to Files Tab and see how it appears.( As of now, in SQL Server 2008R2 SSMS there will be no FILES tab when you are performing a Restore Operation.(Actually we’ve to define everything in OPTIONS tab)

SSMS DENALI SSMS SQL 2008R2

So, if we want to change the Physical Location of Files or change logical file names, they offered us a separate tab, basically saying that all the file operations are done under FILE tab, which makes more sense to me!

Let’s go Options tab and see what it has for us.

In the Tail log backup Section it’s giving us where it is storing our tail log backup file. You can change it by clicking ellipses button.

Real Cool thing – It has now an option to close all the existing connections before actual restore starts…yeyyyyyy!! Also, an option to Prompt you before restoring each backup file(In this case, we’ve multiple files).

This is no less than any Third Party tool out there currently in the market!!(I’ve to agree that there’s no encryption offered while backing up and no network resilience features, But still we got all the cool enhancements for Freeee…..Anyways we’ve compression feature from SQL Server 2008). Over all I’m very impressed and glad to share this with our SQL DBA community.

Happy testing Denali:)