SQL Server Full Database Backups

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

Benefits 

Backing up your SQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss.

With valid backups of a database, you can recover your data from many failures, such as:

  • Media failure
  • User errors, for example, dropping a table by mistake.
  • Hardware failures, for example, a damaged disk drive or permanent loss of a server.
  • Natural disasters.

Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up Always On Availability Groups or database mirroring, and archiving.

Backup Types

differential backup : A data backup that is based on the latest full backup of a complete or partial database or a set of data files or file groups (the differential base) and that contains only the data that has changed since that base.

log backup : A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)

full backup : A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

 **Place the database and backups on separate devices. Otherwise, if the device containing the database fails, your backups will be unavailable. Placing the data and backups on separate devices also enhances the I/O performance for both writing backups and the production use of the database.

Create a Full Database Backup 

-After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
-Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.
-Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears
Backup-SQL-Server-Database-Using-SSMS-In the Database list box, verify the database name. You can optionally select a different database from the list.
In the Backup type list box, select Full.
Back-up-Database-Using-SQL-Server-Management-Studio-press OK

Database Backups Under the simple Recovery model

Under the simple recovery model, after each backup, the database is exposed to potential work loss if a disaster were to occur. The work-loss exposure increases with each update until the next backup, when the work-loss exposure returns to zero and a new cycle of work-loss exposure starts. Work-loss exposure increases over time between backups. The following illustration shows the work-loss exposure for a backup strategy that uses only full database backups.

Shows work-loss exposure between database backups

 

Database Backups Under the Full Recovery model

For databases that use full and bulk-logged recovery, database backups are necessary but not sufficient. Transaction log backups are also required. The following illustration shows the least complex backup strategy that is possible under the full recovery model.

 

Series of full database backups and log backups

>> http://msdn.microsoft.com/en-us/library/ms186289.aspx

Advertisements

One thought on “SQL Server Full Database Backups

  1. Pingback: Adventures In Disaster Recovery; How To Lose Clients And Make Enemies. | Second Star Technologies

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s