• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

  • Services
  • Pocket DBA®
  • Blog
  • Testimonials
  • Contact
  • About

syndicated

TIL: Measure-DbaBackupThroughput

May 26, 2020 by SQLDork Leave a Comment

Today’s dbatools command: Measure-DbaBackupThroughput

This one’s a smaller command, but that doesn’t mean it’s without use. If you need information on backup speeds (throughput) or early warning of possible hardware issues, such as in a RAID 5 setup, this can help you.

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Basic command usage, then we pipe it through a select statement and into a gridview. Selecting only those specific columns to cut down on redundant information, and also to get it all in one picture easier. Only measures full backups by default.


 

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test `
    -Last `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Maybe we don’t want all the backup history for every database. Maybe we just want the most recent history for one database. The -Database parameter lets us specify which databases to check, and the -Last parameter returns only the most recent backup of each type.



Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Since (Get-Date).AddDays(-28) `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

The -Since parameter combined with Get-Date lets us get all the information since a certain point in time relative to now. Presumably this can also be done with a hard-coded date, but that seems like an edge-case scenario to me.


 

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test `
    -Type Differential `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Use  the -Type parameter to get information on different kinds of backups, accepts “Full”, “Log” or “Differential”.
Note: -Type parameter does not accept multiple values with commas, probably because the results would then have to have up to 3 times as many rows, one for each backup type.


 

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -ExcludeDatabase msdb, master, model, tempdb `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

If we don’t want specific databases, we can use -ExcludeDatabase for that. Useful for only checking the user databases. You don’t have to specifically exclude tempdb, but i did it anyways, just for fun.


Speaking of just for fun, i also took backups of the test database onto 3 different drives, C and two external drives. The results are somewhat less useful than i’d like, though:

Speaking of external drives, go follow me (b|t) on twitter, where i mostly talk about two things:

  1. Things related to SQL Server.
  2. Things that may or may not be related to SQL Server.

Previous Dbatools posts:

Backup-DbaDatabase
Restore-DbaDatabase
Get-DbaLastBackup
Get-DbaAgentJobHistory

Future Dbatools posts:

None yet!

Filed Under: PowerShell, TIL Tagged With: DBATools.io, syndicated

Data Bits – Episode 8 – Chrissy LeMaire

May 18, 2020 by Kevin3NF 1 Comment

Microsoft Data Platform and PowerShell MVP, Chrissy Lemaire (b|t) sat down with us in the virtual studio Saturday, and we had a blast getting to know her!

Quotables:

“This is horrible, this not what I want to do” (Oracle reference)
“This is an at risk system”
“Hold on, let me fix my tongue”

Chrissy’s shout-outs:

Real Cajun Recipes

DBA Tools In a Month of Lunches

SQL Community Slack

 

My Pluralsight course for new SQL Server DBAs

Filed Under: Podcast Tagged With: DBATools.io, syndicated

TIL: Get-DbaAgentJobHistory

May 14, 2020 by SQLDork Leave a Comment

Another blog post, another dbatools command.

Today’s command: Get-DbaAgentJobHistory

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object * `
    | Out-Gridview 

Basic command, gets all the job history info and pipes it to gridview, because there’s a LOT of info here. Probably a good idea to filter it down to the interesting stuff.

The Duration column behaves a bit weird:
For durations under 1 minute, it’ll say x number of seconds or milliseconds. (Yellow arrow in picture below)
Anything over a minute gets formatted as minutes:seconds:milliseconds. (Red arrow)
Unless it’s over an hour, in which case it’s hours:minutes:seconds, which looks exactly the same. (Blue arrow)

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Out-Gridview

Not using Select-Object * cuts down on columns a lot, but we can do better.

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-10 `
    | Out-Gridview

Using -StartDate to only return all the job history from the last 3 days, default value is Jan 1, 1900.

I’m like, 90% sure this job doesn’t have any results from the 20th century though.

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 1900-01-01 `
    -EndDate 1999-12-31 `
    | Out-Gridview

This doesn’t even pull up a gridview on account of there’s no data, which means my earlier theory was right! On a more useful note, -EndDate lets us exclude results from a given date onwards. Default value is $(Get-Date).

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Out-Gridview

If you don’t care about which steps were successful and which failed, -ExcludeJobSteps cuts it down to reporting the job run attempt as a whole.

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Select-Object * `
    | where Status `
        -Match Failed `
    | Out-GridView

Filter to just the failure results. Useful if you want to check which jobs failed and when.

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Select-Object `
        SqlInstance, StartDate, EndDate, `
        Duration, Status, Job, InstanceID, `
        Message, StepName, SqlSeverity, JobName, `
        RunStatus, RunDate, RunDuration, RetriesAttempted `
    | where Status `
        -Match Failed `
    | Export-Csv `
        -Path C:\output.csv `
        -NoTypeInformation

Two things here:
One: Filter out the columns we don’t care about in Select-Object, we don’t need to be doing Select * From * Where * = * here.
Two: Output the data to a csv file so we can play around with it in excel.

Three: There is no three, this is the end of the post. This is also the part where i (b|t) link to my twitter a bunch of times to generate traffic. Also because it’s funny.

Previous Dbatools posts:

Backup-DbaDatabase
Restore-DbaDatabase
Get-DbaLastBackup

Future Dbatools posts:

None yet, unless there are and i forgot to update this part. If that’s the case, go yell at me on twitter!

Filed Under: TIL Tagged With: DBATools.io, syndicated

TIL: Get-DbaLastBackup

May 6, 2020 by SQLDork Leave a Comment

(This post comes from one of our Junior DBAs – @SQLDork)

I’ve been learning dbatools over the past week or so, you can read the previous blog posts here and here.

You can read the current one here, or just scroll down i guess.

Today’s Command: Get-DbaLastBackup

Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016

Nothing fancy, just spits out the last backup info for each database on the box.

Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 | `
    Select-Object *

This gives us some more useful information, telling us the number of days since the database was created/backed up, the backup type, etc.

Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test

Specify which database you want to check, defaults to all of them.

Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -ExcludeDatabase test

Specify that you don’t want to check a certain database. Again, not using this or -Database will return data on all of them.

Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 | `
    Select-Object * | `
    Out-Gridview

Formatting, puts the data in a grid. Useful for pasting into excel or something. Though there’s no way to copy the headers as far as i can tell, so you’ll have to put those in manually.

(Click image to enlarge in new tab)
Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 | `
    Select-Object Database,RecoveryModel | `
    Out-Gridview

Only select some columns, rather than all of them.

Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 | `
    Select-Object * | `
    Export-Csv `
    -Path C:\output.csv `
    -NoTypeInformation

How about we pipe the output to excel/CSV directly instead of pasting it in? Sounds like a good idea to me!

(Click image to enlarge in new tab)

 

That’s all i (b|t) have for today, but it wouldn’t be a SQLDork blog post without several links to my twitter at the end!

(follow my twitter)

Filed Under: PowerShell, TIL Tagged With: DBATools.io, syndicated

TIL: Restore-DbaDatabase

April 29, 2020 by SQLDork Leave a Comment

(This post comes from one of our Junior DBAs – @SQLDork)

If you haven’t read it already, i recently wrote a blogpost where i go over Backup-DbaDatabase, you can read that post here.

Or here, it’s your choice which link you want to click to go there.

Today’s command: Restore-DbaDatabase

Same setup as last time, backups, ISE, SQL Server instances, all that good stuff.

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016

This kinda works, but asks to specify a bunch of paths if you run it. Best to specify those in the parameters ahead of time, i’d imagine.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'

This seems to work, but won’t do anything because the databases already exist.
Warning message suggests using -WithReplace to overwrite the existing databases, but i think there’s another option…

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -DatabaseName 'test_restored'`
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'

Moved some parameters around for readabilities’ sake.
Warning message implies we need to specify the names for the .mdf and .ldf, which makes sense.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -DatabaseName 'test_restored'`
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'`
-DestinationFilePrefix 'restored_'

Success! Database name is “test_restored”, and file names are “restored_test.mdf” and “restored_test.ldf”
Let’s dork around with the parameters a bit to get the names to be consistent:

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_'

Moved the log files to go in the DATA folder, because apparently the Log folder is for different stuff. (Ed: yep…that’s for errorlogs, crash dumps, default trace files, etc.)
No longer specifies the database name via -DatabaseName.
Instead uses -RestoredDatabaseNamePrefix to put ‘restored_’ at the beginning of the name.
This would be useful if we were restoring multiple databases at once.

NOTE: There is a -DestinationFileSuffix, but there is no -RestoredDatabaseNameSuffix parameter. I’ve asked twitter for clarification on this.

There doesn’t seem to be a parameter for renaming the logical names of the
files in Restore-DbaDatabase either, but there is one in Rename-DbaDatabase.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak', 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\square[]brackets_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_'

Restore multiple databases at once, there’s probably a better way to do this without having to specify the obnoxiously long path both times.

Yes, i have a database named square[]brackets.
Yes, i have gone crazy.
No, you shouldn’t make a database with square brackets in the name.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_' -OutputScriptOnly

Using -OutputScriptOnly dumps a T-SQL script into the command line, which you can edit or run to your heart’s content.

 

Summary

Parameters i found to be useful, some of which i didn’t test
because i’d have to insert lines into the database and i’m lazy™:

-SqlInstance: Required, tells powershell what instance to restore to.
Can use commas to point to multiple instances, like if you wanted a fresh copy of prod data on your 7 dev boxi.

-Path: Tells powershell where the backups to restore are, enclose in single-quotes if there’s a space. (Looking at you, Program Files)
Can point to a folder to restore all the backups in that folder, or to a .bak file to restore just that file.
Use commas to specify multiple paths.

-DestinationDataDirectory: Where to put the physical data files, enclose in single-quotes if there’s a space.
-DestinationLogDirectory: Where to put the physical log files, enclose in single-quotes if there’s a space.

NOTE: All paths need to be specified relative to the instance specified with -SqlInstance.

-DestinationFilePrefix: Adds a string to the beginning of the names of the restored files.
-RestoredDatabaseNamePrefix: Adds a string to the beginning of the restored database name.

-DestinationFileSuffix: Adds a string to the end of the names of the restored files.
-RestoredDatabaseNameSuffix: Does not exist, i wish it did, but it doesn’t.

-OutputScriptOnly: Paranoid? This will give you the script for the thing you just did, just in case.

-NoRecovery: Restores the files with NoRecovery, for if you have more files to restore.
-Recover: Does a With Recovery, nothing fancy.

-RestoreTime: Point in time recovery, stop restoring before the bad thing™ happened.

-WithReplace: Overwrites the existing database if there is one with the same name.

-MaintenanceSolutionBackup: Tell dbatools that you’re using OlaHallengren’s (b) maintenance solution.
Makes things faster since it now knows what to look for, rather than checking every file in the folder.

That’s all for today, if you want to bombard me (b|t) with questions i don’t know how to answer, you can do so on twitter!

Filed Under: PowerShell, Restore, TIL Tagged With: DBATools.io, syndicated

TIL: Backup-DbaDatabase

April 27, 2020 by SQLDork Leave a Comment

(This post comes from one of our Junior DBAs – @SQLDork)

I’m learning how to use dbatools.

Today’s command: Backup-DbaDatabase

I’m using the 64-bit PowerShell ISE (running as administrator), running PowerShell version 5.1 on Windows 10, with a local SQL 2016 install.

Here’s the code, each snippet adding additional parameters and building off the one before it, mostly, with a bunch of 100% hilarious comments.*

*Comments may or may not be 100% hilarious, on account of hilarity not being a measurable substance.**
**Probably.

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test

This one works, yaaaay! All it does is take a full backup of one database. We can do better, probably.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Diff

This takes a differential backup, but calling your diffs .bak is bad™

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Differential -FilePath test2.dff

-FilePath lets us set the extension, but in doing so, we lose timestamps. I wonder if there’s a way to fix that… (Spoiler alert: there totally is)

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Differential -FilePath test2.dff -CompressBackup

Also figured out how to compress backups, but it doesn’t work if you try to take a compressed diff after an uncompressed full.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full -FilePath test3.bak -CompressBackup
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type diff -FilePath test3.dff -CompressBackup

This one worked, because science!™ Also backup chains, there’s probably a blog post around here somewhere explaining those, you should go find it and send it to all your friends!

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full -FilePath test3.bak -CompressBackup -initialize

Using -initialize makes it overwrite the existing copy, nothing too exciting.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016

Bare minimum required to run without errors, takes full backups of every system and user database on the box.
Except for tempdb for some reason, idk why*
* I totally know why

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type diff `
-FilePath test3_timestamp.dff -CompressBackup -ReplaceInName

Using -ReplaceInName lets us use timestamps with reasonable file extensions
Welcome to people-not-yelling-at-you-because-they-can’t-tell-if-the-important-backup-file-is-a-full-or-a-log town!
Population: Us

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test, model

Use commas to take backups of multiple databases at once.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -confirm

Anything that’s a checkbox in the Command Add-On doesn’t need any fancy parameters, it just works™.
If you’re using the PS ISE. If you’re using notepad, stop it.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full `
-FilePath test3_timestamp.bak -ReplaceInName -buildpath -createfolder `
-path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup'

-Path tells the files where to go
-FilePath sets the name/extension of the file
-BuildPath tells PowerShell to make the path if it doesn’t exist
-CreateFolder sends all the files into nice little subfolders for the humans to look at and go “Hey look, subfolders!”.

And then they go drink coffee and complain about the database being “slow”.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Type full `
-FilePath dbname_timestamp.bak -ReplaceInName -ExcludeDatabase test -buildpath `
-path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\donuts'

-ExcludeDatabase has a very non-intuitive and confusing function: it excludes databases from being backed up.
Useful for the really big ones you only want to backup once a week or something.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test `
-FilePath dbname_timestamp.bak -ReplaceInName -FileCount 2

This makes multiple striped files, appending numbers automatically, as so:
[filename]-1-of-2.bak
[filename]-2-of-2.bak
[filename]-donuts-of-2.bak
Actually that last one was a joke, but that’s all I got for today, and probably the next month or so.

 

Follow me (b|t) on twitter, where I also probably won’t say anything for the next month or so.

 

Filed Under: backup, PowerShell, TIL Tagged With: DBATools.io, syndicated

  • « Go to Previous Page
  • Go to page 1
  • Interim pages omitted …
  • Go to page 9
  • Go to page 10
  • Go to page 11
  • Go to page 12
  • Go to page 13
  • Go to Next Page »

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!

Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2025 · WordPress · Log in

 

Loading Comments...