Backup is the skill that no one else in organization more than a DBA itself. It is ultimate shield and optimized back plan may be of great advantages. Following are some baselines for database backups
- All production databases should be set to use the full recovery model. This way, you can create transaction log backups on a periodic basis.
- Whenever possible, perform a daily full backup of all system and user databases.
- For all production databases, perform regular transaction log backups, at least once an hour.
- Perform full backups during periods of low user activity in order to minimize the impact of backups on users.
- Periodically test backups to ensure that they are good and can be restored.
- Backup first to disk, then move to tape or some other form of backup media.
- Store backups offsite.
- If using SQL Server 2005 encryption, be sure to backup the service master key, database master keys, and certificates.
- If you find that backup times take longer than your backup window, or if backup file sizes are taking up too much space on your storage device, consider a third party backup program, such as SQL Backup Pro or SQL tool belt.
- Document, step-by-step, the process to restore system and user databases onto the same, or a different server. You don’t want to be looking this information up during an emergency.
Chosen from SQL Server DBA best practices By Brad M.mcGehee