Download Microsoft SQL Server 2012 RC0

OK, this is definitely not what I have been expecting around SQL Server 2012. But as usual, Microsoft managed to surprise their users (along with the news around certification, editions, etc. lately) with the release for download of the RC0.

Download Microsoft SQL Server 2012 RC0

The download process requires registration but I believe that should not be a show stopper Smile

The news around SQL Server 2012 are so much and all so big and “life changing” that I would not like to spam you with them. I will just let you read about them yourself Smile

What is new in Microsoft SQL Server 2012 – information on what is new in this version

SQL Server 2012 Developer Training Kit – training kit preview where you can explore new features, technologies and innovations in All Mighty SQL 2012.

SQL Server 2012 Licensing FAQ – interesting questions and answers on the subject of licensing and licensing changes in SQL Server 2012!



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


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


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.


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).


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”.


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

And finally – the Options tab:


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

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

After the release of SQL Server Code name “Denali” CTP3 I’ve been playing a lot with it and one of the things that stroke me was the new amazing features concerning database restores using SQL Server Management Studio. You are aware that the old SSMS was capable of doing a ton of stuff but there was also another ton of stuff that it was just not able to do (or suggest you to do). So let me begin with my short introduction to what I have found so far:

Point one is the ability to restore PAGE from the GUI. So far PAGE restores were only for those who can script Smile. Well now we have pretty neat screen where we can do it just with some lame clicking Open-mouthed smile


I used one of the corrupted database samples of Paul Randal (blog | twitter) which has a corrupted page (marked as suspect after a DBCC CHECKDB run). the PAGE restore window is a bit rough but for emergencies it will do the job:


After choosing the Database, SQL Server automatically extracts the info from table msdb.dbo.suspect_pages and populates the Pages window. You can enter additional pages if you want to using Add button. The tail-log backup is something new that is introduced in the GUI (you will see it and in database restore part) and this section is where you put the filename of the tail-log backup which is going to be performed during the restore. The bottom part is the Backup sets where you can see the last backups available.

In my next post I will try to show you the new stuff around Database restores so stay tuned! Smile

SQL Server Code Name “Denali” CTP3 available for download

OK, I have been waiting for the next CTP release of SQL Server Code Name “Denali” for a bit now and as far as I was able to follow the news I would have expected to see CTP2 instead of CTP3. However, two days ago Microsoft release to the public CTP3 which can be downloaded here (the link is valid as of July 2011).

According to the official communication the CTP3 includes huge number of new capabilities and a lot more functionalities that users can see and test.

Product highlights for this CTP include:

  • Greater availability. Deliver the required 9s and data protection you need with AlwaysOn which delivers added functionality over CTP1.
  • Blazing-fast performance. Get unprecedented performance gains with Project “Apollo,” a new column store index that offers 10-100x star join or similar query performance improvements available for the first customer preview.
    Breakthrough insight. Unlock new insights with rapid data exploration across your organization with Project “Crescent,” available to customers for the first time. Get your hands on the data exploration and visualization tool that everyone is talking about and enables end users to explore data at the speed of thought.
  • Credible, consistent data. Provide a consistent view across heterogeneous data sources with the BI Semantic Model (BISM) a single model for Business Intelligence applications, from reporting and analysis to dashboards and scorecards. Make data quality a part of everyday life with the Master Data Services add-in for Excel and new Data Quality Services that is integrated with third party data providers through Windows Azure Marketplace. Customers can test this functionality for the first time and put data management and cleansing tools in the hands of those who need it.  Available integrated Marketplace providers include:
    • Cdyne: CDYNE Phone Verification will validate the first 7 digits of your phone number(s) and return what carrier the phone number id is assigned to, whether it is a cellular number or a land line, the telco, and additional information including time zone, area code and email address if it is a cellular number
    • Digital Trowel: Powerlinx allows users to send data for refinement and enhancement and receive back cleansed and enriched data. The database contains 10 million company website addresses, 25 million detailed company profiles, and 25 million executives, including 5 million in-depth profiles with email addresses and phone numbers. 
    • Loqate: The Loqate Verify enables users to parse, standardize, verify, cleanse, transliterate, and format address data for 240+ world countries.
    • Loqate: The Geocode enables a latitude-longitude coordinate to be added to any world address with worldwide coverage to city or postal code for over 120 countries
    • Melissa Data: WebSmart Address Check parses, standardizes, corrects and enriches U.S. and Canadian addresses to increase deliverables, reduce wasted postage and printing, and enhance response
  • Productive development experience. Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Developer Tools code name “Juneau” for a unified and modern development experience across database, BI, and cloud functions. Additionally, Express customers can test a new LocalDB version for fast, zero-configuration installation.

Today, you have the chance to preview and test these marquee capabilities and the upgrade or migration experience. CTP3 is a production quality release that includes access to upgrade and migration tools like Upgrade Advisor, Distributed Replay and SQL Server Migration Assistant (SSMA). Upgrade Advisor and Distributed Replay allow you to perform thorough analysis and testing of your current SQL Server applications before upgrading so you know what to expect. You can also use SSMA to automate migrate non-SQL Server databases to SQL Server Code Name “Denali”. For more detailed information about the new SQL Server Migration Assistant v5.1, visit the SSMA team blog.

You can download and test yourself. I hope you enjoy it! Smile

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

SQL Server CMS – where are locally registered servers stored

In one of my previous posts I showed you how to register multiple instances in SQL Server Management Studio Registered Servers explorer. However registering them locally has its pitfalls such as not being accessible for other DBAs without being imported/exported from the GUI. This operation is not that complicated itself, but it got me curious where are those registered instances stored after all so I do not have to export and import anything. It took me a bit longer to find it, but I did it. So, if you are curious too, those instances are stored in an XML file stored on the following paths:

Windows Server 2003 based systems: C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSvr.xml

Windows Server 2008 based systems: C:\Users\<user>\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSvr.xml

The XML file is structured just as the one, created after exporting registered servers from the GUI. So you can just copy it and restore it wherever you desire.

SQL 11 HADRON Learning Series – part 4

CSS SQL Escalation Services are continuing their learning series on SQL Server 11 HADRON. In their latest article they write about page level repair and its enhancements in the latest release. It also shows and some new features of the SSMS GUI introducing the “Resource Assistant” and the colorful timeline thingy Smile

The article can be read here. Enjoy! Winking smile

Step-by-step setup of Always ON (HADR ON) feature in SQL 11 (video)

Following is a video of a step-by-step setup of SQL Server 11 AlwaysOn availability group feature from Gopal Ashoc (Product manager for SQL Server High Availability). The video demonstrates both enabling and configuring the Failover cluster and configuring SQL Server HADR ON with adding databases to the availability group.

You can watch the video on Youtube here.