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

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

No SQL Server deployment is complete without a tested backup and restore strategy. Backups are meaningless without verified restores, and even the best hardware fails. SQL Server 2022 on Windows Server 2025 provides a rich set of backup types, PowerShell cmdlets, and SQL Server Agent scheduling capabilities that together enable point-in-time recovery — the ability to restore a database to any moment captured by the transaction log chain. This guide covers the full backup spectrum: full, differential, and transaction log backups; the correct restore sequence; restoring to a different server or instance; verifying backup integrity; scheduling automated jobs with SQL Server Agent; monitoring backup history through the msdb system database; and integrating Ola Hallengren’s industry-standard maintenance scripts for production environments.

Prerequisites

• SQL Server 2022 installed on Windows Server 2025.
• The database to be backed up is in Full recovery model for point-in-time recovery (Simple recovery model supports only full and differential backups).
• A dedicated backup volume or UNC share with sufficient space and correct NTFS permissions for the SQL Server service account.
• The SqlServer PowerShell module (Install-Module SqlServer) for PowerShell-based operations.
• SQL Server Management Studio (SSMS) 20+ for GUI-based jobs and monitoring.

Step 1 — Backup Types and Recovery Strategy

SQL Server supports three primary backup types that form a recovery chain:

• Full backup: A snapshot of the entire database at a point in time. The baseline for any restore sequence. Large and slow to produce — typically scheduled weekly or nightly.
• Differential backup: Contains all changes since the last full backup. Smaller than a full, faster to restore than replaying many logs. Typically scheduled daily or every few hours.
• Transaction log backup: Contains all transactions committed since the last log backup. Enables point-in-time recovery. Typically scheduled every 15–60 minutes. Log backups also truncate the log file, preventing unbounded growth.

A typical production strategy for an OLTP database:

• Sunday 02:00 — Full backup
• Mon–Sat 02:00 — Differential backup
• Every 30 minutes — Transaction log backup

Step 2 — Full Database Backup with T-SQL

-- Full backup with compression and checksum verification
BACKUP DATABASE [SalesDB]
    TO DISK = 'G:SQLBackupSalesDBSalesDB_FULL_20260517_020000.bak'
    WITH
        FORMAT,                      -- Overwrite existing media set
        INIT,                        -- Initialize the media set
        NAME = 'SalesDB Full Backup 2026-05-17',
        DESCRIPTION = 'Weekly full backup',
        COMPRESSION,                 -- Compresses backup (reduce size by 60-80%)
        CHECKSUM,                    -- Writes page checksums for integrity verification
        STATS = 10;                  -- Progress updates every 10%
GO

To stripe the backup across multiple files for faster I/O (parallel write):

BACKUP DATABASE [SalesDB]
    TO  DISK = 'G:SQLBackupSalesDBSalesDB_FULL_01.bak',
        DISK = 'H:SQLBackupSalesDBSalesDB_FULL_02.bak',
        DISK = 'I:SQLBackupSalesDBSalesDB_FULL_03.bak'
    WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;
GO

Step 3 — Differential and Transaction Log Backups

-- Differential backup (much faster than full, smaller size)
BACKUP DATABASE [SalesDB]
    TO DISK = 'G:SQLBackupSalesDBSalesDB_DIFF_20260517_020000.bak'
    WITH DIFFERENTIAL,
         FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;
GO

-- Transaction log backup (enables point-in-time recovery)
BACKUP LOG [SalesDB]
    TO DISK = 'G:SQLBackupSalesDBSalesDB_LOG_20260517_023000.trn'
    WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;
GO

Step 4 — Backup with PowerShell

Import-Module SqlServer

$server     = "SQL2025-N1"
$database   = "SalesDB"
$backupRoot = "G:SQLBackupSalesDB"
$timestamp  = Get-Date -Format "yyyyMMdd_HHmmss"

# Full backup
Backup-SqlDatabase -ServerInstance $server -Database $database `
    -BackupFile "$backupRootSalesDB_FULL_$timestamp.bak" `
    -BackupAction Database `
    -CompressionOption On `
    -Initialize `
    -TrustServerCertificate

# Differential backup
Backup-SqlDatabase -ServerInstance $server -Database $database `
    -BackupFile "$backupRootSalesDB_DIFF_$timestamp.bak" `
    -BackupAction Database `
    -Incremental `
    -CompressionOption On `
    -Initialize `
    -TrustServerCertificate

# Log backup
Backup-SqlDatabase -ServerInstance $server -Database $database `
    -BackupFile "$backupRootSalesDB_LOG_$timestamp.trn" `
    -BackupAction Log `
    -CompressionOption On `
    -Initialize `
    -TrustServerCertificate

Write-Host "Backups completed: $timestamp"

Step 5 — Verify Backup Integrity

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

-- Verify a single backup file
RESTORE VERIFYONLY
    FROM DISK = 'G:SQLBackupSalesDBSalesDB_FULL_20260517_020000.bak'
    WITH CHECKSUM;
GO

-- Inspect backup file header (metadata about the backup set)
RESTORE HEADERONLY
    FROM DISK = 'G:SQLBackupSalesDBSalesDB_FULL_20260517_020000.bak';
GO

-- Inspect the file list inside the backup
RESTORE FILELISTONLY
    FROM DISK = 'G:SQLBackupSalesDBSalesDB_FULL_20260517_020000.bak';
GO

Step 6 — Restore a Database (Point-in-Time Recovery)

The restore sequence is always: Full → Differential → Log backups (in order), with all but the final restore using WITH NORECOVERY:

-- Step 1: Restore the full backup (leave DB in restoring state)
RESTORE DATABASE [SalesDB_Restored]
    FROM DISK = 'G:SQLBackupSalesDBSalesDB_FULL_20260517_020000.bak'
    WITH NORECOVERY, REPLACE,
         MOVE 'SalesDB'     TO 'D:SQLDataSalesDB_Restored.mdf',
         MOVE 'SalesDB_log' TO 'E:SQLLogSalesDB_Restored_log.ldf',
         STATS = 10;
GO

-- Step 2: Apply the differential backup (still NORECOVERY)
RESTORE DATABASE [SalesDB_Restored]
    FROM DISK = 'G:SQLBackupSalesDBSalesDB_DIFF_20260517_020000.bak'
    WITH NORECOVERY, STATS = 10;
GO

-- Step 3: Apply each log backup in sequence
RESTORE LOG [SalesDB_Restored]
    FROM DISK = 'G:SQLBackupSalesDBSalesDB_LOG_20260517_023000.trn'
    WITH NORECOVERY, STATS = 10;
GO

RESTORE LOG [SalesDB_Restored]
    FROM DISK = 'G:SQLBackupSalesDBSalesDB_LOG_20260517_030000.trn'
    WITH NORECOVERY, STATS = 10;
GO

-- Step 4: Apply the last log with STOPAT for point-in-time, then RECOVERY
RESTORE LOG [SalesDB_Restored]
    FROM DISK = 'G:SQLBackupSalesDBSalesDB_LOG_20260517_033000.trn'
    WITH RECOVERY,
         STOPAT = '2026-05-17T03:14:00',   -- Stop at specific moment
         STATS = 10;
GO

Step 7 — Restore to a Different Server or Instance

-- On the target server (SQL2025-DR), restore using a UNC path from the source backup share
RESTORE DATABASE [SalesDB]
    FROM DISK = '\SQL2025-N1SQLBackupSalesDBSalesDB_FULL_20260517_020000.bak'
    WITH RECOVERY, REPLACE,
         MOVE 'SalesDB'     TO 'D:SQLDataSalesDB.mdf',
         MOVE 'SalesDB_log' TO 'E:SQLLogSalesDB_log.ldf',
         STATS = 10;
GO

-- Fix orphaned users after restore to a different server
-- SQL logins lose their SID mapping; use sp_change_users_login
USE [SalesDB];
GO
EXEC sp_change_users_login 'Auto_Fix', 'AppServiceAccount';
GO

-- For Windows logins, simply recreate the user mapping
DROP USER [JaneSmith];
CREATE USER [JaneSmith] FOR LOGIN [NEWDOMAINjane.smith];
GO

Step 8 — Schedule Backups with SQL Server Agent

USE [msdb];
GO

-- Create a job for nightly full backups
EXEC sp_add_job
    @job_name = N'DBA - SalesDB Full Backup',
    @description = N'Weekly full backup of SalesDB',
    @category_name = N'Database Maintenance';

-- Add a job step that runs the T-SQL backup command
EXEC sp_add_jobstep
    @job_name = N'DBA - SalesDB Full Backup',
    @step_name = N'Run Full Backup',
    @command = N'BACKUP DATABASE [SalesDB]
TO DISK = ''G:SQLBackupSalesDBSalesDB_FULL_'' + CONVERT(VARCHAR,GETDATE(),112) + ''.bak''
WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;',
    @on_success_action = 1,  -- Quit with success
    @on_fail_action = 2;     -- Quit with failure

-- Schedule the job (Sunday 02:00)
EXEC sp_add_schedule
    @schedule_name = N'Weekly Sunday 2AM',
    @freq_type = 8,           -- Weekly
    @freq_interval = 1,       -- Sunday
    @active_start_time = 20000; -- 02:00:00

EXEC sp_attach_schedule
    @job_name = N'DBA - SalesDB Full Backup',
    @schedule_name = N'Weekly Sunday 2AM';

EXEC sp_add_jobserver @job_name = N'DBA - SalesDB Full Backup';
GO

Step 9 — Monitor Backup History with msdb

-- View recent backup history for a database
SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.backup_size / 1048576.0 AS backup_size_mb,
    bs.compressed_backup_size / 1048576.0 AS compressed_size_mb,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    bmf.physical_device_name,
    bs.has_backup_checksums,
    bs.is_password_protected
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
    ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'SalesDB'
ORDER BY bs.backup_start_date DESC;
GO

-- Check when databases were last backed up (identify gaps)
SELECT
    d.name AS database_name,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full,
    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE d.database_id > 4  -- Exclude system databases
GROUP BY d.name
ORDER BY d.name;

Step 10 — Ola Hallengren’s Maintenance Solution

Ola Hallengren’s open-source SQL Server Maintenance Solution (ola.hallengren.com) is the industry standard for automated backup management. It creates stored procedures (DatabaseBackup, DatabaseIntegrityCheck, IndexOptimize) and SQL Agent jobs. Download and run MaintenanceSolution.sql in SSMS, then invoke backups like this:

-- Full backup of all user databases with 30-day retention
EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES',
    @Directory = 'G:SQLBackup',
    @BackupType = 'FULL',
    @Verify = 'Y',
    @CleanupTime = 720,       -- Delete files older than 720 hours (30 days)
    @CheckSum = 'Y',
    @Compress = 'Y',
    @LogToTable = 'Y';       -- Log results to CommandLog table for auditing

-- Log backup for all user databases
EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES',
    @Directory = 'G:SQLBackup',
    @BackupType = 'LOG',
    @Verify = 'Y',
    @CleanupTime = 48,
    @CheckSum = 'Y',
    @Compress = 'Y',
    @LogToTable = 'Y';

Conclusion

A resilient backup strategy for SQL Server 2022 on Windows Server 2025 combines three backup types — full, differential, and transaction log — scheduled at appropriate intervals to meet your recovery time objective (RTO) and recovery point objective (RPO). Critically, backups must be tested through actual restores, not just verified with RESTORE VERIFYONLY. Schedule quarterly restore drills to a separate server and document the elapsed time. Monitor backup history through the msdb catalog views so that alerting fires immediately when a job misses its schedule. Whether you use native T-SQL, PowerShell cmdlets, SQL Server Agent, or Ola Hallengren’s maintenance solution, the principles are the same: automate, verify, monitor, and test. Your backup strategy is only as good as your last successful restore.