Top 15 tips to maintain SQL Server database backup

Having a valid and up to data backup is most important for any organization. Backup should be the first step while planning for disaster recovery. It is the foundation of your disaster recovery plan. Today I will talk about the best practices for maintaining SQL Server backup.

1. Don't store your backup on the same computer on which your database file is stored. If your system crashes due to hardware failure or any other reason then you will lose the backup also.
2. Your backup plan should be according to your business needs. If your database changes frequently then take regular backups.
3. Make a plan that will be helpful in recovering from all kind of disasters like hardware failure, power failure, human error etc.
4. Disaster recovery plan should cover the three most basic database requirements such as performance, security and scalability.
5. If your database remains active all the time then perform transaction log backup every hour.
6. Perform differential backup every day and full backup every week.
7. Test your backup by restoring data on a server and see if it is working properly. You should run your database backup regularly with all the options and conditions that can occur during disaster.
8. Use RESTORE VERIFYONLY command to test backup files.
9. Schedule your backup operation when you are not performing any database activity.
10. Physically secure your backup media.   
11. Use advance backup features like BACKUP CHECKSUM to detect problem in backup itself.
12. Store backup data on disk rather than tapes.
13. Compress backup files before transferring it to tape or any long term storage device.
14. For long run storage, backup your data on tapes and for short run use disk.
15. You should also check the size and speed of your backup. If it is taking too long to restore then you may need to use backup compression software.