Backup and Restore Commands

This article provides a reference for the Transact‑SQL (T‑SQL) commands used to back up and restore a Microsoft SQL Server database.

Backup Command Overview ▼

The BACKUP DATABASE and BACKUP LOG statements create a backup set of a database or transaction log.

BACKUP DATABASE <database_name>
TO DISK = 'path\file_name.bak'
WITH 
    [ COMPRESSION ]
    , [ FORMAT ]
    , [ INIT ]
    , [ STATS = n ];

Common Options

Option	Description
`COMPRESSION`	Compresses the backup set.
`FORMAT`	Creates a new media set, overwriting existing media header.
`INIT`	Overwrites existing backup sets on the media.
`STATS = n`	Reports progress every n percent.

Restore Command Overview ▼

The RESTORE DATABASE and RESTORE LOG statements restore a previously created backup.

RESTORE DATABASE <database_name>
FROM DISK = 'path\file_name.bak'
WITH 
    [ RECOVERY ]
    , [ NORECOVERY ]
    , [ STANDBY = 'undo_file_path' ]
    , [ REPLACE ]
    , [ STATS = n ];

Common Options

Option	Description
`RECOVERY`	Brings the database online after restore.
`NORECOVERY`	Leaves the database in restoring state for additional restores.
`STANDBY = 'undo_file_path'`	Leaves the database read‑only and creates an undo file.
`REPLACE`	Overwrites the existing database.
`STATS = n`	Reports progress every n percent.

Examples

Full Database Backup

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH COMPRESSION, STATS = 10;

Transaction Log Backup

BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH NOFORMAT, INIT, STATS = 10;

Restoring a Full Backup

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH RECOVERY, STATS = 10;

Point‑In‑Time Restore (Differential + Log)

-- Restore full backup
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY;

-- Restore differential backup
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Diff.bak'
WITH NORECOVERY;

-- Restore log backup up to a specific point in time
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH STOPAT = '2024-10-01T12:30:00', RECOVERY;

For more detailed scenarios, see Backup Strategies and Restore Options.