How to Back Up and Restore SQL Server Databases on Windows Server 2022

Reliable database backups are the foundation of any SQL Server disaster recovery strategy. SQL Server 2022 provides flexible backup types, recovery models, compression, encryption, and cloud-integrated backup to Azure Blob Storage. This guide covers everything from the basic BACKUP DATABASE command to automated maintenance plans, the Ola Hallengren backup solution, point-in-time restores, and backup verification.

SQL Server Recovery Models

Before configuring backups, you must understand the three recovery models, as they determine which backup types are available and what level of data recovery is possible.

Simple recovery model does not retain transaction log records after a checkpoint. Only full and differential backups are supported. Point-in-time recovery is not possible; you can only recover to the end of the last backup. This model is appropriate for development, test, and databases that can tolerate losing work since the last backup (for example, a data warehouse refreshed from an ETL process).

Full recovery model retains all transaction log records until a log backup is taken. Full, differential, and transaction log backups are supported. Point-in-time restore to any moment covered by the log chain is possible. This model is required for production OLTP databases where minimal data loss is acceptable.

Bulk-Logged recovery model minimally logs bulk operations (BULK INSERT, SELECT INTO, CREATE INDEX, BCP) to reduce log growth during large data loads. Log backups are still required, but point-in-time recovery may not be possible during periods when bulk operations occurred. Use it temporarily during bulk load operations and switch back to Full recovery when the load is complete.

-- Check and set recovery model
SELECT name, recovery_model_desc FROM sys.databases;

ALTER DATABASE AppDatabase SET RECOVERY FULL;
ALTER DATABASE AppDatabase SET RECOVERY SIMPLE;
ALTER DATABASE AppDatabase SET RECOVERY BULK_LOGGED;

Full Backups

A full backup contains a complete copy of the database at the time the backup was taken, including enough of the transaction log to make the backup consistent. It is the foundation of any backup strategy; differential and log backups depend on a prior full backup.

-- Full backup to a local disk file with compression
BACKUP DATABASE AppDatabase
TO DISK = 'D:BackupsAppDatabase_FULL_20240517.bak'
WITH 
    INIT,
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    NAME = N'AppDatabase Full Backup';

-- Full backup with encryption (requires a database master key and certificate)
BACKUP DATABASE AppDatabase
TO DISK = 'D:BackupsAppDatabase_FULL_encrypted.bak'
WITH 
    COMPRESSION,
    ENCRYPTION (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = SQLBackupCert
    ),
    STATS = 10;

-- Full backup to multiple files (striped) for large databases and faster I/O
BACKUP DATABASE AppDatabase
TO DISK = 'D:BackupsAppDatabase_FULL_1.bak',
   DISK = 'D:BackupsAppDatabase_FULL_2.bak',
   DISK = 'D:BackupsAppDatabase_FULL_3.bak'
WITH COMPRESSION, INIT, STATS = 10;

Differential Backups

A differential backup contains only the data pages that have changed since the most recent full backup. Differentials are faster to take and restore than a full backup but require the base full backup during restore. They do not reset the log chain; log backups continue from the same log sequence number regardless of whether a differential was taken.

-- Differential backup
BACKUP DATABASE AppDatabase
TO DISK = 'D:BackupsAppDatabase_DIFF_20240517_1800.bak'
WITH DIFFERENTIAL, COMPRESSION, INIT, STATS = 10;

Transaction Log Backups

In Full recovery model, transaction log backups capture the log records generated since the last log backup. They truncate the inactive portion of the log, preventing uncontrolled log file growth. Regular log backups — typically every 15 to 60 minutes depending on RPO requirements — are essential for databases in Full recovery model.

-- Log backup
BACKUP LOG AppDatabase
TO DISK = 'D:BackupsAppDatabase_LOG_20240517_1815.trn'
WITH COMPRESSION, INIT, STATS = 10;

