• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

  • Services
    • SQL Server Health Check
    • Fractional DBA
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

PowerShell

TIL: Dismount-DbaDatabase and Mount-DbaDatabase

June 24, 2020 by SQLDork 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!

Filed Under: PowerShell, TIL Tagged With: DBATools.io, 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

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

IRL #7: Test Restores and CheckDB

April 23, 2020 by Kevin3NF 2 Comments

My Pluralsight course for new SQL Server DBAs

Problem:

My client asked me for a way to automate test restores and CheckDB either ad hoc or on a schedule, but not for certain databases that are close to or over 1TB in size.

Background:

My client has a number of SQL Server instances across multiple versions from 2008R2 up to 2016, Standard and Enterprise edition. We recently standardized all of their maintenance (backups, indexes, stats and checkdb) across the board using the Maintenance Solution from Ola Hallengren.

Investigation:

Digging around a bit revealed several choices:

  • Roll my own T-SQL ($$$)
  • Use a 3rd party product ($$)
  • Use DBATools.io (Free)

I was given the OK to spend up to 4 hours for a Proof of Concept on the DBATools option. DBATools is a set of commands that are all built using PowerShell. You don’t need to know PowerShell to use them.

Process:

I have a lab with SQL Server 2016, 2017 and 2019 installed (all Developer edition). Each instance has a few test databases, including WideWorldImporters, StackOverflow and some other local ones used just to test specific ideas.

I am using PSVersion 5.1.x, with the DBATools module installed.

The core command I am using (out of well over 550 available) is Test-DbaLastBackup. I need to start with getting one instance to restore its databases into another, run CheckDB and then drop the DB.

Test-DbaLastBackup -Destination kbh-precision\SQL2019 -SqlInstance  KBH-Precision\SQL2017 `
                   -IgnoreLogBackup -ExcludeDatabase master,model,msdb, StackOverflow

This allowed me to do the minimum, skipping System databases and StackOverflow (due to its size) and work from just the most recent Full backup plus any Differentials. I only have one database that met the criteria, and I have intentionally corrupted it using DBCC WRITEPAGE. DONT DO THIS UNLESS YOU KNOW WHAT YOU ARE DOING!!!

Results:

SourceServer   : KBH-Precision\SQL2017
TestServer     : kbh-precision\SQL2019
Database       : XE_demo
FileExists     : True
Size           : 3.20 MB
RestoreResult  : Success
DbccResult     : Table error: Object ID 565577053, index ID 0, partition ID 72057594041270272, alloc unit ID 72057594046906368 (type In-row data), page (1:560). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. 
                 Values are 133129 and -4.
                 Object ID 565577053, index ID 0, partition ID 72057594041270272, alloc unit ID 72057594046906368 (type In-row data): Page (1:560) could not be processed.  See other errors for details.
                 DBCC results for 'RndNums'.
                 There are 0 rows in 0 pages for object "RndNums".
                 CHECKDB found 0 allocation errors and 2 consistency errors in table 'RndNums' (object ID 565577053).
                 DBCC results for 'sys.plan_persist_query_hints'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_query_hints".
                 DBCC results for 'sys.plan_persist_query_template_parameterization'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_query_template_parameterization".
                 DBCC results for 'sys.plan_persist_wait_stats'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_wait_stats".
                 DBCC results for 'sys.persistent_version_store'.
                 There are 0 rows in 0 pages for object "sys.persistent_version_store".
                 DBCC results for 'sys.persistent_version_store_long_term'.
                 There are 0 rows in 0 pages for object "sys.persistent_version_store_long_term".
                 DBCC results for 'sys.wpr_bucket_table'.
                 There are 0 rows in 0 pages for object "sys.wpr_bucket_table".
                 DBCC results for 'sys.queue_messages_1977058079'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
                 DBCC results for 'sys.queue_messages_2009058193'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
                 DBCC results for 'sys.queue_messages_2041058307'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
                 DBCC results for 'sys.filestream_tombstone_2073058421'.
                 There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
                 DBCC results for 'sys.syscommittab'.
                 There are 0 rows in 0 pages for object "sys.syscommittab".
                 DBCC results for 'sys.filetable_updates_2105058535'.
                 There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
                 CHECKDB found 0 allocation errors and 2 consistency errors in database 'dbatools-testrestore-XE_demo'.
                 repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbatools-testrestore-XE_demo).
                 DBCC execution completed. If DBCC printed error messages, contact your system administrator.
RestoreStart   : 2020-04-22 07:36:09.391
RestoreEnd     : 2020-04-22 07:36:10.706
RestoreElapsed : 00:00:01
DbccStart      : 2020-04-22 07:36:10.772
DbccEnd        : 2020-04-22 07:36:12.323
DbccElapsed    : 00:00:01
BackupDates    : {04/16/2020 10:19:19}
BackupFiles    : D:\Backups\KBH-Precision_SQL2017\XE_Demo_20200416.bak

PS C:\WINDOWS\system32>

From there I tested various methods to get the output into something I can look at or, alert on, etc.  Not knowing how deep my client wanted to go at the time, I settled on this:

#Import-Module dbatools

Test-DbaLastBackup -Destination kbh-precision\SQL2019 -SqlInstance  KBH-Precision\SQL2017, KBH-Precision\SQL2016 `
                   -IgnoreLogBackup -ExcludeDatabase master,model,msdb, StackOverflow | `
                    Select-Object -Property SourceServer,Database,RestoreResult,DBCCResult | `
                    Write-SQLTableData -ServerInstance "kbh-precision\SQL2019" -DatabaseName "DBA" -SchemaName "dbo" -Tablename "Test_DBALastBackup" -Force

The last step was to get this into a SQL Agent job (or jobs, depending on the flexibility needed).

I assumed a SQL Agent PowerShell job step would be the best, but my friend Andy Levy(b|t) suggested using this approach.

Results:

With these 4 lines of code and less than 3 hours (ignoring the permissions issues I had), I was able to create a flexible process that can be changed by a simple edit of the .ps1 file.

No doubt there are better ways to implement all of this, error handling, etc. but the ideas got my client what they needed.

“That’s exactly what we wanted”

“How well does it work on Availability Groups?” (Don’t know yet, will test that soon).

Conclusion:

  • DBATools.io has a ton of useful, free, opensource commands…look there before you roll your own T-SQL. Someone else may have already solved your problem.
  • You don’t have to know PowerShell to use these tools, any more than you have to know how fuel injectors work to drive a car.

Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/

 

The video version:

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

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

  • Go to page 1
  • Go to page 2
  • 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...