Press "Enter" to skip to content

Category: Powershell

Finding Identity Columns Close To Overflow

Published 2017-04-07 by Kevin Feasel

Rob Sewell demos another dbatools feature:

It would be very useful to be able to quickly see what the current values of the identity columns are and how close they are to being full so that we can plan for and be able to take action before we end up with shouty smart suits at our desk. If we could do it with just one line of code that would be even easier.
Step forward dbatools.  This PowerShell module is a community based project written by excellent, brilliant people in their own time and available to you free. To find out more and how to use and install it visit https://dbatools.io
There is a command called Test-DbaIdentityUsage This command was created by Brandon Abshire. You can find Brandon blogging at netnerds.net. Thank you Brandon

That’s quite useful for taking a quick look at identity columns across a database or instance.

Comments closed

DataRows Everywhere

Published 2017-04-07 by Kevin Feasel

Shane O’Neill shows how to deal with .NET DataRows in Powershell:

Now I don’t know about you but when I query stuff in a SQL database, it’s to do something to/with the results. They could be a list of servers that I monitor, they could be a list of databases that I want to check the recovery model of, or it could be a list of tables that I want to see how much space they are using. The main point is that I want to do something with the results.

But for this simple case, I just want to list out the customer name from this table. Simple? Yes, but this is just a test case to prove a point.

So let’s PowerShell this!

The moral of the story is to Get-Member early and Get-Member often.

Comments closed

Connecting Powershell To Docker Containers

Published 2017-04-06 by Kevin Feasel

Andrew Pruski shows how to open a Powershell session on a container:

One of the questions that I was asked at SQL Saturday Iceland was “how can I view the filesystem within a container?”.

This is a great question as one of the things that people find off-putting about containers is their opaqueness. It’s not obvious where everything lives within the container or how we can view the files within it.

Thankfully there’s a simple docker command that allows us to open a powershell session within a container, that command is docker exec.

For Linux-based containers, /bin/bash (or your favorite shell, if it’s installed) serves as its analog.

Comments closed

Last Known Good DBCC CHECKDB In Powershell

Published 2017-04-05 by Kevin Feasel

Rob Sewell shows off a cmdlet to check DBCC DBINFO for each database to get the last known good CHECKDB run:

This time we get more information. The server name, database name, when the database was created, the last good DBCC Checkdb, how long since the database was created, how long since the last known good DBCC Checkdb, a status and a Data Purity enabled flag. If you look at the image above it shows that the DBA_Admin database has a status of “New database, not checked yet” even though it has a date for the last known good DBCC CheckDb. This is because it was restored after this server was upgrade from CTP 1.3 to CTP 1.4 and there has not yet been a DBCC CheckDb run yet. The system databases have a status of “CheckDb should be performed”. This is because the last known good DBCC CheckDb is more than 7 days ago. Lets run a DBCC CheckDb and check again

Do read the caveats, and also check out a previous Arun Sirpal blog post on DBCC DBINFO.

Comments closed

Database File Sizes In Powershell

Published 2017-03-29 by Kevin Feasel

Rob Sewell has a nice post on checking database file sizes using dbatools in Powershell:

As always, PowerShell uses the permissions of the account running the sessions to connect to the SQL Server unless you provide a separate credential for SQL Authentication. If you need to connect with a different windows account you will need to hold Shift down and right click on the PowerShell icon and click run as a different user.

Lets get the information for a single database. The command has dynamic parameters which populate the database names to save you time and keystrokes

It’s a great post, save for the donut chart…  Anyhow, this is recommended reading.

Comments closed

Testing SQL Server On Linux Backups

Published 2017-03-28 by Kevin Feasel

Rob Sewell confirms that Test-DbaLastBackup in the dbatools kit works for Linux:

I have written about Test-DbaLastBackup in posts here, here and here. They have been Windows only posts.

With SQL Server vNext CTP 1.4 now available and providing SQL Agent capability on Linux, I wrote here about using Ola Hallengrens scripts on Linux SQL Servers so can Test-DbaLastBackup work with Linux?

It’s a short post but good to know.

Comments closed

Connecting To Linux SQL Agent Using Powershell

Published 2017-03-28 by Kevin Feasel

Slava Murygin shows how to connect to a SQL Agent running on Linux using the SqlServer Powershell module:

From this point we will work directly with SQL Server.
In order to establish connection you have to run following script.
The most important are 2nd and third lines:
– In second line you have to provide your SQL Server Instance address, by replacing “<your_server_instance>” by something like “192.168.58.11” or “192.168.58.11\MSSQLSERVER,1433”
– When second line runs it will ask you for SQL Server credentials !!! So, you have to enter SQL user name and it’s password.

Slava does note some limitations at present, but a lot of the functionality seems to be there.

Comments closed

SQL On Linux Backups

Published 2017-03-23 by Kevin Feasel

Rob Sewell shows how to use Ola Hallengren’s solution to back up SQL Server databases on Linux using the SQL Agent:

Now the jobs are not going to run as they are as they have CmdExec steps and this is not supported in SQL on Linux so we have to make some changes to the steps. As I blogged previously, this is really easy using PowerShell

First we need to grab the jobs into a variable. We will use Get-SQLAgentJobHistory from the sqlserver module which you need to download SSMS 2016 or later to get. You can get it from https://sqlps.io/dl As we are targeting a Linux SQL Server we will use SQL authentication which we will provide via Get-Credential and then take a look at the jobs

It’s not “point, click, done,” but Rob shows you certainly can do it.

Comments closed

Testing Backups With dbatools

Published 2017-03-20 by Kevin Feasel

Sander Stad shows how to test database restorations en masse using dbatools:

Testing your backups is a tedious job and it takes a lot of time which I as a DBA don’t have. I don’t have the time to restore a database, run a DBCC command for every database that’s backed up.

There is a solution and it’s called “Test-DbaLastBackup” which is part of the dbatools module.

Rob Sewell also has a recent post on the topic.

Comments closed

Restoring Databases With dbatools

Published 2017-03-20 by Kevin Feasel

Rob Sewell shows how to restore a slew of databases using one dbatools command:

In my lab I had installed SQL 2016 on a server running Server 2016 TP5 which expired so I needed to re-install Windows and therefore needed to restore all of my user databases again. This was so easy using the dbatools module that I thought it was worth sharing to show how easy your disaster recovery process could be.

Having re-installed Windows and SQL and copied the backup files back to the server (although I could have used a network location), I then had to restore all of the user databases.

This is how I restored all of my user databases using the dbatools module command Restore-SQLBackupFromDirectory

Read on for the answer, as well as a warning that the upcoming dbatools 1.0 release may change some things.

Comments closed