Category Archives: SQL

Configuring SQL instance firewall rules via PowerShell

Doing distributed environments setups rather frequently these days I realized that I really don’t want wasting my time setting up SQL Server firewall rules via GUI (I described the process here) and luckily enough Ryan Mangan already created such script. All I had to do is try it (confirm that it works), save it on GitHub and share on my blog for the benefit of wider community:

Original blog post by Ryan/source of this script: PowerShell Script for SQL Firewall rules


PS script to get SQL version from BAK file

Quite unpleasant thing about MS SQL Server database backup files it that you can’t restore them on an older version of SQL Server (it seems that even if you really want to create such BAK file it is not possible), moreover this is valid not only for major versions but also for things like R2, meaning you can’t restore BAK file created in SQL Server 2008 R2 on SQL Server 2008 Server (non-R2).

Just to save my time trying to restore BAK files against wrong versions of SQL Server I created this script which allows you to retrieve SQL version from BAK file headers and compare it with your server SQL version. Specify path to your BAK file and check the output – if BAK file SQL version is newer that your server version then BAK file can’t be restored on this server. Sample script output can be found below:

It tells you that backup was taken on SQL Server 2012 SP3, while you run SQL Server 2012 SP2. Once you install SP3 for SQL Server script output will change to this:

Once two numbers match you are ready for backup restore 🙂


How to: Drop multiple databases via SQL Script (no worries backup/restore is covered too :) )

Recently I did rather a lot of test requiring me to work with non-consolidated K2 DBs. Test included multiple DB restore/delete operations and I realized that I need some script to quickly drop all my K2 DBs and start from scratch. Here is this script:

Script selects every database prefixes with “K2” and you just need to copy its output into new query window and execute.

And in case you tend to backup things before you delete them, similar script for backup:

And for restore you can use the script below. Unfortunately it uses hard coded file paths but assuming your back up files have default DB names (and for example were created by the script above) you can get away with minimum find and replace adjustments (path to backup files and your SQL instance data directories may need to be adjusted). Here is the script for restore:


SQL Server: How to attach all databases from specific folder

I recently had to change SQL Server instance collation using the steps I described in my earlier post. After this operation dozen of databases which were hosted on that instance become detached and I was able to see only set of instance’s system DBs. So I had to look for an option to attach all DB from folder via script as doing this manually for 20 or so databases a bit too much of an effort to me 🙂

There is “Attach all the databases in one folder” script available in TechNet Script Center which was able to solve this problem for me. All you need it pass DatabaseDir parameter to the function specifying folder with your DBs:

Just in case I provide PS code of this function below in case you don’t want to grab the same from TechNet Script Center. All credit goes to Ivan Josipovic.


Changing SQL collation for deployed instance without reinstall (almost)

Collation matters. Period. If you not sure just wait till you get an incident or issue which will teach you that it is. Some apps require you to have specific collation for their back end DB. For example for K2 you need to have  Latin1_General_CI_AS collation (see my earlier post on that _ ).

Nonetheless it can often be the case that your RTFM/Prerequisites_Check moment (I wonder if I can copyright/trademark this term? I heard that Michael Buffer trademarked his “let’s get ready to rumble” phrase _ ) happens after SQL server instance has been already provisioned. For newly created (clean install) K2 database you just need to delete database which was created on instance with wrong collation and allow K2 Setup Manager to create it again after you deal with your instance collation. There is an MSDN article detailing that _ but I will outline the procedure below.

Aforementioned MSDN article says that you have to have installation media to perform this operation, but in fact you can get away with Setup Bootstrap folder which should present on your server in location similar to one below (may vary from version to version, see details _ )

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014

So steps are the following:

1. Locate setup bootstrap folder for your SQL server installation, open CMD or PS window (don’t forget “dot net sourcing” thing for the latter):

2. Execute the following command:

Once this operation completes  master, model, msdb, and tempdb system databases are rebuilt (dropped and re-created) with collation you specified in the command. Obviously all your modifications to these databases are lost (if you made any) and essentially it is sort of reinstall/repair type of operation just much quicker and without need to wade through SQL Setup GUI.


SQL Server: Giving user role membership on specific DB

Just a really quick note on how to manage RBA in SQL Server not relying on SQL Server Management GUI, as sometimes it is far quicker to execute a few lines, for example to create DB and then grant dbowner role membership, for let’s say you application service account so that your app can use newly created DB. So SQL code is the following:

NOTE: In real world/production environments you should not be so generous with granting dbowner role to each and every user, event to app service account. See some expanations for example here: 5 Reasons Against Allowing db_owner Role Permissions.


Free resources for SQL Server MCSA and MCSE Exam preparation

Just sharing number of links to some MVA resources which can be helpful for Microsoft SQL certification preparation and learning Microsoft SQL Server in general.


Don’t ask me why is the picture above has any relevance to this blog post 🙂

Anyway, the following courses cover SQL Server 2012, along with performance tuning and high availability features of SQL Server 2014:

Developing Microsoft SQL Server Databases (prep for Exam 464)Designing Database Solutions for SQL Server (prep for Exam 465)Implementing Data Models & Reports with Microsoft SQL Server (prep for Exam 466)Designing BI Solutions with Microsoft SQL Server (prep for Exam 467)

And next, these courses complement the existing online exam prep resources for MCSA: SQL Server 2012 exams:

Querying Microsoft SQL Server 2012 (prep for Exam 461)Administering Microsoft SQL Server 2012 Databases (prep for Exam 462)Implementing a Data Warehouse with Microsoft SQL Server 2012 (prep for Exam 463)

Source – Born to Learn Blog


How to get listing of stored procedures for SQL server database

Just a quick note on how to get listing of stored procedures for SQL server database. Following query will do this for you (as long as you’re not in the master database, system stored procedures won’t be returned):


SELECT *\nFROM DatabaseName.information_schema.routinesWHERE routine_type = ‘PROCEDURE’

You may add AND specific_name LIKE ‘%part_of_specific_name%’ to WHERE clause to filter/look up for specific stored procedure by name.

If for some reason you had non-system stored procedures in the master database, you could use the query (this will filter out MOST system stored procedures):


SELECT *\nFROM master.information_schema.routinesWHERE routine_type = ‘PROCEDURE’\nAND Left(Routine_Name, 3) NOT IN (‘sp_’, ‘xp_’, ‘ms_’)

\nSource – question on Query that returns list of all Stored Procedures in an MS SQL database.


How to check Microsoft SQL Server version

Sometimes you need to verify exact version of Microsoft SQL Server and you can do it in a different ways. GUI way of doing this is to access Help > About in Microsoft SQL Server Management Studio which will give you something like this:


Well this is good, but can you readily translate 11.0.5058.0 into SQL Server XXXX SPX? Maybe you can, but I don’t. So the better way is to employ some SQL commands to get this information in more readable form:

Will return you something like this (but in online):

Microsoft SQL Server 2012 – 11.0.5058.0 (X64)

May 14 2014 18:34:29

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Well at least it does show you that 11.0.5058.0 is SQL Server 2012, but doesn’t disclose what SP level you have… Then next command (works starting from SQL Server 2000) will help you:

This returns following details (build, SP level, edition):

11.0.5058.0 SP2 Enterprise Edition (64-bit)

But other frequent scenario if when you don’t have access to the server and have only build number provided to you, here you may refer to the following comprehensive list of SQL Server builds resolved to product name and SP/update level (pay attention that below on that page you may find the links for the similar data on Exchange and SharePoint though these two list don’t look as impressive as the list of SQL Server versions):

This should be enough to establish exact SQL version.

As a bonus there are couple of alternative way of checking SQL server version information. PowerShell way (almost).

1) Using Invoke-sqlcmd, strictly speaking is just use of old good sqlcmd but inside of PowerShell – so this is not a pure PowerShell method, bur rather an example of how to execute SQL query in PowerShell:

It should look similar to this (and the build on the screenshot is SQL Server 2014 SP1):

Check SQL Server version PS

2) Reading relevant registry keys with PowerShell (this is a bit cooler as it enumerates your instances):

I took this script from Shawn Melton‘s answer to “How do I check for the SQL Server Version using Powershell?” question on

Here is an output from my test server with two instances:

Check SQL Server version PS option 2 output


SQL: Get next run time for SQL server jobs

SQL Server Manager GUI doesn’t show you next run time when you look at job schedule (at least in SQL Server 2008 R2) so you may use this to get it:

\n\n\n[code lang=”sql”]\nSUBSTRING(CONVERT(VARCHAR(10),CASEWHENSUBSTRING(CONVERT(VARCHAR(10),next_run_time), 1 ,2)> 12\n\nTHENSUBSTRING(CONVERT(VARCHAR(10),next_run_time),1,2)-12\n\nELSESUBSTRING(CONVERT(VARCHAR(10),next_run_time),1,2)END),1,2)\n\n+‘:’+SUBSTRING(CONVERT(VARCHAR(10), next_run_time),3,2)\n\n+‘:’+SUBSTRING(CONVERT(VARCHAR(10), next_run_time ),5,2)‘Scheduled At’\n\nFROM sysjobs A ,CTE B\n\nWHERE A.job_id = B.job_id\n\nANDSUBSTRING(CONVERT(VARCHAR(10),next_run_date), 5,2)+‘/’+\n\nSUBSTRING(CONVERT(VARCHAR(10),next_run_date), 7,2)+‘/’+\n\nSUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4)=CONVERT(VARCHAR(10),GETDATE(),101)\n\nAND(SUBSTRING(CONVERT(VARCHAR(10),\n\nCASEWHENSUBSTRING(CONVERT(VARCHAR(10),next_run_time), 1 ,2)> 12\n\nTHENSUBSTRING(CONVERT(VARCHAR(10),next_run_time), 1 ,2)-12\n\nELSESUBSTRING(CONVERT(VARCHAR(10),next_run_time), 1 ,2)END),1,2)\n\n+‘:’+SUBSTRING(CONVERT(VARCHAR(10), next_run_time ),3,2)\n\n+‘:’+SUBSTRING(CONVERT(VARCHAR(10), next_run_time ),5,2))>\n\nSUBSTRING(CONVERT(VARCHAR(30),GETDATE(),9),13,7)\n[/code]\n\n