![\"full](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/full-backup1.png\")
<\/p>\nThe 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\u00a0result because this option applies\u00a0only when you restore the transaction log. Please see how a\u00a0periodic full backup works in the picture below:<\/p>\n
<\/p>\n
The good thing about database\u00a0backup is that transactions continue running\u00a0during the backup process, although database I\/O operations may slow them down. However, since the\u00a0transaction log cannot be truncated at this time you may face a problem when a huge number of changes in the database during the\u00a0full backup make the transaction log grow rapidly and, therefore, occupy your free disk space.<\/p>\n
A full database backup backs up\u00a0all 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:<\/p>\n
![\"How](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Untitled-drawing.png\")
<\/p>\n
00:00<\/strong> Full backup starts. It forces a database checkpoint and this flushes\u00a0all 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.<\/p>\n 00:01<\/strong> The backup process\u00a0reads Data Page 1 and adds it to the backup file.<\/p>\n 00:02<\/strong> The backup process\u00a0reads Data Page 2 and simultaneously\u00a0some changes in Data Page 1 take place. These changes are added to the Transaction Log as well. Now the\u00a0backup contains Data Page 2 and the old version of Data Page 1 (as it was at 00:01).<\/p>\n 00:03<\/strong> 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.<\/p>\n A full database backup doesn’t contain\u00a0all records from\u00a0your 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\u00a0the latest active transaction or from the checkpoint created by the backup as shown in the picture below:<\/p>\n Transaction A started before the full backup began, made some changes after the checkpoint and committed\u00a0before the backup completed. In this case the full backup includes all transaction log records starting from\u00a0the 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\u00a0changes during the database recovery process.<\/p>\n The above picture shows that\u00a0transaction A started and committed before\u00a0the full backup began. Therefore, since all the changes made by the transaction were applied on the checkpoint the\u00a0backup will contain transaction log records added after the checkpoint.<\/p>\nWhat Transaction Log Records Get into Full Backup<\/h2>\n
![\"Latest](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Latest-Transaction.png\")
<\/p>\n![\"LSN](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/LSN-Checkpoint.png\")
<\/p>\nHow to Make a Full Backup<\/h2>\n