...Because someone's gotta tell the story...


To return to the main Blog List, click Full Blog Listing.



SQL Server: Backup Pitfalls

Tuesday, February 19, 2008 in Technical Articles (Views: 3533)
Backing up in Microsoft SQL Server seems easy enough, with the Maintenance Plan Wizard. However, backing up seems to be more of an art than a science.

A lot of things need to be considered. First of all, how will you back up? Do you have the proper space to allow for a reasonable amount of online backups?

Some tricks, gotchas, and things to consider when backing up.

1. Backup files (aka Dump Files) are not compressed.

Backup files do take up a lot of space, and by default are not compressed. You can save a lot of space by simply compressing your backup directory on the disk. You will be shocked at how much space you recover.

2. Carefully consider your recovery model.

Microsoft SQL Server has a lot of cool features, one of which being Transaction Log, or "Point in Time" backups.

This can be a good feature, but be careful - many of your custom (and not so custom) database applications only support restoring from full backup and not from transaction logs, usually causing your transaction logs to grow out of control.

To shrink your transaction logs, you can do a couple of things:

1. Set the recovery model of the database to Simple. This will remove the need for transaction logs.

2. Backup the database (without the logs)

3. Shrink the database

4. You will notice the associated LDF file all but disappear.

In a later blog, I will show you a neat way through TSQL how you can shrink and defragment a database, while it is still live.

Do remember when the database recovery model is set to simple, there are no transaction log backups.

3. SQL 2005 Legacy Backups can be trouble

If SQL Server imports a SQL 2000 Maintenance Plan, one key feature is NOT implemented. Which feature is this? The feature to delete backups older than xx days. If you don't pay attention, your disk will fill up in no time.

You can remove your old backup jobs for SQL 2000 and replace them with SQL 2005 Native Jobs, which are much more streamlined and although requiring a learning curve, aren't too bad once you get used to them. One good example is that you can set up a backup plan for multiple databases, complete with full and differential backups in the same job.

I ran into the trouble of database backups not auto-deleting, and wrote a VBScript that deletes files that are older than xx days. This little script is good for a lot of things, but was intended to delete SQL backups.

4. SQL 2005 Backups support Differentials

This is a great new feature. Instead of messing with Transaction Logs, you can do full and differential backups on the data, similar to a file system backup.

In general, these are some things I've seen with SQL Server and backups. Hopefully this will help in keeping your disks from filling up too quickly, taking your server down in the process.. :)


Related Blogs You May Be Interested In:

To leave a comment, please log in and/or register.