Minimizing Backup and Recovery Times in Mission-Critical Environments
Mission-critical environments often require that the database be available continuously, or for extended periods of time with minimal downtime. Therefore, the duration of unexpected situations, such as a hardware failure, that require the database to be restored need to be kept as short as possible. Additionally, mission-critical databases are often large, requiring longer periods of time to back up and restore. Microsoft SQL Server offers several methods for increasing the speed of backup and restore operations, thereby minimizing the effect on users during both operations.
Using multiple backup devices simultaneously allows backups to be written to all devices at the same time. Similarly, the backup can be restored from multiple devices at the same time.
Using a combination of database, differential database, and transaction log backups to minimize the number of backups that need to be applied to bring the database to the point of failure.
Using file and file group backups and transaction log backups, allowing only those files that contain the relevant data to be backed up or restored, rather than backing up the entire database.
To create a backup of a SQL database
In SQL Server Enterprise Manager, expand a server group, and then expand a server.
Expand Databases, right-click the database you want to back up, point to All Tasks, and then click Backup Database.
On the General tab, in the Name box, type the backup set name.
Optionally, in Description, enter a description for the backup set.
Under Backup, select Database - complete.
Under Destination, click Add to add an existing backup device or to create a new backup device.
Or, click Remove to remove a backup device from the list of backup devices to be used.
In the SQL Server Backup dialog box, under Overwrite, do the following:
Use this To do this Append to media Append the backup to any existing backups on the backup device. Overwrite existing media Overwrite any existing backups on the backup device. Schedule Optionally, select to schedule the backup operation for later or periodic execution. Optionally, click the Options tab and select from these backup options:
Verify backup upon completion. Causes the backup to be verified when backed up.
Eject tape after backup. Causes the tape to be ejected when the backup operation has completed. Available only with tape devices.
Check media set name and backup set expiration. Causes the backup media to be checked to prevent accidental overwrites. In Mediasetname, enter the name of the media that should be used for the backup operation. Leave blank when specifying only the backup set expiration.
If you are using the backup media for the first time, or if you want to change an existing media label, under Media set labels, select Initialize and label media, and enter the media set name and media set description. (The media can only be initialized and labeled when overwriting the media.)