commands

Clear
^

Test-DbaLastBackup
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.

Synopsis

Quickly and easily tests the last set of full backups for a server.

Description

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

  1. Gathers information about the last full backups
  2. Restores the backups to the Destination with a new name. If no Destination is specified, the originating SQL Server instance wil be used.
  3. The database is restored as "dbatools-testrestore-$databaseName" by default, but you can change dbatools-testrestore to whatever you would like using -Prefix
  4. The internal file names are also renamed to prevent conflicts with original database
  5. A DBCC CHECKDB is then performed
  6. And the test database is finally dropped

Syntax

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>]

 

Examples

 

Example: 1
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.

Example: 2
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.

Example: 3
PS C:\> Get-DbaDatabase -SqlInstance sql2016, sql2017 | Test-DbaLastBackup

Tests every database backup on sql2016 and sql2017

Example: 4
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

Example: 5
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.

Example: 6
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.

Example: 7
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.

Example: 8
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.

Example: 9
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.

Example: 10
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/

Example: 11
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

Optional Parameters

-SqlInstance

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
-SqlCredential

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
-Database

The database backups to test. If -Database is not provided, all database backups will be tested.
Alias
Required False
Pipeline false
Default Value
-ExcludeDatabase

Exclude specific Database backups to test.
Alias
Required False
Pipeline false
Default Value
-Destination

The destination server to use to test the restore. By default, the Destination will be set to the source server
If a different Destination server is specified, you must ensure that the database backups are on a shared location
Alias
Required False
Pipeline false
Default Value
-DestinationSqlCredential

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
-DataDirectory

Specifies an alternative directory for mdfs, ndfs and so on. The command uses the SQL Server's default data directory for all restores.
Alias
Required False
Pipeline false
Default Value
-LogDirectory

Specifies an alternative directory for ldfs. The command uses the SQL Server's default log directory for all restores.
Alias
Required False
Pipeline false
Default Value
-FileStreamDirectory

Specifies a directory for filestream data.
Alias
Required False
Pipeline false
Default Value
-Prefix

The database is restored as "dbatools-testrestore-$databaseName" by default. You can change dbatools-testrestore to whatever you would like using this parameter.
Alias
Required False
Pipeline false
Default Value dbatools-testrestore-
-VerifyOnly

If this switch is enabled, VERIFYONLY will be performed. An actual restore will not be executed.
Alias
Required False
Pipeline false
Default Value False
-NoCheck

If this switch is enabled, DBCC CHECKDB will be skipped
Alias
Required False
Pipeline false
Default Value False
-NoDrop

If this switch is enabled, the newly-created test database will not be dropped.
Alias
Required False
Pipeline false
Default Value False
-CopyFile

If this switch is enabled, the backup file will be copied to the destination default backup location unless CopyPath is specified.
Alias
Required False
Pipeline false
Default Value False
-CopyPath

Specifies a path relative to the SQL Server to copy backups when CopyFile is specified. If not specified will use destination default backup location. If destination SQL Server is not local, admin
UNC paths will be utilized for the copy.
Alias
Required False
Pipeline false
Default Value
-MaxSize

Max size in MB. Databases larger than this value will not be restored.
Alias
Required False
Pipeline false
Default Value 0
-DeviceType

Specifies a filter for backup sets based on DeviceTypes. Valid options are 'Disk','Permanent Disk Device', 'Tape', 'Permanent Tape Device','Pipe','Permanent Pipe Device','Virtual Device', in addition
to custom integers for your own DeviceTypes.
Alias
Required False
Pipeline false
Default Value
-IncludeCopyOnly

If this switch is enabled, copy only backups will be counted as a last backup.
Alias
Required False
Pipeline false
Default Value False
-IgnoreLogBackup

If this switch is enabled, transaction log backups will be ignored. The restore will stop at the latest full or differential backup point.
Alias
Required False
Pipeline false
Default Value False
-AzureCredential

The name of the SQL Server credential on the destination instance that holds the key to the azure storage account.
Alias
Required False
Pipeline false
Default Value
-InputObject

Enables piping from Get-DbaDatabase
Alias
Required False
Pipeline true (ByValue)
Default Value
-MaxTransferSize

Parameter to set the unit of transfer. Values must be a multiple of 64kb and a max of 4GB
Parameter is used as passthrough for Restore-DbaDatabase.
Alias
Required False
Pipeline false
Default Value 0
-BufferCount

Number of I/O buffers to use to perform the operation.
Reference: https://msdn.microsoft.com/en-us/library/ms178615.aspx#data-transfer-options
Parameter is used as passthrough for Restore-DbaDatabase.
Alias
Required False
Pipeline false
Default Value 0
-IgnoreDiffBackup

If this switch is enabled, differential backups will be ignored. The restore will only use Full and Log backups, so will take longer to complete
Alias
Required False
Pipeline false
Default Value False
-MaxDop

Allows you to pass in a MAXDOP setting to the DBCC CheckDB command to limit the number of parallel processes used.
Alias
Required False
Pipeline false
Default Value 0
-ReuseSourceFolderStructure

By default, databases will be migrated to the destination Sql Server's default data and log directories. You can override this by specifying -ReuseSourceFolderStructure.
The same structure on the SOURCE will be kept exactly, so consider this if you're migrating between different versions and use part of Microsoft's default Sql structure (MSSql12.INSTANCE, etc)
Alias
Required False
Pipeline false
Default Value False
-EnableException

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
-WhatIf

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
-Confirm

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