FULL Database Backups in SQL Server Step by Step Tutorial with Examples
FULL database backup is used to back up the whole database in SQL Server. A full database backup will include parts of transaction log so that if the need arises a database can be recovered completely by restoring full database backup.
Full database backup represent the database at the time when the backup has finished. However, as the database size increases the full database backup takes more time to finish and it will also require more storage space. Hence for larger databases one must supplement a full database backup with a series of differential database backups and even transactional log backups if the database is in FULL or BULK-LOGGED recovery model. Transactional log backup is not allowed when the database is in SIMPLE recovery model this is by design.
A full backup is a complete backup of your SQL Server database. It backs up all of the objects of the database: tables, procedures, functions, views, indexes, etc.
You can create a full SQL Server database backup using SQL Server Management Studio, Transact-SQL, or PowerShell.
**A full backup is the foundation for every other backup type; it must be performed at least once before you can run any of the other types of backups.
Database Backups Under Simple Recovery Model
When your database is in SIMPLE recovery model, after every successfully FULL or DIFFERENTIAL database backup, the database is exposed to potential data loss if a disaster happens. The amount of work loss increases with every single INSERT, DELETE or an UPDATE command until the very next backup, when the work loss exposure returns to zero and immediately a new cycle of work loss starts. More the delay for the backups more the risk towards loss of data. Below illustration from MSDN explains this scenario in detail when you just relay upon FULL database backups.
Image: Credits MSDN
Database Backups Under the Full Recovery Model
When your database is in FULL and BULK-LOGGED recovery model, database backups are very much needed. However, one must initiate regular transaction log backups to reduce the amount of data loss. Below illustration shows in detail the least complex backup strategy which is possible under the full recovery model.
Image: Credits MSDN
One can take the full backup of the database either by using SQL Server Management Studio or by using TSQL commands. Let us take a look at both the options one by one in detail.
Create Full Database Backup Using TSQL Script
Update the below TSQL script with the database name and location where database backup needs to be written to and then execute the script to take the full back up of AdventureWorks database.
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\DBBackups\AdventureWorks.BAK'
WITH NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25
/* Verify the Database Backup File Once the Backup has completed successfully */
RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\AdventureWorks.BAK'
GO