dbatools is a free PowerShell module with over 180 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

Get-Help

From PowerShell, execute Get-Help Test-DbaLastBackup -Detailed for more information on this function.

Source Code

Want to see the source code? View Test-DbaLastBackup.ps1 on GitHub
 

Related commands