Blog

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