Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

One Line PowerShell Solutions To Common SQL Server DBA Tasks


By:   |   Last Updated: 2014-11-20   |   Comments (5)   |   Related Tips: More > PowerShell

Problem

What are the common DBA tasks suitable for SQL Server PowerShell? How can I start to learn about these tasks in SQL Server?

Solution

These days, PowerShell is no doubt a hot skill for all Microsoft Server product Administrators. To DBAs, there is a special SQL Server module called SQLPS that has been available since SQL Server 2012. We can use it to do many DBA tasks on almost all SQL Server versions (from SQL Server 2005 on). For example, in the DBA community, how to script out a particular object type, such as tables, foreign keys, UDFs, stored procedures, SQL Server Agent Jobs, etc., seems to be a never-ending topic. This is probably because DBAs frequently need to do this task, like during a deployment, we will script out the affected objects as a backup before the deployment changes them.

There are many different ways using T-SQL to generate the creation or deletion script. However the issue with this T-SQL approach is that it is not intuitive and may need quite some time to come up with a complex T-SQL solution using many joins and even cursors.

With SQL Server PowerShell available, it is very intuitive to come up with a solution as simple as one line. The following examples are intended to illustrate such one-line solutions to some common DBA tasks.

All scripts are tested on my laptop which has SQL Server 2012 developer Edition, PowerShell V4 and Window 7 professional. For demo purpose, I will use my local SQL Server instances where possible.

MyLaptop_SQLDB

Quick Introduction of SQLPS

With SQLPS, a SQL Server environment is presented as a folder structure, with the SQL Server instance as root folder in the format of:

SQLServer:\SQL\[ComputerName]\[InstanceName]\

In my local computer case, my computer name is TP_W520, and I have two SQL Server instances, one is the [default] instance and another is [SQL2K8R2] and as such, in SQLPS, I can explore the two sql instances via the following two folders:

  • DIR SQLSERVER:\SQL\TP_W520\DEFAULT\
  • DIR SQLSERVER:\SQL\TP_W520\SQL2K8R2\

Example 1: Scripting SQL Server Database Objects

#first we need to import SQLPS module
import-module SQLPS -DisableNameChecking;

# script out all foreign keys in T-SQL
dir sqlserver:\sql\tp_w520\default\databases\AdventureWorks2012\tables | % {$_.foreignkeys } |  % {$_.script()};

# if we need to save the script to a file, we just add out-file at the end of the code
dir sqlserver:\sql\tp_w520\default\databases\AdventureWorks2012\tables | % {$_.foreignkeys } |  % {$_.script()} | out-file "c:\temp\fk.sql" -force;

# script out all foreign key deletion in T-SQL
dir sqlserver:\sql\tp_w520\default\databases\AdventureWorks2012\tables |% {$_.foreignkeys } |  % {"alter table $($_.parent) drop $_;"};

#script out all stored procedures
dir sqlserver:\sql\tp_w520\SQL2K8R2\databases\AdventureWorks2008R2\StoredProcedures | % {$_.script()+'go'};

#script out views with prefix as 'vEmployee'
dir sqlserver:\sql\tp_w520\SQL2K8R2\databases\AdventureWorks2008R2\Views | ? {$_.name -like 'vEmployee*' } | % {$_.script()+'go'};

#script out all DDL triggers
dir sqlserver:\sql\tp_w520\SQL2K8R2\databases\AdventureWorks2008R2\Triggers | % {$_.script()+'go'};

#script out UDFs
dir sqlserver:\sql\tp_w520\SQL2K8R2\databases\AdventureWorks2008R2\UserDefinedFunctions | % {$_.script()+'go'};

#script out SQL Server Agent Jobs whose name is 'ps test' and save it to a file at c:\temp\job.sql, if the file exist, just append the script to it
dir sqlserver:\sql\tp_w520\SQL2K8R2\jobserver\jobs | ? {$_.name -eq 'ps test'}| % {$_.script()+'go'} | out-file c:\temp\job.sql -append;

Example 2: Collect SQL Server Information

#find the top 10 largest tables (in rows) in a database
dir sqlserver:\sql\tp_w520\default\databases\adventureworks2012\tables | sort rowcount -desc | select name, rowcount -first 10;

#find out logins with sysadmin rights on mulitple servers (assume the default sql instance only)
'ServerA', 'ServerB', 'ServerC' | % { dir "sqlserver:\sql\$_\default\logins" }  | ? {$_.ismember('sysadmin')} | select Parent, Name;

#find user database sizes across multi servers (assuming default sql instances)
'ServerA', 'ServerB', 'ServerC' | % { dir sqlserver:\sql\$_\default\databases } |  select parent, name, size | ogv -Title "Database Size(MB)";

#check whether a specific login name is on which servers (assume default sql instances)
'ServerA', 'ServerB', 'ServerC' | % { dir sqlserver:\sql\$_\default\logins } | ? {$_.name -eq 'specific-loginname'} |  select Parent, Name;

#check whether there is any non-simple recovery database which has not a had a transaction log backup in the last 1 hour
'ServerA', 'ServerB', 'ServerC' | % {dir sqlserver:\sql\$_\default\databases} | ? {($_.RecoveryModel -ne 'Simple') -and ($_.lastlogbackupdate -lt (get-date).addhours(-1))} | select Parent, Name, LastLogbackupdate;

Example 3: More Complex PowerShell Examples for SQL Server

#find out the database user role membership 
dir sqlserver:\sql\tp_w520\default\databases\MyTestDB\users | % -Begin {[email protected]()} -process { $a += New-Object PSObject -property @{User=$_; Role=$_.EnumRoles()} } -end {$a} | select User, Role;

#find the last execution status of all SQL Server Agent Jobs on a SQL Server instance
dir sqlserver:\sql\tp_w520\default\jobserver\jobs | % {$_.enumhistory()} | group  jobname | % {$_.group[0]} | select  Server,  JobName,  RunDate,  Message;

#find the current failed SQL Server Agent Jobs
dir sqlserver:\sql\tp_w520\default\jobserver\jobs | % {$_.enumhistory()} | group  jobname | % {$_.group[0]} | ? {$_.RunStatus -eq 0}  |  select Server, JobName, Rundate, Message;

#get the reason of last server shutdown/reboot (note: the local language should be English - US, otherwise, [Message] will not be displayed. This is a reported bug)
'ServerA', 'ServerB', 'ServerC' | % {Get-WinEvent -ComputerName $_ -filterhashtable @{logname='System'; id=1074; level=4} -MaxEvents 1 } | select Message, TimeCreated | format-list;

#check when the multiple machines were last rebooted
gwmi -class win32_OperatingSystem -Computer ServerA, ServerB, ServerC | select @{label='Server'; e={$_.PSComputerName}}, @{label='LastBootupTime'; e={$_.converttodatetime($_.lastBootupTime)}};

Next Steps

To use the script in this tip, please make sure the necessary changes for [MachineName], [InstanceName], [DatabaseName] and [LoginName] etc. There are many good resources over the internet about PowerShell, but the important thing is to practice it as much as possible, and the best way is to use it in daily DBA work. The following links have some relevant content so we can see the pros and cons of each different solution.

SQL PowerShell shows its strength best in multiple server scenario, it is concise, elegant and intuitive. I especially enjoy using PowerShell script in some complex DBA tasks, such as Always-On HA, replication, mirroring setup and administration, because usually there are multiple servers involved in such tasks. I hope this tip may intrigue you to explore more about what SQLPS can do for you as a SQL Server DBA.



Last Updated: 2014-11-20


get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 12, 2016 - 4:56:49 PM - jbjdba Back To Top

This is great stuff!  Thanks for sharing Jeff.  I've been wanting to use PS more in my administration ( as you point out, it's my best place to learn this technology.) 

 

 


Monday, December 01, 2014 - 2:58:18 PM - jeff_yao Back To Top

@alzdba, I agree with you as I have experienced both environments (ie. windows vs sql server authentications). I guess that is an intended design of SQLPS for using Windows authentication alone. Just think of opening file explorer, there is no other way, like inputting a user id / password to open file explorer to review files / directories.


Monday, December 01, 2014 - 9:29:23 AM - alzdba Back To Top

Great set of one-liners  :-)

The big / HUGE down side of using the SQLSERVER:\ drive is that it relies on Windows authorisations, in stead of the SQLServer authorisations.

Many dba are SQLServer sysadmins, but don't have any authority at Windows level.

They will not be able to "browse" using the SQLSERVER:\ drive, so must rely on other SQLPS or SQLSMO objects to perform alike operations.


Thursday, November 20, 2014 - 1:46:30 PM - jeff_yao Back To Top

Thanks Ranga. 

Yes, PS is really powerful in that it opens another dimension for DBA to look at things.


Thursday, November 20, 2014 - 9:30:07 AM - Ranga Back To Top

Fantastic!! Powershell is great for tasks which are not so easy to get done in TSQL or Mgmt Studio. Thanks for sharing.


Learn more about SQL Server tools