SQL Full backup

A full backup is the bases for all other types of backup. A Full backup starts the Log Sequence Number (LSN) Chain. It provides a complete copy of the database at the time the backup is taken. Below shows how to create a Full backup:

BACKUP DATABASE db_name TO DISK = 'full.bak'

There are times where additional Full backups are needed in addition to an existing Full Recover backup strategy. To prevent re-basing LSN and impacting Differential and Log backups Full backup should be used with ‘WITH COPY_ONLY’ option.

BACKUP DATABASE your_database TO DISK = 'full.bak' WITH COPY_ONLY

Note: Full backups with ‘COPY_ONLY’ are not compatible with subsequent Differential or Log backup until a new Full backup is taken (from restored ‘COPY_ONLY’ database).

source – Full backup
source – understanding LSN
source – LSN and SQL backups

Leave a Reply

Your email address will not be published. Required fields are marked *