Backup and recovery features for SQL Server databases

Mirrored backup

SQL Server 2005 allows you to create a mirrored backup. A mirrored backup allows you to create two or four identical copies of a backup file in case one of the collections is corrupted. Mirroring has the same content, so you can fix the file when a file is corrupted.

Suppose you have a mirrored set of 1 and a mirrored set of 2, and all two collections have full backup and transaction log backups. If a full backup of mirror set 1 has been corrupted, you can recover from the mirrored collection, and then continue to use the transaction log backups for the Mirrored collection 1.

Online recovery

You can also restore online, but don't get too excited. From the name, it seems that you can keep the database up and running and ensure that users log in to the database while you're recovering--but that's not the case. Online recovery allows you to restore an offline filegroup while keeping the database online. So you can guarantee that most of the database is working, but the filegroup you want to recover must be offline.

Note: To run this feature, you must use the SQL Server 2005 Enterprise Edition, and the main filegroup cannot be offline. In addition, you must make sure that your application can take the filegroup offline and still work. With careful planning, this feature is very useful, but many people may not use it.

Copy-only Backup

I think a very useful feature is a copy-only backup, which allows you to copy the backup process without disrupting 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 full backups and all the transaction logs that occur after a full backup in order to recover. This new feature allows you to create a copy of the backup only, and then use the normal full copy to achieve the recovery goal.

For different backups, there is no change in the way they are handled. For transaction log backups, you can also copy only the backups--no need to disrupt the order of other backup files. Any backups created through this option are marked as copy_only in the backup table

Partial backup

You might think that partial backups are the same as differential backups-but they are different. A partial backup is a backup of all filegroups except those that are marked as read-only (unless specified). For a read-only database, only the base filegroup is backed up. If you have a lot of static data in a read-only filegroup, it's much quicker to back up your database in this way.

Recovering from a previous version

One of the identical features reserved in SQL Server 2005 is the ability to recover the database from a previous release; You can restore database backups from SQL 7.0 and 2000. (However, there is still no way to restore the backup from version 6.5.) )

Note: System databases cannot be recovered from earlier versions, as are SQL Server 2005.