The recoverability of your databases is probably the most important thing you need to be thinking about when you are creating a new database or you have an existing system. This provides you the ability to recover your data in the event of a data corruption, If you are doing an upgrade and there are issues, possibly even some malicious damage being undertaken. If you do not have any database backups then you are in a world of hurt with no way of recovering you data.
Some things you need to consider in determining the recovery you require:
- Recovery Point Objective (RPO)
- Recovery Time Objective (RTO)
RPO – is how much data loss you can handle in the event of having to rollback to a database backup. Your data may not change that frequently and you may be able to live with going back to the previous day. On the other hand your data may be highly reactive and change very frequently and if you were to rollback to yesterday you will have lost a lot of data. If that is the case how does that effect your business or environment that your database is being used for?
RTO – is the time allowed for you to be able to recover from backup to bring your system back up and online. Like RPO this is dependent on your environment and your requirements.
Now that we understand what recoverability is all about, we now have to put something in place to meet our requirements. Database backups are the mainstay behind your ability to recover. Some possible scenarios for your recoverability set are:
1. Full Database Backup nightly. You are not concerned about being able to recover to a specific point in time, but are happy to be able to roll back (if necessary) to last night’s backup. Your database will be in a Simple Recovery Mode (No ability to run transaction log backups)
2. Full Database Backup nightly, Transaction Log backup every 4 hours. You are concerned in being able to recover to a particular point in time. You database will be in Full Recovery Mode. (The frequency of your TLog backups is arbitrary and is dependent on your own environment. You could go to have TLog backups every 15 minutes)
3. Full Database Backup Weekly on a Sunday Night, Nightly Differential Backup, 4 Hourly TLog backups. This type of backup scenario is more for large systems. As your databases are restricted to 4GB this is not really an issue. I have just included this option for completeness.
If you have not read my blog post – SQL Server Agent for SQL Server 2005 Express , you may be asking yourself “How can I implement a backup strategy when my instance of SQL Server 2005 Express does not have a scheduling agent? ”. To implement any kind of backup strategy you need some sort of scheduling agent. You can implement the SQLScheduler or if you like getting down and dirty with scripts you could write some batch files, including calls to osql to perform your backups and call these batch files through the Windows Scheduled tasks. Either way there is nothing out of the box with SQL Server 2005 Express that gives you this functionality.
Feel free to download any of the following backup scripts
- Usp_BackupSystemDatabases.sql
- Usp_BackupUserDatabases.sql
- Usp_BackupUserTLogs.sql
- Usp_CleanupOldBackups.sql
Stay tuned for the next installment in Database Maintenance in SQL Server 2005 Express – Integrity
Leave a Reply