FULL Database Backups in SQL Server Step by Step Tutorial with Examples
Related Tutorials: SQL Server Database Backup Options>
Next Topic: Differential Database Backups
Page 8 / 16
You need to use FULL database backup to back up the whole database in SQL Server. A full database backup will include parts of transactional 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.
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 to 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 to 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 */
FROM DISK = 'C:\DBBackups\AdventureWorks.BAK'
Create a FULL Database Backup Using SQL Server Management Studio
For detailed information, see How to Create Full Database Backups in SQL Server Using SQL Server Management Studio.
Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.
- How to Create Full Database Backups in SQL Server Using SQL Server Management Studio
- Understanding SQL Server Recovery Model In Depth
- DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
- TRANSACTIONAL LOG Backups in SQL Server Step by Step Tutorial with Examples
- COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples
- Mirrored Backup in SQL Server Step by Step Tutorial with Examples
Last Updated On: Feb 12, 2014
Please leave your Valuable Comment or Let us know how this article helped you: