Best practices for backing up and restoring SQL Server 2008

Best practices for backing up and restoring SQL Server 2008:

■ Define and document an SLA relevant to the SQL Server 2008 environment.

■ Test the backup and recovery plan on a periodic basis and also before production to validate that it is operational and the SLA can be met.

■ Select the appropriate recovery model for all systems and user databases. Use the Full recovery model for mission-critical databases that need to be restored to the point of failure in the event of a disaster.

■ Isolate database and transaction log files on separate spindles for recovery purposes.

■ Save the backups locally on a redundant disk drive separate from the online databases and back up to tape on a regular basis.

■ If database or transaction log backups are stored locally on disk, do not store them on the same volumes as the database and transaction log files. If a drive or volume failure occurs, both the files and backups could be lost.

■ For retention and recovery purposes, the backups stored to disk should be committed to tape.

■ Commit to doing frequent backups if the system is an online transaction processing (OLTP) environment. An OLTP database is also known as the databases residing in the SQL Server Database Engine.

■ Try to schedule backups when SQL Server is not in the process of being heavily updated.

■ Use maintenance plans to streamline, automate, and schedule backups for all system and user databases.

■ For large databases, consider introducing additional files or filegroups and include a combination of full, differential, filegroup, and transaction log backups to reduce backup and restore times.

■ Speed up the backup process by selecting multiple backup devices.

■ Leverage the new Backup Compression feature to minimize the backup footprint on disk. However, first test the impact in a dedicated lab before rolling out into production.

■ When restoring the database to the point in failure, remember to first back up the tail-log and then conduct the restore.


Note from book "Microsoft SQL Server 2008 Management and Administration".