Tag 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


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.


SQL Script: List all tables in DB with their row counts

Often I need to test something and use SQL database table of particular size for my test, and sometimes it is easier to pick up some table of appropriate size (at least in terms of rows number) from what you already have. This is the case when this SQL script may come in handy:


How to: quickly check SQL server collation settings

Just a quick note on how to quickly check Microsoft SQL server collation without wading through SQL Server Management Studio GUI. Go to CMD and execute the following:

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "SELECT CONVERT (varchar, SERVERPROPERTY(‘collation’));"\n[/code]\n\n

Alternatively you may use sp_helpsort stored procedure, though when run via sqlcmd it gives a bit strange output if run with sqlcmd

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "EXECUTE sp_helpsort;"\n[/code]\n\n

On the boxes with SQL server installed path to sqlcmd executable added to PATH variable so you can fire it off without specifying full path. I guess you may also check corresponding key in registry which should exist there I believe (at least for server collation) 🙂

Also you may view collation settings on databases:

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "SELECT name, collation_name FROM sys.databases;"\n[/code]\n\n

Reference: MSDN: View Collation Information


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 stackowerflow.com: Query that returns list of all Stored Procedures in an MS SQL database.


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

Source: http://blog.sqlauthority.com/2008/12/22/sql-server-find-next-running-time-of-scheduled-job-using-t-sql/