Remove-DbaDbBackupRestoreHistory

Want to see the source code for this command? Check out Remove-DbaDbBackupRestoreHistory on GitHub.
Want to see the Bill Of Health for this command? Check out Remove-DbaDbBackupRestoreHistory.

Synopsis

Removes backup and restore history records from MSDB database to prevent excessive growth

Description

Removes backup and restore history records from MSDB database tables to prevent them from consuming excessive disk space and degrading performance. Over time, these history tables can grow substantially on busy SQL Server instances with frequent backup operations.

Works in two modes: server-level cleanup removes records older than a specified retention period (default 30 days), while database-level cleanup removes the complete backup/restore history for specific databases. This is particularly useful when decommissioning databases or cleaning up after major maintenance operations.

The backup and restore history tables reside in the MSDB database and include backupset, backupfile, restorehistory, and related system tables. Large history accumulations can impact backup operations, SSMS performance when viewing backup history, and overall MSDB database size.

For production environments, consider scheduling regular cleanup using the sp_delete_backuphistory agent job from Ola Hallengren's SQL Server Maintenance Solution (https://ola.hallengren.com) rather than manual cleanup operations.

Syntax

Remove-DbaDbBackupRestoreHistory
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-KeepDays] <Int32>]
    [[-Database] <String[]>]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Remove-DbaDbBackupRestoreHistory -SqlInstance sql2016

Prompts for confirmation then deletes backup and restore history on SQL Server sql2016 older than 30 days (default period)

Example: 2

PS C:\> Remove-DbaDbBackupRestoreHistory -SqlInstance sql2016 -KeepDays 100 -Confirm:$false

Remove backup and restore history on SQL Server sql2016 older than 100 days. Does not prompt for confirmation.

Example: 3

PS C:\> Remove-DbaDbBackupRestoreHistory -SqlInstance sql2016 -Database db1

Prompts for confirmation then deletes all backup and restore history for database db1 on SQL Server sql2016

Example: 4

PS C:\> Get-DbaDatabase -SqlInstance sql2016 | Remove-DbaDbBackupRestoreHistory -WhatIf

Remove complete backup and restore history for all databases on SQL Server sql2016

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. You must have sysadmin access and server version must be SQL Server version 2000 or higher.

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

-KeepDays

Specifies how many days of backup and restore history to retain when performing server-level cleanup. Records older than this period will be deleted from MSDB history tables.
Use this for regular maintenance to prevent MSDB growth while preserving recent history for troubleshooting. Cannot be combined with Database parameter.

-Database

Specifies specific databases to completely remove all backup and restore history records regardless of age. Accepts multiple database names and wildcards.
Use this when decommissioning databases or performing targeted cleanup after major maintenance operations. Cannot be combined with KeepDays parameter.

-InputObject

Accepts database objects piped from Get-DbaDatabase to remove complete backup and restore history for those specific databases.
Use this for pipeline operations when working with filtered database collections or when combining with other dbatools database commands.

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

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.