One Line PowerShell Solutions To Common SQL Server DBA Tasks

By:   |   Comments (5)   |   Related: > PowerShell


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


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.


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:


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:


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 | ? {$ -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 | ? {$ -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 } | ? {$ -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 {$a=@()} -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 | % {$[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 | % {$[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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

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 1, 2014 - 2:58:18 PM - jeff_yao Back To Top (35450)

@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 1, 2014 - 9:29:23 AM - alzdba Back To Top (35442)

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 (35356)

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 (35353)

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

get free sql tips
agree to terms