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.
