MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter

















Latest SQL Server Tips










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

Read Comments   |   Related Tutorials: SQL Server Database Backup Options > Next Topic: Differential Database Backups

Page 8 / 16


Click here to read this tutorial from the beginning...

Introduction

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.

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.

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.. Next Page..


Learn More...




Last Updated On: Feb 12, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: