Technical Architects Zone (TAZ)

SQL Server Backup Scripts

Full, Differential, Transaction Log, Copy-Only, Validation & DBA Utility Scripts

Introduction

A reliable SQL Server backup strategy is one of the most important responsibilities of a DBA. Backups protect databases against accidental deletion, corruption, failed deployments, infrastructure failures, and disaster-recovery scenarios.

This page brings together practical T-SQL scripts for common SQL Server backup and validation activities, including full, differential, transaction log, copy-only, striped, tail-log, backup-history, and recovery-model checks.

Important: A successful backup job is not the same as a proven recovery strategy. Regularly perform complete test restores in a separate SQL Server environment.

1. Full Database Backup 2. Differential Backup 3. Transaction Log Backup 4. Copy-Only Backup 5. Backup to Multiple Files 6. Verify a Backup 7. Tail-Log Backup 8. Backup All User Databases 9. Check Backup History 10. Check Recovery Model 11. Table-Level Safety Copy 12. Example Production Backup Strategy

1. Full Database Backup

A full database backup provides the primary data backup used as the foundation for many SQL Server recovery sequences.

BACKUP DATABASE MyDatabase
TO DISK = 'D:\SQLBackup\MyDatabase_Full.bak'
WITH
    INIT,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

COMPRESSION can reduce backup size and storage I/O, with additional CPU usage.
CHECKSUM enables backup checksums and additional page verification during backup.
STATS = 10 reports approximate progress at 10% intervals.
INIT overwrites existing backup sets on the target media, so use it deliberately.

Caution: Be careful with INIT. Verify that the target file does not contain backup sets that must be retained.

2. Differential Backup

A differential backup contains extents changed since its differential base, normally the most recent conventional full database backup.

BACKUP DATABASE MyDatabase
TO DISK = 'D:\SQLBackup\MyDatabase_Diff.bak'
WITH
    DIFFERENTIAL,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

A differential restore normally requires its corresponding full backup followed by the selected differential backup.

3. Transaction Log Backup

Transaction log backups are fundamental to point-in-time recovery when using the FULL or BULK_LOGGED recovery model and maintaining an appropriate log-backup chain.

BACKUP LOG MyDatabase
TO DISK = 'D:\SQLBackup\MyDatabase_Log.trn'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

Remember: Simply changing a database to FULL recovery is not enough. Establish the recovery sequence and maintain regular transaction log backups.

4. Copy-Only Backup

A copy-only full backup is useful when an ad-hoc full backup is required without changing the normal differential base.

BACKUP DATABASE MyDatabase
TO DISK = 'D:\SQLBackup\MyDatabase_CopyOnly.bak'
WITH
    COPY_ONLY,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

This can be useful before troubleshooting, migrations, controlled deployments, or other one-time activities where the regular backup strategy should remain unchanged.

5. Backup to Multiple Files

SQL Server can stripe a backup across multiple backup devices. This can improve throughput when the underlying storage architecture provides sufficient independent I/O capacity.

BACKUP DATABASE MyDatabase
TO
    DISK = 'D:\SQLBackup\MyDatabase_1.bak',
    DISK = 'E:\SQLBackup\MyDatabase_2.bak',
    DISK = 'F:\SQLBackup\MyDatabase_3.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

Important: All files are part of the same striped backup set. Keep every stripe together because the restore normally requires all media families.

6. Verify a Backup

RESTORE VERIFYONLY
FROM DISK = 'D:\SQLBackup\MyDatabase_Full.bak'
WITH CHECKSUM;
GO

Very important: RESTORE VERIFYONLY provides useful validation but does not prove that a complete recovery operation will succeed. Regularly perform actual test restores.

7. Tail-Log Backup

A tail-log backup attempts to capture transaction log records that have not yet been backed up before beginning an appropriate recovery sequence.

BACKUP LOG MyDatabase
TO DISK = 'D:\SQLBackup\MyDatabase_TailLog.trn'
WITH
    NORECOVERY,
    CHECKSUM,
    STATS = 10;
GO

The correct tail-log options depend on the failure scenario. NO_TRUNCATE should not be added automatically to every tail-log backup.

8. Backup All Online User Databases

The following example performs a full backup of each online user database and creates a separate timestamped backup file.

DECLARE
    @DatabaseName sysname,
    @BackupFile nvarchar(4000),
    @SQL nvarchar(max);

DECLARE DatabaseCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE database_id > 4
  AND state_desc = 'ONLINE'
  AND source_database_id IS NULL
ORDER BY name;

OPEN DatabaseCursor;

