PowerShell

We have a double feature for today’s dbatools blog post, as these two commands go hand-in-hand.

Todays commands: Dismount-DbaDatabase and Mount-DbaDatabase

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test'

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test'

Detach-DbaDatabase and Attach-DbaDatabase can be used as aliases. For the purpose of this blog i’m using the official names, because reasons™

Note that the .mdf and .ldf files are still present, meaning detaching a database can keep you from deleting a database you were told wasn’t needed anymore, only to instantly have someone ask why they can’t get to the database they need. If the files are still there, you can attach it and it’s good as new.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -Force

If the database is part of a mirror or something, -Force can be used to detach it anyways.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -UpdateStatistics

This will update the statistics of the database before it’s detached.

$testAttach = New-Object System.Collections.Specialized.StringCollection
$testAttach.Add("C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\test.mdf")
$testAttach.Add("C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\test_log.ldf")

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -FileStructure $testAttach

The -FileStructure parameter can be used to point out where the files to attach are. If not specified, it’ll use backup history to figure out what you mean, if possible.

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -DatabaseOwner 'sa'

The -DatabaseOwner parameter lets you set the, well, database owner, when you attach the database. Defaults to sa.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test', 'square[]brackets'

The -Database parameter can take multiple values.

That’s all i got for this one, not too complicated unless you’re doing something with credentials and stuff. Follow me (b|t) on twitter!

Previous Dbatools posts:

Backup-DbaDatabase
Restore-DbaDatabase
Get-DbaLastBackup
Get-DbaAgentJobHistory
Measure-DbaBackupThroughput
Get-DbaErrorLog

Future Dbatools posts:

None yet!

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!

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