Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2008 : Managing Backups - Recovery Models & Backup Architecture

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/5/2011 4:29:25 PM

1. Recovery Models

The first thing you need to be aware of when determining a backup strategy is the database recovery model. A database recovery model determines how transactions are logged and therefore impacts the type of backup operations that can be performed on a database. You can set a database to use one of three different recovery models that provide different levels of transaction protection.

  • Full: The full recovery model provides the highest level of data protection by logging all transactions. It is usually the preferred recovery model for all production databases. The full recovery model also gives you the most flexibility in your backup strategy by allowing you to perform any type of backup available in SQL Server.

  • Bulk-logged: The bulk-logged recovery model is much like the full recovery model except that certain operations are minimally logged, reducing the space that would be required to store the entire transaction in the log file. You will also receive performance gains on bulk operations, since only the end result is logged and not the entire operation. As with the full recovery model, you can also perform any type of backup for a database that is bulk-logged. The limitation is that you may not be able to do a pointin- time recovery if a bulk operation has been performed. The following are considered bulk operations and are minimally logged when using the bulk-logged recovery model.

    • SELECT INTO

    • BCP

    • BULK INSERT

    • OPENROWSET with the BULK rowset provider

    • CREATE INDEX

    • ALTER INDEX REBUILD

    • DROP INDEX (The page deallocation is fully logged, but if a heap rebuild is necessary, it will be minimally logged.)

    • Partial updates to large data types using the .WRITE clause

  • Simple: The simple recovery model does not offer any form of logged data protection and is generally not used in production environments that require point-in-time recovery since the last full backup. The transaction log only holds the data until a checkpoint has occurred and the data is written to disk. For this reason, you cannot perform transaction log backups on a database that is using the simple recovery model. As with the bulk-logged recovery model, bulk operations are also minimally logged using the simple recovery model.

You can also use sort of a hybrid method between the full and bulk-logged recovery models. You can set the database to use the full recovery model, and then change it to use bulk-logged when performing specific bulk operations that may not perform well using the full recovery model. You should back up the log before switching to the bulk-logged recovery model, and then back up once again after switching back to the full recovery model. This will minimize the logs that cannot be restored using the point-in-time recovery option.

To check the current recovery model, you can use the DATABASEPROPERTYEX system function using the RECOVERY parameter, as shown in Listing 1. To change a database recovery model, you can use the ALTER DATABASE statement specifying FULL, BULK_LOGGED, or SIMPLE, also shown in Listing 1.

Example 1. Code to View and Change the Database Recovery Model
USE master
GO

--Select the database recovery model
SELECT DATABASEPROPERTYEX('AdventureWorks2008','RECOVERY');

--Change the database recovery model
ALTER DATABASE AdventureWorks2008 SET RECOVERY FULL;

2. Backup Architecture

Backups are grouped into logical containers known as media sets. A media set is one or more tapes or disk files that contain an ordered collection of backups. A media set must be either completely on tape or completely on disk. You can't create a media set that spans multiple device types. A new media set is created the first time a backup is performed or by using the FORMAT option in the BACKUP statement. The FORMAT option deletes the old media header and creates a new header for the media set. The header contains information about the media type and media family.

Media family refers to the type of device that was used to create the media set. If you use multiple backup devices in a media set, your backup set will contain multiple media families. For example, if you back up your database to two disk files, the media set will contain two media families that are both marked as disk devices.

Media sets are made up of one or more backup sets. A backup set consists of all the information gathered during an individual backup process. Each time a successful backup is performed, a backup set is added to the media set. The backup set is evenly distributed across all the devices that make up the media set.

Look at the sample code in Listing 2. The BACKUP command creates a media set named AdventureWorks2008_MediaSet that contains two disk devices, meaning the media set contains two media families. Each time the BACKUP command is executed, a backup set is evenly written across the two devices. The illustration shown in Figure 1 assumes you have executed the BACKUP command three times to create three backup sets.

Example 2. Code to Create a Media Set with Multiple Devices
USE master
GO
BACKUP DATABASE [AdventureWorks2008] TO
DISK = 'C:\Backup\AdventureWorks2008_1.bak',
DISK = 'D:\Backup\AdventureWorks2008_2.bak'
WITH MEDIANAME = 'AdventureWorks2008_MediaSet'
GO

Figure 1. AdventureWorks2008 backup media set created by the code in Listing 2

Most of the time this architecture goes unnoticed because a database is backed up to a media set on a single disk to a single file; so we tend to think of it as just a backup file. If you think of backups in terms of backup sets, media sets, and media families, it will make a lot more sense when reviewing many of the backup options available in SQL Server.
Other -----------------
- Microsoft Dynamics CRM 2011 : Closing an Opportunity & Reopening an Opportunity
- Microsoft Dynamics CRM 2011 : Using Opportunities to Forecast Potential Sales
- Integrating Dynamics NAV and the Microsoft Office system (part 3) - Using extensibility with NAV 2009 SP1
- Integrating Dynamics NAV and the Microsoft Office system (part 2) - Exporting documents to MS Excel and MS Word
- Integrating Dynamics NAV and the Microsoft Office system (part 1) - MS SharePoint interface
- Microsoft Dynamics NAV : Installing the RoleTailored client for Dynamics NAV
- BizTalk 2009 : Exposing a WCF Service (part 2) - Creating the WCF Test Client
- BizTalk 2009 : Exposing a WCF Service (part 1) - Securing Requests with Message-Level Certificate Encryption
- BizTalk 2009 : WS-AtomicTransaction Support
- SharePoint 2010 : Securing Information - Securing Lists
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer