Backups are central to business continuity: they protect against accidental deletion, sabotage, natural disasters, malware, and other threats, and can even keep a company alive after a disaster. Beyond recovery, backups accelerate everyday work by enabling quick database resets for developers, powering automated tests in DevOps pipelines, and supporting migrations or full server rebuilds. This chapter shows how dbatools streamlines creating, managing, and testing backups across an entire SQL Server estate—from legacy versions to Azure—covering the three core backup types (full, differential, and log) and typical cadences, while noting that enterprise-grade scheduling is best handled by established maintenance solutions.
The toolkit makes common tasks simple to start and flexible to extend. You can back up every database on an instance with a minimal command, direct output to specific storage paths (ensuring the service account has write permissions), enable compression, send backups to NUL for performance testing, or generate T‑SQL with script‑only output. dbatools supports encrypted backups, reading backup metadata, and assembling restore-ready backup sets. It includes first-class options for cloud and Linux scenarios: backing up directly to Azure Storage via SQL Credentials using Shared Access Signatures (recommended) or Access Keys, and running backups against SQL Server on Linux and in Docker (including alternate ports and container-relative paths or mounted shares). For insight and speed, you can query msdb’s backup history with Get-DbaDbBackupHistory, inspect file headers with Read-DbaBackupHeader, and auto-build restore chains with Get-DbaBackupInformation.
Operational hygiene is covered too: prune aged files with Find-DbaBackup and standard PowerShell deletion, and—most critically—verify backups. Test-DbaLastBackup automates best practices by finding the latest full/diff/log chain, restoring to a chosen destination with safe file/name remapping, running DBCC CHECKDB, then dropping the test database and reporting results. Offload these checks to a separate server to protect production, and persist outcomes with Write-DbaDataTable for analytics and audit evidence. Note that dbatools focuses on native SQL Server backups rather than third‑party formats. Combined, these capabilities help you implement resilient backup strategies, validate recoverability regularly, and be prepared for rapid, reliable restoration.
Hands-on Lab
- Backup all databases on your SQL Server to a non-default path
- Backup a database and pipe the results directly to
Read-DbaBackupHeader
- Find transaction log (trn) backups older than 21 days using
Find-DbaBackup
FAQ
What are the three SQL Server backup types and a common schedule?
Full, Differential, and Log. A common cadence is: Full weekly, Differential daily, and Log at least hourly. Full backs up everything, Differential captures changes since the last Full, and Log backs up the transaction log for point‑in‑time recovery and to keep log size under control.How do I back up all databases on an instance with dbatools?
RunBackup-DbaDatabase -SqlInstance sql01. By default, this creates Full backups for every database to the instance’s default backup directory with timestamped file names. To choose a location, add -Path \\nas\sqlbackups and ensure the SQL Server service account can write there. Many teams back up to centralized storage and then replicate off‑site for ransomware resilience. dbatools doesn’t schedule jobs; consider Ola Hallengren’s Maintenance Solution (Install-DbaMaintenanceSolution) or Windows Task Scheduler (for SQL Express).How can I generate backup T‑SQL scripts without running the backups?
Use-OutputScriptOnly. You can also filter databases first, then output or save the scripts: $dbs = Get-DbaDatabase -SqlInstance sql01 | Where-Object Name -match 'factory' then $dbs | Backup-DbaDatabase -Path \\nas\sqlbackups -OutputScriptOnly. Pipe to Set-Clipboard or Out-File if needed.How do I back up directly to Azure using a Shared Access Signature (SAS)?
1) Generate a SAS and remove any leading “?”. 2) Create a SQL Credential where Name is the container URL and Identity isSHARED ACCESS SIGNATURE: New-DbaCredential -SqlInstance sql01 -Name https://acmecorp.blob.core.windows.net/backups -Identity 'SHARED ACCESS SIGNATURE' -SecurePassword (Get-Credential).Password. 3) Run the backup: Backup-DbaDatabase -SqlInstance sql01 -AzureBaseUrl https://acmecorp.blob.core.windows.net/backups/ -Database MyDb -BackupFileName MyDb.bak. No -AzureCredential is required when using SAS credentials.How do I back up to Azure with Access Keys, and when is this needed?
Access Keys are mainly for older versions (e.g., SQL Server 2012/2014) that don’t support SAS. Create a credential where Identity is the storage account name (e.g., “acmecorp”), then supply that credential during backup:New-DbaCredential -SqlInstance sql01 -Name AzureAccessKey -Identity acmecorp -SecurePassword (Get-Credential).PasswordBackup-DbaDatabase -SqlInstance sql01 -AzureCredential AzureAccessKey -AzureBaseUrl https://acmecorp.blob.core.windows.net/backups/ -Database MyDbHow do I back up SQL Server running in Docker/Linux?
Connect using the published port (dbatools accepts colon syntax and converts it for SQL Server):Backup-DbaDatabase -SqlInstance localhost:14433 -SqlCredential sqladmin -BackupDirectory /tmp. The backup path is inside the container. To write to a host share, back up to the container’s mount point (e.g., /shared/backups).How can I read details from existing backup files and assemble a restore set?
UseRead-DbaBackupHeader -SqlInstance sql01 -Path \\nas\sql\backups\MyDb.bak to retrieve metadata (server, file list, LSNs, etc.). To scan a folder and build the correct Full/Diff/Log chain, use Get-DbaBackupInformation -SqlInstance sql01 -Path \\nas\sql\backups\sql01 and pipe to Restore-DbaBackup if desired.How can I see backup history and the last backup chain for databases?
SQL Server stores backup history in msdb. Query it viaGet-DbaDbBackupHistory, e.g. -Database pubs. To quickly collect the latest Full/Diff/Log chain for all databases on a server, use Get-DbaDbBackupHistory -SqlInstance sql01 -Last.How do I find and remove old backup files?
UseFind-DbaBackup to locate backups older than a retention period, then pipe to Remove-Item. Example: Find-DbaBackup -Path \\nas\sql\backups -BackupFileExtension bak -RetentionPeriod 90d | Remove-Item -Verbose. For log backups, set -BackupFileExtension trn.How can I verify that backups are restorable and the data is sound?
RunTest-DbaLastBackup. It finds the latest Full/Diff/Log chain, restores it (with new names/paths), runs DBCC CHECKDB, then drops the test database and reports the outcome. Example: Test-DbaLastBackup -SqlInstance sql01,sql02 -Destination sqltest -DataDirectory R:\ -LogDirectory L:\. Results can be saved to a table with Write-DbaDataTable. Note: this supports native SQL Server backups only; consider running on a non‑production server.
Learn dbatools in a Month of Lunches ebook for free
