| Author | Chrissy LeMaire (@cl), netnerds.net |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Test-DbaLastBackup on GitHub.
Want to see the Bill Of Health for this command? Check out Test-DbaLastBackup.
Quickly and easily tests the last set of full backups for a server.
Restores all or some of the latest backups and performs a DBCC CHECKDB.
Test-DbaLastBackup
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-Database] <Object[]>]
[[-ExcludeDatabase] <Object[]>]
[[-Destination] <DbaInstanceParameter>]
[[-DestinationSqlCredential] <Object>]
[[-DataDirectory] <String>]
[[-LogDirectory] <String>]
[[-FileStreamDirectory] <String>]
[[-Prefix] <String>]
[-VerifyOnly]
[-NoCheck]
[-NoDrop]
[-CopyFile]
[[-CopyPath] <String>]
[[-MaxSize] <Int32>]
[[-DeviceType] <String[]>]
[-IncludeCopyOnly]
[-IgnoreLogBackup]
[[-AzureCredential] <String>]
[[-InputObject] <Database[]>]
[[-MaxTransferSize] <Int32>]
[[-BufferCount] <Int32>]
[-IgnoreDiffBackup]
[[-MaxDop] <Int32>]
[-ReuseSourceFolderStructure]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Test-DbaLastBackup -SqlInstance sql2016
Determines the last full backup for ALL databases, attempts to restore all databases (with a different name and file structure), then performs a DBCC CHECKDB. Once the test is complete, the test
restore will be dropped.
PS C:\> Test-DbaLastBackup -SqlInstance sql2016 -Database SharePoint_Config
Determines the last full backup for SharePoint_Config, attempts to restore it, then performs a DBCC CHECKDB.
PS C:\> Get-DbaDatabase -SqlInstance sql2016, sql2017 | Test-DbaLastBackup
Tests every database backup on sql2016 and sql2017
PS C:\> Get-DbaDatabase -SqlInstance sql2016, sql2017 -Database SharePoint_Config | Test-DbaLastBackup
Tests the database backup for the SharePoint_Config database on sql2016 and sql2017
PS C:\> Test-DbaLastBackup -SqlInstance sql2016 -Database model, master -VerifyOnly
Skips performing an action restore of the database and simply verifies the backup using VERIFYONLY option of the restore.
PS C:\> Test-DbaLastBackup -SqlInstance sql2016 -NoCheck -NoDrop
Skips the DBCC CHECKDB check. This can help speed up the tests but makes it less tested. The test restores will remain on the server.
PS C:\> Test-DbaLastBackup -SqlInstance sql2016 -DataDirectory E:\bigdrive -LogDirectory L:\bigdrive -MaxSize 10240
Restores data and log files to alternative locations and only restores databases that are smaller than 10 GB.
PS C:\> Test-DbaLastBackup -SqlInstance sql2014 -Destination sql2016 -CopyFile
Copies the backup files for sql2014 databases to sql2016 default backup locations and then attempts restore from there.
PS C:\> Test-DbaLastBackup -SqlInstance sql2014 -Destination sql2016 -CopyFile -CopyPath "\\BackupShare\TestRestore\"
Copies the backup files for sql2014 databases to sql2016 default backup locations and then attempts restore from there.
PS C:\> Test-DbaLastBackup -SqlInstance sql2016 -NoCheck -MaxTransferSize 4194302 -BufferCount 24
Determines the last full backup for ALL databases, attempts to restore all databases (with a different name and file structure).
The Restore will use more memory for reading the backup files. Do not set these values to high or you can get an Out of Memory error!!!
When running the restore with these additional parameters and there is other server activity it could affect server OLTP performance. Please use with caution.
Prior to running, you should check memory and server resources before configure it to run automatically.
More information:
https://www.mssqltips.com/sqlservertip/4935/optimize-sql-server-database-restore-performance/
PS C:\> Test-DbaLastBackup -SqlInstance sql2016 -MaxDop 4
The use of the MaxDop parameter will limit the number of processors used during the DBCC command
The target SQL Server instance or instances. Unlike many of the other commands, you cannot specify more than one server.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies which databases to include in the backup test. Accepts wildcards for pattern matching.
Use this to limit testing to specific databases instead of all databases on the instance.
Helpful when you only want to verify critical databases or troubleshoot specific backup issues.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies databases to exclude from backup testing. Accepts wildcards for pattern matching.
Use this to skip non-critical databases, large databases, or databases with known backup issues.
Commonly used to exclude system databases or databases that would take too long to test.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the SQL Server instance where test restores will be performed. Defaults to the source server if not specified.
Use this when you want to test restores on a different server, such as isolating test operations from production workloads.
When using a different destination server, backup files must be accessible from that server via shared storage or use -CopyFile.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Credentials for connecting to the destination SQL Server instance when different from the source.
Use this when the destination server requires different authentication than the source server.
Accepts PowerShell credentials (Get-Credential) and supports Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the directory where restored database data files (.mdf, .ndf) will be placed. Defaults to the SQL Server's default data directory.
Use this when you need to direct test restores to specific storage, such as faster drives for testing or isolated storage locations.
The SQL Server service account must have write permissions to this directory.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the directory where restored database log files (.ldf) will be placed. Defaults to the SQL Server's default log directory.
Use this when you want to separate test database logs from production logs or direct them to faster storage for testing.
The SQL Server service account must have write permissions to this directory.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the directory where FileStream data will be restored for databases that use FileStream storage.
Use this when testing databases with FileStream-enabled tables to ensure the FileStream data is properly restored and accessible.
Required only for databases that contain FileStream data and when not using -ReuseSourceFolderStructure.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the naming prefix for test databases. Defaults to 'dbatools-testrestore-' resulting in names like 'dbatools-testrestore-MyDB'.
Use this to customize test database naming for organizational standards or to avoid naming conflicts.
Choose prefixes that clearly identify databases as temporary test restores.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | dbatools-testrestore- |
Performs backup verification only without actually restoring the database. Uses T-SQL RESTORE VERIFYONLY command.
Use this for faster backup validation when you only need to confirm backup file integrity without full restore testing.
Skips DBCC CHECKDB since no actual database is restored.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Skips the DBCC CHECKDB operation after restoring the test database.
Use this to speed up the testing process when you only need to verify that backups can be restored successfully.
Reduces testing time but provides less thorough validation of database integrity.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Prevents the test database from being automatically dropped after the test completes.
Use this when you need to examine the restored database manually or perform additional testing.
Remember to manually clean up test databases later to avoid storage issues.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Copies backup files to the destination server's default backup directory before attempting the restore.
Use this when backup files are not accessible from the destination server, such as local backups on different servers.
Cannot be used with Azure SQL Database backups.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Specifies the destination directory where backup files will be copied when using -CopyFile. Defaults to the destination server's default backup directory.
Use this to control where backup files are temporarily stored during testing, such as directing them to faster storage.
Path must be accessible to the destination SQL Server service account.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Maximum database size in MB. Databases with backups larger than this value will be skipped.
Use this to avoid testing extremely large databases that would consume excessive time or storage resources.
Helps focus testing on databases that can be practically tested within available resources.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Filters backups by device type such as 'Disk', 'Tape', or 'Virtual Device'. Accepts multiple values.
Use this when you need to test only backups from specific backup devices or exclude certain device types.
Commonly used to test only disk backups or exclude tape backups that may be offline.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Includes copy-only backups when determining the most recent backup to test.
Use this when you want to test copy-only backups that were created for specific purposes like migrations or testing.
Copy-only backups don't break the backup chain but are normally excluded from 'last backup' queries.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Skips transaction log backups during restore, stopping at the most recent full or differential backup.
Use this for faster testing when point-in-time recovery precision isn't critical for the test.
Results in some data loss compared to a complete restore chain but significantly reduces testing time.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Specifies the name of the SQL Server credential that contains the key for accessing Azure Storage where backups are stored.
Use this when testing backups stored in Azure Blob Storage that require credential-based authentication.
The credential must already exist on the destination SQL Server instance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Accepts database objects from Get-DbaDatabase for pipeline processing.
Use this to test backups for databases selected through Get-DbaDatabase filtering options.
Enables complex database selection scenarios beyond simple name matching.
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
Sets the data transfer unit size for backup restoration. Must be a multiple of 64KB with a maximum of 4GB.
Use this to optimize restore performance by increasing buffer size, especially for large databases on high-speed storage.
Higher values can improve performance but consume more memory during the restore operation.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Specifies the number of I/O buffers used during the restore operation.
Use this to optimize restore performance by controlling memory allocation for the restore process.
Higher values can improve performance but consume more memory, so balance against other server activity.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Skips differential backups during restore, using only full and transaction log backups.
Use this to test restore scenarios that don't rely on differential backups or when differential backups are suspected to be problematic.
May significantly increase restore time due to processing more transaction log backups.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Sets the maximum degree of parallelism for the DBCC CHECKDB operation. Limits the number of parallel processes used.
Use this to control resource usage during integrity checks, especially on busy servers or when testing multiple databases.
Lower values reduce CPU usage but increase DBCC runtime.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Maintains the original file paths and directory structure from the source database during restore.
Use this when testing databases that have specific file location requirements or when simulating exact production restore scenarios.
Ensures the destination server has the same directory structure as the source or the restore will fail.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.
| Alias | wi |
| Required | False |
| Pipeline | false |
| Default Value |
If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.
| Alias | cf |
| Required | False |
| Pipeline | false |
| Default Value |