dbatools is a free PowerShell module with over 100 SQL Server administration, best practice and migration commands included.


Test-DbaLastBackup

Restores all or some of the latest backups and performs a DBCC CHECKTABLE

  • Restores the test databases (named dbatools-testrestore-$dbname by default) to the specified -SqlServer unless you specify a -Destination.
  • Then it’ll restore to the Destination (if remote, so long as the backups are on a shared directory — which mine always are).
  • You can even specify a MaxMB if you don’t have space for super large database restores (in that case, the -VerifyOnly switch could come in handy).
  • Want your Data and Log files to go somewhere other than default? Use the -DataDirectory and -LogDirectory params.

Video

Examples

Determines the last full backup for ALL databases, attempts to restore all databases (with a different name and file structure), then performs a DBCC CHECKTABLE. Once the test is complete, the test restore will be dropped

Test-DbaLastBackup -SqlServer sql2016

Determines the last full backup for master, attempts to restore it, then performs a DBCC CHECKTABLE

Test-DbaLastBackup -SqlServer sql2016 -Databases master

To perform a VERIFYONLY restore

Test-DbaLastBackup -SqlServer sql2016 -Databases model, master -VerifyOnly

Want to skip the DBCC CHECKTABLE check? NoCheck can help speed up the tests but makes it less tested. Don’t want to automatically drop the restored test database? NoDrop means that the test restores will remain on the server.

Test-DbaLastBackup -SqlServer sql2016 -NoCheck -NoDrop

Restores data and log files to alternative locations and only restores databases that are smaller than 10 GB

Test-DbaLastBackup -SqlServer sql2016 -DataDirectory E:\bigdrive -LogDirectory L:\bigdrive -MaxMB 10240

Screenshots

From PowerShell, execute   Get-Help Test-DbaLastBackup -Detailed   for more information on this function. Want to see the source code? See it on GitHub