Best Practices for Backing up Large critical databases

Data is very important for any organization or business. this article discusses few best practices to backup and restore the data of your application.

Maintainence of data is very important and is of high priority for any organization dealing with data in sql server database.

In an organization where the users expect no or very less down time of the application for maintenance operations, The database must be restored in as small amount of time as possible.

Below we are going to discuss some of the features of SQL Server which helps us in increasing the backup and restore operation speed so that the application is down for very less time and it does not affect the users.

1. Use file, file group backups and t-log backups as it allows only relevant data to be backed up or restored.


2. Use a mirrored media set which has 4 mirrors per media set. Using the mirrored media set, the backup operation writes to multiple groups of backup devices. Each single mirror set must use the same quantity and type of physical backup devices, and should have the same properties.

3. Avoid using the same physical disk that holds database files or Log files for backup purposes as it affects the performance and can reduce the recoverability of the plan.

4. Use low priority back up compression whose cpu usage is monitored by 'resource governor ' as the newly introduced backup compression feature increases cpu usage.

5. Use multiple backup devices for backup and write the backup to the backup devices at the same time. similarly we can restore the data from the backup in parallel.

6.Use snapshot backups which is the fastest backup created by using the split-mirror solution. It has minimal effect on the server and is useful for the database where the availability is important.