SQL Server Database Recovery

SQL server database recovery can be achieved when you have carefully considered how to react in a crisis. As with any kind of file recovery, backing up is still your best protection from losing data in the event of a breakdown. The backup and restore capability offered within the system offers a certain amount of protection for your data. However there are other considerations to be made when backing up and restoring.

• How long will take to run?
• When is the appropriate time to run it?

Restoring Process
Backing up can be quick and painless however restoring data can be a time consuming effort. Getting the database back in production is always the number one priority. For instance, if one of the disks on the server or the disk containing the transaction log were lost it would not take a whole lot of time to fix compared to a disastrous situation where the whole server was destroyed.

During the restoring process users will not be able to connect to the database. Taking this into consideration you need to decide which type of database recovery model will best suit your backup needs.

When you have chosen a recovery model you need to plan a backup schedule that will support the level of recovery you will need.

A Faster Backup
During a backup some SQL server commands cannot be made

• ALTER DATABASE with either the ADD FILE or REMOVE FILE options cannot be made
• Cannot shrink a database
• Cannot run a CREATE INDEX statement
• Cannot make SELECT INTO, bulk load and so on

Backup performance is better achieved if you perform backups on the local hard disk first and then copy backup file(s) to the tape later.

The faster the backup operation, the less impact there will be on the server when the backup occurs. Keep in mind that the more often you back up the less data you will lose if the database becomes corrupt.

Backups should be performed on multiple backup devices. This way the SQL Server creates a separate backup thread for each backup device.

Perform backups on a physical disk array. The more disks in array, the more quickly the backup will be made.

Schedule backups during slow production periods so that precious company time is not jeopardized.

Backup Procedures
Full backups take the longest to perform compared to differential and incremental types however they take the least time to restore.

Incremental backups perform faster than full or differential backups but take longer to restore.

Differential backups should be used when users regularly update the same data. It captures only those data pages that have changed after the last backup.

Separate your database into different files and file groups. Backing up a particular file or file group will be achieved more efficiently and will have less of an impact on the server when backing up.