Sql-server – SQL Server Backups-Order of FULL & T-log backups

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)

While this is functional, there's some holes in here that will make it difficult for to recover in the event of a problem:

1. I'm not sure what the device is your backing up to for your logs, but based on what I'm seeing here, you're appending all your log backups to the same file/device for all your log backup actions. This can be cumbersome to manage and can cause you storage issues.

2. The BACKUP LOG ... WITH TRUNCATE_ONLY is redundant and can possibly cause you data loss. Your log truncation occurs after every log backup, so that should be sufficient to manage your log, though you should consider scheduling your log backups for every hour 24/7 instead of just 6 AM-11 PM.

3. Your full backup with it's current syntax will overwrite your previous backup every run, so unless your saving that backup off to tape between the full backup runs, you will not be able to restore previous to your last full backup.

4. Shrinking databases regularly is VERY bad practice, for a variety of reasons. Paul Randal talks more about it here.

In general, it looks like your backup/maintenance plan is as follows:

• Execute a full backup nightly (1 AM)
• Execute transaction log backups every hour
• Rebuild fragmented indexes nightly

This is a solid enough plan, it looks like you just need to fill in some of the gaps. Unfortunately, backup/restore is far to large a topic to cover here. I recommend you read about this at SQLServerPedia(now ToadWorld). Since you're committed to SQL 2000, this limits you to making use of some of the currently existing tools out there. However, plenty of code examples exist to fine tune and polish your currently existing code.

As for index fragmentation, without seeing your code, I can't comment to any possible issues with it. If it's been working fine up until now, there's probably no issues there, but you can read more about managing this up on MSDN.