The simplest kind of SQL Server backup is a full database backup. It provides a complete copy of the database but allows restoring the database only to a point-in-time when the backup was made. Even if you add the “WITH STOPAT=<time or log sequence number>” option to restore command you will not get the expectedĀ result because this option appliesĀ only when you restore the transaction log. Please see how aĀ periodic full backup works in the picture below:
The good thing about databaseĀ backup is that transactions continue runningĀ during the backup process, although database I/O operations may slow them down. However, since theĀ transaction log cannot be truncated at this time you may face a problem when a huge number of changes in the database during theĀ full backup make the transaction log grow rapidly and, therefore, occupy your free disk space.
How Full Backup Works
A full database backup backs upĀ all data files and active part of the transaction log. The active part of the transaction log is necessary to restore database to a transactionally consistent point as shown in the picture below:
00:00 Full backup starts. It forces a database checkpoint and this flushesĀ all dirty pages in the disk. Please note, if you use a simple recovery model the log is truncated, meaning that all committed transactions are removed from the log automatically. In full and bulk-logged recovery models it does not occur.
00:01 The backup processĀ reads Data Page 1 and adds it to the backup file.
00:02 The backup processĀ reads Data Page 2 and simultaneouslyĀ some changes in Data Page 1 take place. These changes are added to the Transaction Log as well. Now theĀ backup contains Data Page 2 and the old version of Data Page 1 (as it was at 00:01).
00:03 Data reading operation is complete and followed by Transaction Log reading. Transaction Log contains changes made in Data Page 1 (at 00:01) and they are added into the backup to be applied to Data Page 1 later during the restore process recovery stage.
What Transaction Log Records Get into Full Backup
A full database backup doesn’t containĀ all records fromĀ your transaction log. Instead it chooses only those records that are necessary to make the restored copy of the database transactionally consistent. Those records begin either fromĀ the latest active transaction or from the checkpoint created by the backup as shown in the picture below:
Transaction A started before the full backup began, made some changes after the checkpoint and committedĀ before the backup completed. In this case the full backup includes all transaction log records starting fromĀ the latest active transaction. This implies that the full backup includes the whole transaction A with all changes that were made after the checkpoint to apply thoseĀ changes during the database recovery process.
The above picture shows thatĀ transaction A started and committed beforeĀ the full backup began. Therefore, since all the changes made by the transaction were applied on the checkpoint theĀ backup will contain transaction log records added after the checkpoint.
How to Make a Full Backup
To make a full backup you canĀ use T-SQL command:
BACKUP DATABASE your_database TO DISK = 'full.bak'
Another way to backup a database is to use SQL Server Management Studio (SSMS): right click on the database you want to backup, select “Tasks”, then “Back up…”. Choose “Full” backup type, add a backup destination and click “OK”.
However, usingĀ SqlBackUpAndFtpĀ utility, that makes full database backup every 24 hours by default, is perhaps the easiest way of creating SQL Server backups. Moreover, you can benefit from using it’s advanced backup schedulingĀ if you require more sophisticatedĀ backup scenarios.
