dbatools 1.0 and how to make yourself a SuperHero

dbatools 1.0

https://dbatools.io/

Have you ever needed to migrate a SQL Server database from one version to another and dreaded all the T-SQL you will have to write and debug?

What about migrating 30 SQL Server machines with 100's of database on each machine?

I feel your pain.

How about using Log Shipping to migrate the data over time, then cut over with little downtime.

# Also supports multiple destinations!
# Oh, and has a ton of params, so use a PowerShell splat
 $params = @{
    Source = 'sql2008'
    Destination = 'sql2016', 'sql2017'
    Database = 'shipped'
    BackupNetworkPath= '\\backups\sql'
    PrimaryMonitorServer = 'sql2012'
    SecondaryMonitorServer = 'sql2012'
    BackupScheduleFrequencyType = 'Daily'
    BackupScheduleFrequencyInterval = 1
    CompressBackup = $true
    CopyScheduleFrequencyType = 'Daily'
    CopyScheduleFrequencyInterval = 1
    GenerateFullBackup = $true
    Force = $true
}

# pass the splat
Invoke-DbaLogShipping @params

# And now, failover to secondary
Invoke-DbaLogShippingRecovery -SqlInstance localhost\sql2017 -Database shipped

dbatools is an expansive and extremely flexible PowerShell toolkit that leverages  the scripting abilities within PowerShell to manipulate SQL Server objects.

Do NOT let the 1.0 fool you. This code base has been building incrementally over the past few years, with many contributors writing code and creating Pester tests.

There are modules for every common operation that a SQL Server DBA will have to perform.

This toolkit contains over 500 modules to help you get your work automated and projects completed.

My favorite modules are the discovery modules, to help you find details about your SQL Server instance.

https://docs.dbatools.io/

Get-dba* in the Search box will show you all the cool discovery modules. The naming convention of the module will give you a hint of what it will do.

There is good documentation in the code and on the dbatools blog.

https://dbatools.io/blog/

Support is the backbone of this tool, where you can chat or email directly to the developers, contributors, and power users that will give you tips, code samples, and help you debug any roadblock you may encounter.

To get started, try this Getting Started web page.

https://dbatools.io/getting-started/

On Slack you can connect with the dbatools family and just soak in all the great chats and posting that relate to the tool, PowerShell, and SQL Server. The Slack channel is #dbatools and it is a great place to hang out and meet really awesome DBA's and developers from around the world.

https://dbatools.io/slack

The code is community driven, open source, and free.

What environments does dbatools support?

SQL Server 2000 – 2017
Express – Datacenter Edition
Clustered and stand-alone instances
Windows and SQL authentication
Default and named instances
Multiple instances on one server

We love new contributions of code, so please join up!

If you want to file an issue with the code or have a suggestion, go to http://dbatools.io/issues

Chrissy LeMaire (t) @cl is the ring leader of this awesome group, and she can always point you in the right direction for help, support or encouragement.

Script to generate all the exploration I normally do when I want to install a new PowerShell module

Script to generate all the exploration I normally do when I want to install a new PowerShell module

#PowerShell Script to generate all the exploration I normally do when I want to install a new PowerShell module, generates code in a new .PS1 for exploring each function in the module including a formatted splat

 I find this a time saver for 

me.  

https://gist.github.com/NakedPowerShell/9711e57e18fa4c4cd0fed148497f900e>

New PowerShell Module: Get-SQLSatDL

# Blog Post for Get-SQLSatDL

Blog: Get-SQLSat

Get-SQLSatDL : Release Date - 07/05/2018 Version 1.0

In February 2018 I went to my third SQL Saturday in Redmond WA, held on the campus of Microsoft.

I had an awesome time and got to reconnect with many SQL friends and met plenty of new ones.

I always learn so much from the session speakers and I look forward to seeing what kind of content they post on the SQL Saturday website a few days later.

A few days after the event, I went to the schedule web page:

<http://www.sqlsaturday.com/696/Sessions/Schedule.aspx>

And then proceeded to click on each download icon under the sessions that did have content.

I had to click the download button 27 times.

I could hear Jeffery Snover’s (Microsoft Fellow and Inventor of PowerShell) voice in my head, “don’t be a click next person, automate”.

I was also looking at a few other past events from around the country that I was interested in downloading.

That would be a lot of clicking download.

I created a PowerShell function to automate the downloading of all the content posted to the schedule page of the SQL Saturday event.

I call it Get-SQLSatDL that has only 3 parameters, the first is the local drive and path where you want the downloads to be stored, second is the SQL Saturday event # and then lastly if you want any zip files to be automatically expanded.

For example:

Get-SQLSatDL "K:\SQLSat_DL" 696 -Uz $true

`Get-SQLSatDL [-Tpath] <String> [-Evt] <Int32> [-Uz] <bool> [<CommonParameters>]`

First parameter [TPath]: Local target path string where to download content to

Second parameter[Evt]: SQL Saturday event # (an integer between 500-1000)

Third parameter [Uz]: $true/$false to unzip zip files

You can find my code on GitHub:

<https://github.com/NakedPowerShell/SQLSatDL>

## Installing SQLSatDL

Download the code from <https://github.com/NakedPowerShell/SQLSatDL>

and put it in a local folder like C:\SQLSatDL

## Run PowerShell command line or PowerShell ISE

From within PowerShell type this:

Import-Module C:\SQLSatDL\SQLSatDL.psm1

`# To see a few examples for this module run this`

Get-Help Get-SQLSatDL -Examples

## To download SQL Saturday Event #696 content Redmond WA

`# Decide where you want to store the content downloaded like K:\SQLSat_DL`

Get-SQLSatDL "K:\SQLSat_DL" 696 -Uz $true

## To download SQL Saturday Event #696 Redmond WA content with Verbose

Get-SQLSatDL "K:\SQLSat_DL" 696 -Uz $true -verbose

`# To see all the comment based help`

Get-Help Get-SQLSatDL -All

If you have any questions, you can find me on Twitter @NakedPowerShell

or send me an email at `NakedPowerShell at gmail.com`

## Future Ideas

Ideas for extending the output of this function:

- Merge all the CSV log_SQLSAT.txt files in each sub-folder into a SQL table for searching and sorting

- Use a search tool to scan all the Schedule_[Event#].html files in each sub-folder for keywords / authors

07/05/2018 <https://nakedpowershell.blogspot.com/>

Robert Herjavec's Dos and Don'ts: From Cryptocurrency to #Cyber Hygiene

Robert Herjavec's Dos and Don'ts: From Cryptocurrency to #Cyber Hygiene #SharkTank https://www.entrepreneur.com/article/310606

Announcing the May 2018 Git Security Vulnerability

Announcing the May 2018 #Git #Security Vulnerability https://blogs.msdn.microsoft.com/devops/2018/05/29/announcing-the-may-2018-git-security-vulnerability/

ICYMI - dbachecks – Dark Mode Historical Validation #PowerBi

ICYMI - dbachecks – Dark Mode Historical Validation #PowerBi RT @sqldbawithbeard "ICYMI - dbachecks – Dark Mode Historical Validation #PowerBi - " http://ht.ly/Gbsr30kfAHL

Create Function from Variable Value I

Create Function from Variable Value I #PowerShell http://tommymaynard.com/create-function-from-variable-value-i-2018/