-- Copy-only log backup (does not break the log chain)
BACKUP LOG AppDatabase
TO DISK = 'D:BackupsAppDatabase_LOG_copyonly.trn'
WITH COPY_ONLY, COMPRESSION, INIT;

Copy-Only Backups

A copy-only backup is an independent backup that does not affect the normal backup sequence. Use it when you need a one-off backup for testing, cloning to a development environment, or transferring data — without disturbing the differential or log chain used by your production backup schedule.

-- Copy-only full backup
BACKUP DATABASE AppDatabase
TO DISK = 'D:BackupsAppDatabase_COPYONLY.bak'
WITH COPY_ONLY, COMPRESSION, INIT;

Restoring a Database

The restore sequence depends on what backups are available and your recovery target. A full restore from a full backup:

-- Restore a full backup (WITH RECOVERY brings it online)
RESTORE DATABASE AppDatabase
FROM DISK = 'D:BackupsAppDatabase_FULL_20240517.bak'
WITH RECOVERY, STATS = 10, REPLACE;

-- Restore to a new database name with different file paths
RESTORE DATABASE AppDatabase_DR
FROM DISK = 'D:BackupsAppDatabase_FULL_20240517.bak'
WITH RECOVERY,
     MOVE 'AppDatabase' TO 'E:SQLDataAppDatabase_DR.mdf',
     MOVE 'AppDatabase_log' TO 'F:SQLLogAppDatabase_DR.ldf',
     STATS = 10;

Point-in-Time Restore

Point-in-time restore lets you recover a database to a specific moment in time, provided you have an unbroken log chain from the last full backup to the desired recovery point. The sequence is: restore full backup with NORECOVERY, apply differential with NORECOVERY (if available), then apply each log backup in order with NORECOVERY until the final one, which is applied WITH RECOVERY and STOPAT.

-- Step 1: Restore full backup without recovering
RESTORE DATABASE AppDatabase
FROM DISK = 'D:BackupsAppDatabase_FULL_20240517.bak'
WITH NORECOVERY, STATS = 10, REPLACE;

-- Step 2: Apply differential backup (if any) without recovering
RESTORE DATABASE AppDatabase
FROM DISK = 'D:BackupsAppDatabase_DIFF_20240517_1800.bak'
WITH NORECOVERY, STATS = 10;

-- Step 3: Apply log backups in sequence without recovering
RESTORE LOG AppDatabase
FROM DISK = 'D:BackupsAppDatabase_LOG_20240517_1815.trn'
WITH NORECOVERY;

-- Step 4: Apply the final log backup, stopping at a specific point in time
RESTORE LOG AppDatabase
FROM DISK = 'D:BackupsAppDatabase_LOG_20240517_1900.trn'
WITH RECOVERY, STOPAT = '2024-05-17T18:45:00';

Tail-Log Backup

Before restoring a database that is still online or partially accessible after a failure, take a tail-log backup to capture any log records not yet backed up. This minimizes data loss by extending the log chain to the point of failure.

-- Tail-log backup (WITH NORECOVERY takes the database offline for restore)
BACKUP LOG AppDatabase
TO DISK = 'D:BackupsAppDatabase_TAILLOG.trn'
WITH NORECOVERY, COMPRESSION, INIT;

-- Tail-log backup without taking the database offline (when the DB is still accessible)
BACKUP LOG AppDatabase
TO DISK = 'D:BackupsAppDatabase_TAILLOG.trn'
WITH CONTINUE_AFTER_ERROR, COMPRESSION, INIT;

Verifying Backups

Always verify backups after creation. RESTORE VERIFYONLY reads and validates the backup without actually restoring data:

-- Verify backup integrity
RESTORE VERIFYONLY
FROM DISK = 'D:BackupsAppDatabase_FULL_20240517.bak';

-- Verify with CHECKSUM (detects page-level corruption if CHECKSUM was used during backup)
RESTORE VERIFYONLY
FROM DISK = 'D:BackupsAppDatabase_FULL_20240517.bak'
WITH CHECKSUM;

