Skip to content

Latest commit

 

History

History
149 lines (106 loc) · 6.29 KB

README.md

File metadata and controls

149 lines (106 loc) · 6.29 KB
Raw

SqlBackupTools

Quality Gate Status Coverage Maintainability Rating Reliability Rating Security Rating Technical Debt

You need fast SQL Server backup & restore ?

SqlBackupTools will parallelize BACKUP RESTORE commands, and let you saturate your disk iops to achieve really fast recoveries.

This tool is used internally at Lucca to achieve low RPO log-shipping, while providing real time monitoring of all backup / restore operations.

Born initially to workaround limitations of the excellent BrentOzar's sp_AllNightLog code, SqlBackupTools will evolve to become the unique entry point for all backup & restore operations.

Features

  • Fast parallel restore operation
  • Native restore methods + BrentOzar's stored procedure support
  • Built upon Ola Hallengren backup convention & directory structure
  • Mail notifications
  • Slack notifications

Getting started

Basic usage

If you just want to restore backups, and leave databases in ONLINE state

SqlBackupTools.exe restore -h localhost -f \\NetworkShare\Backups\SQL-01

Slack notifications

SqlBackupTools provides convenient slack notifications, letting you know when some warnings are triggered.

Those warnings include :

  • RPO target miss
  • Deleted / missing backups
  • Technical problems

Slack notifications

Advanced usages

Main options :

  -h, --hostname                  Required. SQL Server hostname
  -f, --folder                    Required. Root backup folder
  -l, --login                     SQL Server login (leave blank for integrated security)
  -p, --password                  SQL Server password (leave blank for integrated security)
  -t, --threads                   Number of parallel threads
  --rpoLimit                      Limit in minutes before creating a RPO warning
  --timeout                       SQL Command timeout in seconds
  --logs                          Log folder

Filters + behavior toggles :

  --fullOnly                      Restore only FULL backups. If false, then everything is restored (FULL+LOG)
  --databaseName                  Filter on specific databases
  --ignoreDatabases               Exclude specific databases from all the process
  --continueLogs                  Whether or not you are continuing to restore logs after the database has already been restored without recovering it
  --runRecovery                   Whether or not to recover the database (RESTORE DATABASE WITH RECOVERY so that it is now usable)
  --reverseOrder                  Start with small databases instead of bigger ones

Support for BrentOzar's stored proc :

  -b, --brentozar                 Brentozar mode (use
sp_DatabaseRestore.sql script)
  --IgnoreAlreadyPresentInMsdb    Ignore LOG backups already present in msdb

Post-restore script execution :

  --postScripts                   Sql post scripts to execute
  --postScriptFilter              Execute script on databases starting with postScriptFilter

Advanced restore tuning : (use with caution)

  --maxTransferSize               RESTORE MAXTRANSFERSIZE option. Maximum value : 4194304
  --bufferCount                   RESTORE BUFFERCOUNT. Don't try too high values
  --noChecksum                    RESTORE NO_CHECKSUM. Ignore checksum while restoring.

Mail notifications :

  --smtp                          Smtp server to send email
  --email                         Email address to send email

Slack notifications :

  --slackSecret                   Slack token
  --slackChannel                  Slack channel
  --slackOnlyOnError              Send slack message only on warning or error

Backup architecture best practices

Best practices

  • Separate disk between SQL data and backups
  • Even better, use separate machines
  • Use the fastest disk available in terms of iops
  • Setup monitor and alerts on all operations

The 3-2-1 Backup Rule

This strategy promoted by US CERT is simple :

  • Keep 3 copies of any important file: 1 primary and 2 backups.
  • Keep the files on 2 different media types to protect against different types of hazards.
  • Store 1 copy offsite (e.g., outside your home or business facility).

At LuccaSoftware, we have the following architecture

  • Primary production datacenter :
    • Primary SQL Instance (continuous backup)
    • Backup file instance
    • Replica SQL Instance (continuous restore)
  • Secondary production datacenter (offsite):
    • Replica SQL Instance (continuous restore)
  • Off-network storage (offsite):
    • Blob storage on azure.

When SQL availability group aren't enough

At LuccaSoftware, we have a database-per-tenant tenancy strategy, and we are managing more than 1000 databases per cluster. SQL Server Availability groups aren't made for such high database count, so we currently achieve our RPO target by using a simple but efficient simple LOG shipping.

Roadmap

  • Backup operations
  • Restore DIFF support
  • Blob storage storage provider
  • Windows Service & continuous operations