Thought about this SQL Server backup plan?

Question

I just started a new job, and I'm reviewing the database maintenance plan. I've got quite a bit of experience writing SQL, but not much experience with DB administration. My last job was at a large company, and they didn't let regular people touch that sort of stuff.

We are locked into SQL Server 2000 (it's embedded in some quite old software and we can't upgrade yet). The current maintenance plan (Full Recovery model) does the following:

Every hour from 6am - 11pm:

1. backup log Accounting to Accounting_Logs with noinit

Every night at 1am, this happens:

1. backup Log Accounting WITH TRUNCATE_ONLY
2. DBCC SHRINKDATABASE (Accounting, TRUNCATEONLY)
3. backup database Accounting_ReadOnly to Accounting with init

Then at 3am:

1. all the indexes are rebuilt

Is this a decent plan? Will this give us good backups that are easy to recover? I know I'm asking for a lot, but any thought/comments/suggestions would be appreciated.

Please let me know if you need more information. Thanks!

Answer 1

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. 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.

Answer 2

Do not shrink your databases. This kills performance by fragmenting your indexes to 99%. in your situation it seems to be a pointless task, so just remove that step. You also do not need the 1am log backup if you are doing a full backup right after.

Other than that it looks pretty decent.