Backup and Restore with T-SQL

October 7, 2026

Scripted backups are repeatable and leave a record of exactly what was run. Always use CHECKSUM so damaged pages are caught when the backup is taken, not when you need to restore it.

Full backup

BACKUP DATABASE [YourDB]
TO DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_FULL_20261002.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10;

Differential backup

BACKUP DATABASE [YourDB]
TO DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_DIFF_20261002.bak'
WITH DIFFERENTIAL, CHECKSUM, COMPRESSION, STATS = 10;

Transaction log backup

BACKUP LOG [YourDB]
TO DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_LOG_20261002_1400.trn'
WITH CHECKSUM, COMPRESSION;

Copy-only backup for one-off copies

Use COPY_ONLY for an ad hoc backup, for example before a change or to refresh a test server. It does not reset the differential base, and a copy-only log backup does not break the log chain that the scheduled jobs (or DPM) rely on.

BACKUP DATABASE [YourDB]
TO DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_COPYONLY_20261002.bak'
WITH COPY_ONLY, CHECKSUM, COMPRESSION, STATS = 10;

Verify a backup file

RESTORE VERIFYONLY
FROM DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_FULL_20261002.bak'
WITH CHECKSUM;

VERIFYONLY confirms the backup is complete and readable and re-checks the page checksums. It is not a substitute for a real test restore followed by DBCC CHECKDB.

Find the backups needed for a restore

SELECT b.database_name, b.type, b.is_copy_only,
       b.backup_start_date, b.backup_finish_date,
       b.first_lsn, b.last_lsn, mf.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily mf ON b.media_set_id = mf.media_set_id
WHERE b.database_name = N'YourDB'
  AND b.backup_finish_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY b.backup_finish_date;

type D = full, I = differential, L = log. Each log backup’s first_lsn should equal the previous log backup’s last_lsn; a gap means the chain is broken.

Point-in-time restore (NEEDS APPROVAL)

Restore the last full backup, then the latest differential (if any), then every log backup in order, all WITH NORECOVERY. Stop at the required time with STOPAT, then recover the database.

RESTORE DATABASE [YourDB]
FROM DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_FULL_20261002.bak'
WITH NORECOVERY, REPLACE, STATS = 10;

RESTORE DATABASE [YourDB]
FROM DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_DIFF_20261002.bak'
WITH NORECOVERY;

RESTORE LOG [YourDB]
FROM DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_LOG_20261002_1300.trn'
WITH NORECOVERY;

RESTORE LOG [YourDB]
FROM DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_LOG_20261002_1400.trn'
WITH STOPAT = '2026-10-02T13:45:00', NORECOVERY;

RESTORE DATABASE [YourDB] WITH RECOVERY;

REPLACE overwrites an existing database with the same name. Rollback: none once started, so confirm the database name and the restore point before running it.
If you are restoring over a live database and need its latest transactions, take a tail-log backup first. This leaves the database in RESTORING state, so it is an outage:

BACKUP LOG [YourDB]
TO DISK = N'\\BackupServer\SQLBackups\YourDB\YourDB_TAIL_20261002.trn'
WITH NORECOVERY, CHECKSUM;

To restore under a different name or to different drives, add MOVE clauses (see Restoring a Database to a New Location).

From my SQL Server DBA handbook.

Setup

Clive Roberts