FETCH NEXT FROM DatabaseCursor
INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @BackupFile =
        N'D:\SQLBackup\' +
        @DatabaseName +
        N'_' +
        CONVERT(char(8), GETDATE(), 112) +
        N'_' +
        REPLACE(CONVERT(char(8), GETDATE(), 108), ':', '') +
        N'.bak';

    SET @SQL =
        N'BACKUP DATABASE ' +
        QUOTENAME(@DatabaseName) +
        N' TO DISK = N''' +
        REPLACE(@BackupFile, '''', '''''') +
        N''' WITH COMPRESSION, CHECKSUM, STATS = 10;';

    PRINT @SQL;

    EXEC sys.sp_executesql @SQL;

    FETCH NEXT FROM DatabaseCursor
    INTO @DatabaseName;

END;

CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor;

Production recommendation: For enterprise environments, use a proven SQL Server Agent or backup framework with error handling, notifications, retention, cleanup, validation, and regular restore testing.

9. Check Last Backup Dates

Displaying full, differential, and transaction log backups separately provides a much better operational view than displaying only the most recent backup of any type.

SELECT
    d.name AS DatabaseName,

    MAX(CASE
        WHEN b.type = 'D'
        THEN b.backup_finish_date
    END) AS LastFullBackup,

    MAX(CASE
        WHEN b.type = 'I'
        THEN b.backup_finish_date
    END) AS LastDifferentialBackup,

    MAX(CASE
        WHEN b.type = 'L'
        THEN b.backup_finish_date
    END) AS LastLogBackup

FROM sys.databases AS d

LEFT JOIN msdb.dbo.backupset AS b
    ON d.name = b.database_name

WHERE d.database_id > 4

GROUP BY
    d.name

ORDER BY
    d.name;

A recent full backup alone does not confirm that the transaction-log backup schedule is healthy.

10. Check Database Recovery Models

SELECT
    name,
    recovery_model_desc,
    state_desc
FROM sys.databases
ORDER BY name;

Recovery Model	Log Backups	Typical Consideration
SIMPLE	Not part of a normal log-backup chain	Used where transaction-log point-in-time recovery is not required
FULL	Yes	Supports log backups and point-in-time recovery when the chain is maintained
BULK_LOGGED	Yes	Special-purpose recovery model with additional considerations for minimally logged operations

11. Table-Level Safety Copy

Important terminology: SELECT INTO can create a copy of table data, but this is not a SQL Server database backup.

A table-level copy can still be useful before a controlled cleanup, data-cleansing activity, or corrective update.

IF OBJECT_ID('dbo.Employee_bckp', 'U') IS NULL
BEGIN

    SELECT *
    INTO dbo.Employee_bckp
    FROM dbo.Employee;

END
ELSE
BEGIN

    THROW 50001,
          'dbo.Employee_bckp already exists.',
          1;

END;
GO

Validate the Data Copy

SELECT COUNT_BIG(*) AS SourceCount
FROM dbo.Employee;

SELECT COUNT_BIG(*) AS BackupCopyCount
FROM dbo.Employee_bckp;

SELECT INTO does not reproduce all characteristics of the source table. Indexes, triggers, foreign keys, permissions, and other objects or attributes require separate consideration.

12. Example Production Backup Strategy

There is no single backup schedule suitable for every SQL Server production database. The design should start from the business Recovery Point Objective (RPO) and Recovery Time Objective (RTO).

An example starting design for a database using FULL recovery could look like this:

Frequency / Event	Action	Purpose
Weekly	Full Backup	Establish a data backup baseline
Daily	Differential Backup	Reduce the amount of work required during restore
Every 15 minutes	Transaction Log	Example for a 15-minute RPO requirement
Before selected changes	Copy-Only Full	Ad-hoc recovery point without changing the differential base
Regularly	Test Restore	Prove actual recoverability

If the business requires an RPO of 15 minutes, scheduling transaction log backups every 15 minutes is only the beginning. Monitor failed jobs, backup duration, backup storage, and the health of the log-backup chain.

Production Backup Checklist

Define business RPO and RTO requirements.
Select the appropriate SQL Server recovery model.
Schedule full, differential, and transaction log backups according to recovery requirements.
Use CHECKSUM where appropriate.
Monitor SQL Server Agent backup failures.
Monitor backup duration and storage consumption.
Verify backup retention requirements.
Protect backup files from unauthorized modification or deletion.
Maintain required TDE certificates and encryption keys.
Perform complete restore tests regularly.
Include DBCC CHECKDB in the broader integrity strategy.
Document and rehearse the disaster-recovery runbook.

Key Takeaways

Full backups provide an important foundation for database recovery.
Differential backups capture changed extents relative to the differential base.
Transaction log backups support point-in-time recovery under the appropriate recovery model.
Copy-only full backups provide an ad-hoc recovery point without changing the differential base.
Striped backups can improve throughput when the storage architecture supports parallel I/O.
RESTORE VERIFYONLY is useful, but an actual test restore provides stronger recovery assurance.
Tail-log backups can preserve the latest log records in appropriate recovery scenarios.
A SELECT INTO table copy is not a substitute for a SQL Server database backup.
The correct backup frequency should be derived from business RPO and RTO requirements.

Conclusion

SQL Server backups are more than a collection of BACKUP commands. A production backup strategy must connect business recovery requirements with the correct recovery model, backup types, scheduling, retention, encryption, monitoring, and restore procedures.

From a DBA perspective, the most important question is not "Did the backup job succeed?" The real question is: "Can the database be restored within the required RPO and RTO?"

A backup strategy should therefore be proven through actual restore exercises, documented recovery sequences, failure simulations, and continuous monitoring.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone