Backup and restore enhancements in SQL Server 2005

When any new software is released, you will find new features that you don't know it exists or that you don't know you need. Many of the features of backup and storage in SQL Server 2000 remain intact in SQL Server 2005, but there are some new features worth considering.

Mirrored backup

SQL Server 2005 lets you create a mirrored backup. A mirrored backup allows you to create 2 or 4 copies of the same backup file in case a collection is corrupted. Mirroring has the same content, so you can repair the files when they are damaged.

If you have a mirrored set of 1 and a mirrored set of 2, two are fully backed up and transaction log backups. If a full backup of the mirrored set 1 is damaged, you can use a full backup from the mirrored set 2 to be stored again, and then continue with the transaction log backups from the mirror set and 1.

Online Re-storage

You can also have the ability to store online again, but don't get too excited. By name, it sounds like you can keep the entire database online, run, and use the database while you re storing it-but that's not the case. Online re-storage lets you re-store offline filegroups while keeping the database online. So you can keep the database running at maximum level, but the filegroups you want to store must be offline.

Note: To run this feature, you must use the SQL Server 2005 Enterprise Edition, and the base filegroup cannot be offline. In addition, you have to make sure that your application can take the filegroup offline, and that it will work. After careful planning, this feature can be very useful, but it has not been used by a large number of people.

Copy-only Backup

Another feature I think is very useful is a copy-only backup, which allows you to make a copy in the middle of the backup sequence without disturbing the order of other backup files. With SQL Server 2000, if you run a special full backup in the middle of the day, you must use a full backup and a log of all the transactions that occurred after the full backup in order to store it again. This new feature allows you to create a copy of the backup only, and then use a normal full backup to accomplish the purpose of the RE storage.

For different backups, there is no difference in how they are handled. For transaction log backups, you can also have the ability to copy only backups-still without disrupting the order of other backup files. In the backup table, any backups created with this option are marked as copy-only (copy_only).

Partial backup

You might think that there is no difference between a partial backup and a differential backup-but there are differences. A partial backup backs up all filegroups except those that are marked as read-only, unless specifically specified. For a read-only database, only basic filegroup backups are available. If you have a lot of static data in a read-only filegroup, this may be the quickest way to back up your database.

Re-store from a previous version

One of the capabilities that has not changed in SQL Server 2005 is the ability to store databases from previous releases; You can store database backups from SQL 7.0 and 2000. (That is, for example, you cannot store backups from 6.5.) )

Note: You cannot re-store the system database from a previous version, as in SQL Server 2005.

I highlighted several new features of backup and storage in SQL Server 2005. The SQL Server September CTP is the latest version. Download a copy, and then start learning the new features, and the way you use them.