SQL Server backup strategy, automation and restore

Sets up backups for an installed SQL Server instance (Linux or Windows; single instance, FCI or AG replicas) and recovers from them. Backup: choice of recovery model, weekly full + daily differential + log backup every n minutes, COMPRESSION, CHECKSUM, certificate encryption, retention and scheduling (SQL Server Agent T-SQL jobs or Ola Hallengren; Task Scheduler / cron on Express), one-off COPY_ONLY backup, backup verification and chain check. Recovery: full / differential / log chain restore, point-in-time restore with STOPAT (PITR), marked transaction, restore under another name or to another server WITH MOVE, tail-log + restore after data file loss, page restore, master / msdb / model restore, login mapping (orphaned users) and loading the certificate for an encrypted backup.

Pick what you need from the scenario card; you can also type an error number (e.g. 824, 4305, 33111, 18456) into the catalog search to go straight to the matching scenario. To export a table or query result to a file (data movement, not a backup), see Exporting with bcp.

Topology: 1 server, Failover Cluster Instance or Always On AG replicas (restore to another server: source + target)

Scenarios

• Set up a backup strategy (scheduled backups + retention) — New server or a database with no regular backups: full / differential / log jobs, retention and monitoring are set up.
• Take a one-off backup (e.g. COPY_ONLY before a change) — A single backup before a change, patch or upgrade, or to copy to a test environment; COPY_ONLY does not break the chain.
• Verify backups (history, VERIFYONLY, test restore) — To prove the backups can actually be restored: backup history, VERIFYONLY, HEADERONLY / FILELISTONLY and a test restore to a temporary database + CHECKDB.
• Is the backup chain intact? (LSN gap, missing file) — Before a restore or as a regular audit: rebuilds the chain from msdb history and finds LSN gaps, files missing from disk and backups taken out of band. Changes nothing.
• Restore a database from backup (chain, STOPAT / STOPATMARK, MOVE / REPLACE, another server) — Data was deleted or wrongly updated, a copy of the database is needed or it is moving to another server; the instance is running. Full + differential + log chain, a specific point in time or a marked transaction, a different name / directory, up to the last moment with a tail-log backup.
• Database will not open: data file missing or damaged (tail-log + restore) — The database is RECOVERY_PENDING or SUSPECT and the .mdf/.ndf cannot be read, but the instance is running: first take a tail-log backup WITH NO_TRUNCATE, then restore up to the last moment. The database is unavailable until the procedure finishes.
• A few pages are corrupt (Msg 823 / 824): page restore — Queries or CHECKDB fail on specific pages (msdb.dbo.suspect_pages): only the damaged pages are restored from backup. On Enterprise/Developer the database stays online; on Standard/Express it is offline for a short time.
• Restore the master database (single-user mode) — master is damaged, or logins / linked servers / server settings were lost: the instance is started in single-user mode (-m), master is restored and SQL Server shuts itself down. Requires downtime; if the instance does not start at all, the system databases are rebuilt first.
• Restore the msdb or model database — Jobs, backup history, Database Mail (msdb) or the template for new databases (model) are damaged / lost: restored WITH REPLACE from a backup taken on the same build; Agent is stopped briefly.
• Users cannot connect after a restore (orphaned users, login mapping) — The database was moved or restored to another server and the application gets 'Login failed': logins are transferred from the source with their SID and password hash, and remaining users are mapped to logins. The database stays online.
• Encrypted backup cannot be restored (certificate missing, Msg 33111) — The backup (or the backup of a TDE database) is encrypted with a certificate that does not exist on this server: the thumbprint is identified, the certificate is loaded with its private key and the backup is verified. Existing databases are not touched.

Before you start

• RPO (acceptable data loss) and RTO (acceptable downtime) are agreed in writing with the business. Chosen plan: …at most … minutes of data loss…return to the last full/differential backup (SIMPLE: up to one day of data loss)…; restore time will be measured with a test restore of the largest database.
• SQL Server is running; mssql-tools18 (sqlcmd) is installed and … is sysadmin. Before running the script, read the password into a variable without echoing it: read -rsp 'Password: ' SQLCMDPASSWORD; export SQLCMDPASSWORD and sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• PowerShell was opened as Administrator (Start → "PowerShell" → right-click → Run as administrator; the title shows Administrator:) with a Windows account that is sysadmin in SQL Server; sqlcmd is on the PATH.
• The backup directory (…) is on a disk separate from the data disk and has enough free space for at least a week of backups (roughly: used data size × 2 + daily change × number of days; usually 3–5 times less with compression).
• The backup directory (…) is on a disk separate from the data disk and has enough free space for at least a week of backups (roughly: used data size × 2 + daily change × number of days; usually 3–5 times less with compression).
• Database sizes are known; the backup window (the time a full backup takes) does not overlap business hours.
• The method for copying backups off the server (another server, NAS, object storage, backup software) is decided. A backup on the same disk is useless if the disk or server is lost.
• The SQL Server version you restore to is the same as or newer than the version the backup was taken on. A backup taken on a newer version cannot be restored to an older one (e.g. 2022 → 2019 is not possible).
• The application team was informed and the application is stopped: all connections to the database being restored over are dropped and the database is unavailable during the restore.
• The data (…) and log (…) directories exist on the target, are owned by mssql and have free space for the file sizes of the database being restored (not the backup size; the Size values in FILELISTONLY).
• The data (…) and log (…) directories exist on the target, the SQL Server service account has write permission and there is free space for the file sizes of the database being restored.
• The source (…) is reachable from the target server (…) …over SSH (root or a user with sudo)…over the SMB admin share (…)…; the backup files can be copied.

Phases of the guide

• Current state — List databases and recent backups
• Backup strategy — Prepare the backup directory · Prepare the backup directory · Set the recovery model (…) · Set the AG backup preference (…) · Check the preferred replica · Create the certificate and back it up (encryption) …
• One-off backup — …Take a COPY_ONLY full backup…Take a full backup…Take a differential backup…Take a log backup… (…) · Check the backup history (msdb.dbo.backupset)
• Restore — Find the restore chain from the backup history · Copy the backup files to the target server · Copy the backup files to the target server · Load the certificate on the target server · Load the certificate on the target server · Inspect the backup files (VERIFYONLY, HEADERONLY, FILELISTONLY) …
• Backup verification — Check the backup history (msdb.dbo.backupset) · Inspect the backup file (VERIFYONLY, HEADERONLY, FILELISTONLY) · Run a test restore and DBCC CHECKDB
• Backup chain check — Diagnosis: state of the chain (database + msdb history) · Are the backup files on disk? · Are the backup files on disk? · Verify every backup in the chain (VERIFYONLY) · Afterwards: take a new full backup if the chain is broken
• Data file missing / damaged: tail-log + restore — Diagnosis: state of the database and its files · Diagnosis: are the files on disk, is the log file intact? · Diagnosis: are the files on disk, is the log file intact? · Safety net: copy the remaining files · Safety net: copy the remaining files · Take a tail-log backup (NO_TRUNCATE) …
• Page restore (corrupt pages) — Diagnosis: find the corrupt pages (suspect_pages, CHECKDB) · Diagnosis: which table / index does the page belong to? · Safety net: take a log backup, stop the log backup jobs · Build the restore sequence from msdb history (PAGE) · Take a tail-log backup (NORECOVERY): the database goes offline · Restore the damaged pages and apply the log backups (NORECOVERY) …
• master restore — Diagnosis: backup file, version and what will be lost · Diagnosis: why does SQL Server not start? · Diagnosis: why does SQL Server not start? · Rebuild the system databases (master does not start) · Rebuild the system databases (master does not start) · Start SQL Server in single-user mode …
• msdb / model restore — Diagnosis: state, version and backup file · Safety net: back up the current … database · Stop SQL Server Agent · Restore the … database (REPLACE) · Start SQL Server Agent · Verify
• Login mapping (orphaned users) — Diagnosis: find the orphaned users (target) · Generate the login script on the source (SID + password hash) · Generate the login script on the source (SID + password hash) · Copy the login script to the target and run it · Copy the login script to the target and run it · Map the remaining users to logins (ALTER USER WITH LOGIN) …
• Encrypted backup: load the certificate — Diagnosis: certificates and master key on this server · Diagnosis: thumbprint of the certificate file · Diagnosis: thumbprint of the certificate file · Load the certificate with its private key · Load the certificate with its private key · Verify: the backup can now be read …