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. 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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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!
