DallasDBAs.com

Explaining SQL Server in plain english

Header Right

Blog

TIL: Dismount-DbaDatabase and Mount-DbaDatabase

by Leave a Comment

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!

TIL: Get-DbaErrorLog

by Leave a Comment

Ed: SQLDork is a Junior DBA on the team here, learning the DBATools in addition to normal DBA stuff™

Today’s dbatools command is Get-DbaErrorLog, which is very similar to a script we frequently run on client servers to check for errors that need fixing.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Basic command usage, using Select-Object to filter out the columns i don’t really care about, then piping the results to Out-Gridview.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 1, 0 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Adding the -LogNumber Parameter to check the archived errorlogs, defaults to current log only.
NOTE: If you want the results ordered by date, the values passed into -LogNumber must be in descending order.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Source spid5s `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

The -Source parameter lets us filter out some stuff™. I can’t think of a better way to describe that. The documentation uses -Source Logon as an example, but my sample data didn’t have that so i used a spid.

(Ed: The Source can be a spid, “logon”, “Server”, or anything you see in the errorlog Source column in the SSMS Log Viewer)

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 5, 4, 3, 2, 1, 0 `
    -Text "instance" `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Using -Text as a rudimentary where clause, but this one only supports 1 string. We can do better.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 5, 4, 3, 2, 1, 0 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Where { `
                ( `
                $_.Text -Like "*CheckDB*" `
            -or $_.Text -Like "*Error*" `
            -or $_.Text -Like "*UTC*" `
                ) `
           -and ( `
                $_.Text -NotLike "*found 0 errors*" `
           -and $_.Text -NotLike "*finished without errors*" `
           -and $_.Text -NotLike "*informational message*" `
                ) `
            } `
| Out-GridView

This formatting feels very strange to me, but it does a very similar thing to that errorlog query we use that i mentioned earlier.

(Ed: The Where-Object or Where is PowerShell, not DBATools, click here)

Shoutout to Kevin (b|t) for helping me figure out the syntax on this, and also for pointing out that he changed the -SqlInstance parameter to his own machine name when he was testing this. Saved me a bunch of time troubleshooting connection errors.

Speaking of connection errors, follow me (b|t) on twitter, even though i don’t really tweet that much. Unless i retweet pictures of dogs, that’s always a possibility.

Pluralsight course for new SQL Server DBAs

TIL: Measure-DbaBackupThroughput

by 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. 

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



Previous Dbatools posts:


Backup-DbaDatabase

Restore-DbaDatabase

Get-DbaLastBackup

Get-DbaAgentJobHistory


Future Dbatools posts:


None yet!


Data Bits – Episode 8 – Chrissy LeMaire


 by  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


TIL: Get-DbaAgentJobHistory


 by  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!