-- Get backup file header information
RESTORE HEADERONLY
FROM DISK = 'D:BackupsAppDatabase_FULL_20240517.bak';

-- Get file list from a backup
RESTORE FILELISTONLY
FROM DISK = 'D:BackupsAppDatabase_FULL_20240517.bak';

SQL Server Agent Backup Jobs

SQL Server Agent (available in Standard and Enterprise) allows you to schedule backup jobs. Create a job with a T-SQL step to run the backup command on a schedule:

-- Create a SQL Agent job for nightly full backups
EXEC msdb.dbo.sp_add_job @job_name = N'Nightly Full Backup - AppDatabase';

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Nightly Full Backup - AppDatabase',
    @step_name = N'Run Full Backup',
    @command = N'BACKUP DATABASE AppDatabase 
                 TO DISK = N''D:BackupsAppDatabase_FULL_'' + CONVERT(varchar, GETDATE(), 112) + ''.bak''
                 WITH COMPRESSION, INIT, CHECKSUM, STATS = 10;',
    @database_name = N'master';

EXEC msdb.dbo.sp_add_schedule
    @schedule_name = N'Nightly 11PM',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 230000;

EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'Nightly Full Backup - AppDatabase',
    @schedule_name = N'Nightly 11PM';

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'Nightly Full Backup - AppDatabase';

Ola Hallengren Backup Solution

The Ola Hallengren SQL Server Maintenance Solution is a widely adopted, open-source set of stored procedures for backups, integrity checks, and index maintenance. It provides far more flexibility than SQL Server Maintenance Plans, including per-database configuration, retention-based cleanup, backup file naming with timestamps, and support for compression, encryption, and backup to URL.

After downloading and running the installer script (MaintenanceSolution.sql) from https://ola.hallengren.com, call the backup procedures as SQL Agent job steps:

-- Full backup of all user databases
EXEC dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = N'D:Backups',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @Checksum = 'Y',
    @CleanupTime = 72,  -- Delete backups older than 72 hours
    @LogToTable = 'Y';

-- Differential backup of all user databases
EXEC dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = N'D:Backups',
    @BackupType = 'DIFF',
    @Compress = 'Y',
    @CleanupTime = 48,
    @LogToTable = 'Y';

-- Transaction log backup of all user databases
EXEC dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = N'D:Backups',
    @BackupType = 'LOG',
    @Compress = 'Y',
    @CleanupTime = 24,
    @LogToTable = 'Y';

Backup to URL (Azure Blob Storage)

SQL Server 2022 can back up directly to Azure Blob Storage, providing off-site backup without a separate file copy step. You need an Azure storage account, a container, and a SAS token or managed identity credential.

-- Create a credential for Azure Blob using a SAS token
CREATE CREDENTIAL [https://mystorageacct.blob.core.windows.net/sqlbackups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2022-11-02&ss=b&srt=o&sp=rwdlacuptfx&se=2025-12-31T00:00:00Z&st=2024-01-01T00:00:00Z&spr=https&sig=YOURSASTOKEN';

-- Backup to Azure Blob Storage
BACKUP DATABASE AppDatabase
TO URL = 'https://mystorageacct.blob.core.windows.net/sqlbackups/AppDatabase_FULL_20240517.bak'
WITH COMPRESSION, STATS = 10;

-- Restore from Azure Blob
RESTORE DATABASE AppDatabase
FROM URL = 'https://mystorageacct.blob.core.windows.net/sqlbackups/AppDatabase_FULL_20240517.bak'
WITH RECOVERY, STATS = 10;

With a comprehensive backup strategy combining frequent log backups, nightly differentials, weekly fulls, and off-site copies to Azure Blob Storage, you can confidently meet even demanding RTO and RPO objectives for your SQL Server databases on Windows Server 2022.

Links

Newsletter

Contact

How to Set Up DNS Policies on Windows Server 2022