Tip

SQL Server 2005 partial backup options

By Serdar Yegulalp, Contributor

In SQL Server 2005, I have been especially intrigued by the backup and restore enhancements because I run a remotely-hosted SQL Server with a database that supports 400 MB to 2.5 GB at any given time.

    Requires Free Membership to View

If I could better manage backups on such a machine, I wanted to know about it! Ideally, I wanted to back up only the changes made to a database in a given period of time. Now SQL Server 2005 offers several different ways to go about doing just that.

Partial vs. differential backups

One of the biggest changes is the introduction of partial backups. As described in Microsoft's SQL Server 2005 documentation, partial backups work by filegroup; they will back up all filegroups except those marked as read only (unless you say otherwise), as well as the primary filegroup. If you have a database with a lot of unchanging data segregated into different filegroups, you can use partial backups to only back up filegroups with data changes. If the whole database is read only, only the primary filegroup will be backed up.

Obviously, partial backups only work if you have a database with filegroups in the first place, so if you don't have such an arrangement you may want to think about re-segregating your data into filegroups first. As a general rule, data should be segregated into different filegroups whenever possible for the best possible parallelization.

The other big benefit to working with partial backups is that they work on databases regardless of the recovery model. They'll work for databases that use either the simple or full recovery models, depending on how much protection you need.

Differential backups, available in previous versions of SQL Server, record everything that changes in the primary and read-write filegroups -- but only the changes. However, a differential partial backup, new to SQL Server 2005, will record only changes to the selected filegroups from the last full partial backup. This saves time if you're copying out the backups, but expect a much longer and tedious restore process because you'll need that many more files to perform the operation.

Choosing your backup method

Your backup decision should be based on two things: your needs and the size of the database you're backing up. The best environment for partial and partial-differential backups is one where you have quick and ready access to all parts of the backup needed to perform the restore, and the amount of data that changes is minimal, but crucial. If a lot of data is changing constantly, you may be better served by making complete, non-incremental backups.

One SQL Server 2005 backup option that may also affect your selection is the COPY_ONLY function. When you make a full backup with COPY_ONLY, the system's normal backup and restore cycle is not affected. This means you can make a full backup without changing the archive log point or truncating the transaction log. This is extremely useful if you want to run an immediate one-off database backup that can be sent to someone or archived somewhere, but you don't want to interfere with your usual run of nightly backups. COPY_ONLY can be used in conjunction with differential and differential-partial backups when needed.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

This was first published in July 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.