SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

FULL Database Backups in SQL Server Step by Step Tutorial with Examples

Previous Page.. Begin Tutorial.. Next Page..

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.

Database Backups Under Simple Recovery Model

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.

Database Backups 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

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.

Previous Page.. Begin Tutorial.. Next Page..

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement