What’s new in SQL Server “Denali”–database restores using SSMS–part 2

In my previous post I shortly introduced you some of the new cool possibilities of SQL Server Management Studio in “Denali”. Now I would like to continue with this and show you a bit more about the database restore part (last time I just showed you how to restore a page). First lets open the Database restore GUI

image

The windows that opens is quite similar to the one we’re so used to but with several differences:

image

In this dialog box I chose to restore database TestBR to the last backup taken. This database is set to Full recovery with Full DB backup on every hour, Diff on every 15 minutes and TLog backup on every 5 minutes (this is for tests sake, do not think that this is my standard approach for implementing a backup strategy Open-mouthed smile)

So, I want to restore my database to the last point possible. Till now that usually means that we have to take manually tail-log backup and then proceed with manual (again) restoring either using the GUI or script writing. Well…in Denali this is done automatically for us. We just have to choose the point to which we would like to recover. Based on that you have a Restore plan created where you can see in very user friendly table what are you going to restore, from what source, etc. In addition to that, on the top of that same window you have a notification message which says that a tail-log backup will be taken of the source database. Well there it is! – you have it all automated, you just need to worry about the availability of the restore media and its integrity Smile

There is one more cool feature on this screen – the Timeline thingy Smile It is used of you would like to restore not to the last backup taken but to a specific point in time in the past.

image

In my case I ‘d like to restore the database to couple of minutes back. After I hit the Timeline button I automatically get this colorful timeline pointing out where I have Full backup taken, where is my Diff and what exactly history I have covered by TLog backups. So my restore point is the red line and it is after my last TLog backup taken. SSMS automatically adds a tail-log backup (check the message on the top of the screen).

image 

This backup is then immediately added to the restore plan. Amazing! Besides that almost everything is done for you (and you do not have to remember to take that tail-log backup), you get and a pretty good visualization of what you want exactly to do and see what your backup strategy is covering.

Once we setup the restore plan, there are couple of other options we have to take care about. On the files tab we can choose where our database files will physically be restored to. You can change the physical path for each file individually or you can do it as “mass” operation by checking the “Relocate all files to folder”.

image

This is extremely useful…especially when you are doing database refreshes.

And finally – the Options tab:

image

Here we also have quite a lot enhancements. The Recovery state is switched to a combo box leaving space for the rest of the stuff( you remember how it is in pre-Denali SSMS – all three options with their simple explanation placed on the dialogue box taking up almost the whole space).  After choosing the recovery state, you have one more new cool option introduced – the “Close existing connections” Options. This is THE THING! Smile I have quite a lot experience with trying to get rid of all users using this database. And besides scripting the restore with adding some ALTER DATABASE <> SET SINGLE_USER it is quite difficult to perform the restore through the GUI. Well now with this checkbox everything seems easier. Besides – this checkbox is doing exactly as we are used to do it manually – before the restore statements it adds a ALTER DATABASE <> SET SINGLE_USER WITH ROLLBACK IMMEDIATE and after the whole restore is completed – ALTER DATABASE <> SET MULTI_USER. Cool, huh? Smile

Well that is pretty much it! A lot of new stuff easing the DBAs life especially the ones that are just loving the GUI and hate scripting Devil

Advertisements

How to check who performed a SQL Server database backup

In this post I would like to share a simple way of extracting latest database backup including the user that has initiated it. Generally SQL Server engine does not store such info unless you have some custom piece of code (such as triggers) or you are using auditing in SQL Server 2008 and above. As you know each backup triggers an event that is stored in the application log of the operating system. This event includes the message that is written in the SQL Server error log and additionally has an user name, that triggered the event. Based on that and using PowerShell here is a simple list that returns newest 2000 backup events (full, differential or log) for all databases, sorting them descending by time. Columns returned are Time (the time the backup completed), Username (the user performed the backup) and Message(the message, which includes the type of backup and the database).

Get-EventLog "Application" -newest 2000 | Where-Object {$_.EventID -eq 18264 -or $_.EventID -eq 18265 -or $_.EventID -eq 18270}  |sort -descending TimeWritten |
Format-Table TimeWritten,UserName,Message -auto

As you can see I am querying directly the OS Application log and filtering three specific events:

  • EventID 18264 – Database full backup event
  • EventID 18265 – Database log backup event
  • EventID18270 – Database differential backup event

Also keep in mind that Backup and Restore events are not written to SQL Server Error log and NT Application event log if trace flag 3226 is enabled. So before trying to find out who performed a certain backup, check if those events are logged at first placeSmile