Press "Enter" to skip to content

Category: Powershell

SQL Server 2014 SMO And TruncateData

Published 2016-09-22 by Kevin Feasel

Max Trinidad finds a version inconsistency in SMO:

Hum! I just found out that in SQL Server 2014 (SP2 installed), while migrating from SQL Server 2005, one of my PowerShell script (I’ve been using for a long time) that uses SMO to truncate tables. But, when running it against a SQL Server 2014 database, I’m getting an error:

“..this property is not available on SQL Server 2014.”

For mi surprise, I ran the same PowerShell script against SQL Server 2016 and it works fine.

That seems rather odd.  If this affects you, vote up his UserVoice item.

Comments closed

Azure Automation

Published 2016-09-19 by Kevin Feasel

Melissa Coates explains Azure Automation:

Azure Automation is a cloud service in Microsoft Azure which let you schedule execution of PowerShell cmdlets and PowerShell workflows. Azure Automation uses the concept of runbooks to execute a set of repeatable, repetitive tasks via PowerShell. Consistency in execution, reduction of errors, and of course saving time, are all key objectives – which makes DBAs and system admins happy, eh?

This is a higher-level discussion including some good tips on the product.

Comments closed

Automating DMV Scripts

Published 2016-09-19 by Kevin Feasel

Sander Stad has a Powershell script to automate using Glenn Berry’s excellent DMV queries:

I’ve used Glenn’s DMV scripts for years but always found them tedious to execute because there are about 70 individual scripts that either query instance or retrieve database information. Glenn did make it easier for you by creating Excel templates to save the information in.
There are separate scripts for each version of SQL Server that updated every month. Glenn only updates the versions for 2012 to 2016 with new features. The scripts are very well documented and even contain hints about how to solve some issues.

Click through for more information on how to install this Powershell module.

Comments closed

Migrating To Azure SQL Database

Published 2016-09-16 by Kevin Feasel

Mike Fal discusses BACPACs, DACPACs, and migrating on-prem databases to Azure SQL Database:

SQL Server Data Tools(SSDT) have always had a process to extract your database. There are two types of extracts you can perform:

  • DACPAC – A binary file that contains the logical database schema and possibly the data. This file retains the platform version of the database (i.e. 2012, 2014, 2016).

  • BACPAC – A binary file that contains the logical database schema and the data as insert statements. This stores the platform version, but is not locked into it.

Mike also walks through SqlPackage.exe.

Comments closed

Create An Azure SQL Database Instance From Powershell

Published 2016-09-15 by Kevin Feasel

Arun Sirpal walks through the steps of setting up an Azure SQL Database instance and database using Powershell:

What I have done here is hard-code three parameters ( database edition, start IP address and end IP address) which for my situation won’t change but I have given the ability to pass in the environment name, SQL Server name and database name.

So a prompt will be presented to the user – here you should enter the relevant details and click enter.

It’s not that difficult to do, and the scripts themselves are probably faster than fumbling around the UI.

Comments closed

Administrative Scripts

Published 2016-09-13 by Kevin Feasel

Slava Murygin has ten Powershell scripts to help administer a SQL Server instance:

Script #5. Read SQL Server Error Log file.

That is extremely important troubleshooting script. When you start/restart the SQL Server service and it does not come up, you can run this script to see what was going on during the SQL Server startup and what was the problem (just note that value of “$SQLInstancePath” must be pre-set by previous script):

Click through for all of the scripts.

Comments closed

SQL Agent Job Durations As TimeSpans

Published 2016-09-13 by Kevin Feasel

Rob Sewell shows us how to convert the SQL Agent job duration into a .NET TimeSpan using Powershell:

The first job took 15 hours 41 minutes  53 seconds, the second 1 minute 25 seconds, the third 21 seconds. This makes it quite tricky to calculate the duration in a suitable datatype. In T-SQL people use scripts like the following from MSSQLTips.com

((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)  as ‘RunDurationMinutes’

I wish that some version of SQL Server would fix this “clever” duration.  We’ve had the time datatype since 2008; at least add a new column with run duration as a time value if you’re that concerned with backwards compatibility.

Comments closed

Powershell Text Splitting

Published 2016-09-12 by Kevin Feasel

Thomas Rushton shows how to split delimited text using Powershell:

Not very readable. At least, I can’t read it easily, so I need to split that into something a bit friendlier, like an actual list, one line per item. Fortunately, PowerShell has a split command that should do the job, as per this post ScriptingGuy’s Split Method in PowerShell.

Split() is just a .NET string method; you can use any overloads of that method in Powershell just like you could in C# or F#.

Comments closed

Finding Your SQL Server License Key

Published 2016-09-12 by Kevin Feasel

Richie Lee reproduces a Powershell script to get the license key used for installing SQL Server:

Copied from somewhere else on the internet, this PowerShell script will return the product key used for a SQL instance Install. Super useful when changing licenses on temporary VM’s I spin up and play around with to SQL Developer whose instances have passed the Enterprise evaluation use-by date. Putting this here for my own benefit. I claim no kudos!

Click through for the code.

Comments closed

Powershell Failing On Error

Published 2016-09-12 by Kevin Feasel

Michael Bourgon has a helpful tip for those CmdExec SQL Agent jobs which run Powershell scripts which won’t fail on error:

However, when run via SQL Agent, it succeeds.  GAH!
I tried 50 different variations; modifying the script, various TRY..CATCH blocks found on the internet.  Nothing.  Every single one of them succeeded.

Then I remembered that by default, even though it had an error, by default errors always continue.  ($ErrorActionPreference=”Continue”.  So I added this line at the top:

Read on for the answer.

Comments closed