SQL Server Backup and Restore Tutorial Full Guide with All Backup Types

SQL Server backup and restore command used in real DBA work. Full backup, differential backup, log backup, tail log backup, copy only backup, split backup, mirrored backup, and full restore workflow. This guide helps you understand core SQL Server backup architecture, recovery models, file handling, and restore sequence for disaster recovery and migration cases. All commands are shown exactly as used in SQL Server. Use this for learning, interview preparation, and daily DBA tasks. Optimized for SQL Server search terms.

Commands
Full backup
BACKUP DATABASE MyDB TO DISK='F:\MyDB_full.bak' WITH INIT

Differential backup
BACKUP DATABASE MyDB TO DISK='F:\MyDB_diff.bak' WITH DIFFERENTIAL, INIT

Log backup
BACKUP LOG MyDB TO DISK='F:\MyDB_log.trn' WITH INIT

Tail log backup
BACKUP LOG MyDB TO DISK='F:\MyDB_tail.trn' WITH NO_TRUNCATE, INIT
Copy only backup
BACKUP DATABASE MyDB TO DISK='F:\MyDB_copyonly.bak' WITH COPY_ONLY, INIT

Split backup
BACKUP DATABASE MyDB TO DISK='F:\MyDB_part1.bak', DISK='F:\MyDB_part2.bak' WITH INIT

Mirrored backup
BACKUP DATABASE MyDB TO DISK='F:\MyDB_main.bak' MIRROR TO DISK='G:\MyDB_mirror.bak' WITH INIT

Restore full
RESTORE DATABASE MyDB FROM DISK='F:\MyDB_full.bak' WITH NORECOVERY

Restore differential
RESTORE DATABASE MyDB FROM DISK='F:\MyDB_diff.bak' WITH NORECOVERY

Restore log
RESTORE LOG MyDB FROM DISK='F:\MyDB_log.trn' WITH NORECOVERY

Restore tail log
RESTORE LOG MyDB FROM DISK='F:\MyDB_tail.trn' WITH NORECOVERY

Bring database online
RESTORE DATABASE MyDB WITH RECOVERY