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 database full backup will include parts of transaction log so that if the need arises a database can be recovered completely by restoring full database backup.

This is Part 8 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

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.

This tutorial explains how to backup a SQL Database. For SQL Backup of Database, you can use the TSQL Command BACKUP DATABASE. One must take timely Database Backup in SQL Server to avoid data loss as a best practice.

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

Create a FULL Database Backup 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.

Related Articles

• SQL Server WHERE Clause T-SQL Tutorial with Examples
• SQL Server ORDER BY Clause T-SQL Tutorial with Examples
• SQL Server SELECT TOP Clause T-SQL Tutorial with Examples
• SQL SELECT | SQL QUERY | SQL SELECT Statement T-SQL Tutorial with Examples
• SERVERPROPERTY Function T-SQL Enhancement in SQL Server 2016
• FORMATMESSAGE Statement T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016
• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
• ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS SQL Server T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples