June 8, 2007 at 8:17 am
Rob,
From what you say I suggest that you the following:
Set to Full Recovery mode.
Backup the database (FULL) each night.
Backup the Transaction Log to disk every 20 minutes during working hours. If the backup is on the same physical disk as the TLog itself then copy the back up files to a different disk drive.
Shrink the database and TLog at the weekend. If the Tlog grows too much due to occasional spurts of high activity then shrink it each night (or after the occasional spurts).
Also don't forget to test the backups occasionally by restoring the Full+TLog backups to either a different database or to a different server.
HTH
David Saville
Aldex Software
June 8, 2007 at 2:09 pm
Thanks, David.
It sounds like a sensible plan - can the database and TLog be shrunk using a Maint. Plan, or should I figure out some way of using a timed batch file in Query Analyzer?
June 8, 2007 at 3:24 pm
Shrinking files on a regular basis is not a recommended practice. It causes physical file fragmentation over time.
If you must do it, you can do it with a maintenance plan. You can also enable autoshrink on the databases, which has the added disadvantage of picking random times to annoy the users with the file shrink operations.
June 12, 2007 at 1:22 am
Don't forget to check your INIT and NO INIT options are correct - I found a database with the log backing up beautifully every hour but each log backup was INIT and overwrote the last!
Don't get buried in articles either, there is just so much it can be confusing - Books Online is still the first resource to turn to and usually solves the problems for me.
June 12, 2007 at 5:33 am
This is all crazy stuff - I have never heard of any of this stuff before now, even though I have a number of clients running something or other on SQL. Wonder how many of htem are properly protected?
I'm wondering if maybe using the 3rd party app (BackupExec) is enough, or if maybe there are a lot of people out there who run blissfully along assuming their backups are properly configured when they are just one small mis-step away from a full-blown disaster?
INIT & NO INIT - I assume these are switches for some obscure command-line function?
June 12, 2007 at 6:17 am
INIT & NO INIT are switches for the obscure BACKUP LOG and BACKUP DATABASE commands. The standard maintenance plan creates a new file for every log backup, so these settings aren't important. That is, if you're backing up to disk. I've never done backups directly to tape with a SQL maintenance plan, I've only seen it done with agents created by Backup Exec.
The maintenance plan wizards are not foolproof, but they are fairly robust (at least through SQL 2000, I gave up on the SQL 2005 version). I think you'd have to abandon the standard maintenance plans and write your own backup procedures to end up with something that overwrites the log backups every hour. The only way to tell whether your backups are any good is to do some test restores every so often.
June 12, 2007 at 6:44 am
Rob,
Yes, there are an awful lot of options with SQL Backups and it can become complicated - but this is partly because of the power and flexibility that SQL Server provides (for example you can restore a backup to a specific point in time rather than just to the end of the backup). I've no idea how other comparable databases do backups but I would be surprised if Oracle and the like did not have a similar range of options.
However - many of these options are only available if you 'hand-roll' your backup procedures. If you create your backups through the Maintenance Plan then the wizard will generally put in defaults that you can accept - and this is really fairly simple. The only complications are that you need to make sure your database is in FULL recovery mode and that, in the Maintenance Plan wizard, you schedule a FULL backup each night and a Transaction Log backup every 15 minutes or so during working hours. Also that your backups are ideally made to a different physical disk to the one(s) on which the database and transaction Log are located. This should be suitable for most standard application databases (but obviously not all) and should only take a minute or two to set-up.
Regards
David Saville
Aldex Software
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply