NEVER plan backup, always plan for Backup Recovery

Well, my first blog so let me give a tip...

Taking backup of your databases should be the part of that Backup Recovery plan. Short detail about backups is that there are three types of backups in SQL Server 2005;

1. Full backup
2. Differential backup
3. Transactional backup

Recovery Model Setting

You also need to know about the Recovery Model of the database you are planning for backup. It should be Set to "Full". You can do this by right click database, hit "Properties", go to Options tab and there is the Recovery Model Option. In simple, the transaction log truncates automatically after the completion of the transaction. You only have the option of Full backup in Simple recovery model.

Maintainence plan

Sketch a maintainence plan according to your requirement. Plan for atleast One Full backup per week. Not more than a week. Plan Daily Differential backup. DO NOT delete the old Differential backup unless you are sure that the next day backup is verified. Plan for hourly or two hourly transaction backup.

Keep all the backups (Full, Differential and Trnsaction) in different folders.

Delete OLD backups

Write a script to delete OLD backups after verification of the fresh backups. Consult your network department. They will give you a batch file. You can add a task in Windows Task Manager. You will have to delete old backups as you may encounter disk space issues.

Should We Backup System Databases?

Plan backup for Master and MSDB database. Your login SQL Server information is in the Master Databse. Your Jobs are saved in the MSDB database. You should have the backup plan of these two system databases.