Help me understand/engineer a backup strategy.

I tried searching, but it appears the search is still broken.

Some of you know my other posts.. pleading for help with a problem (that's really sort of fixed itself/not remanifested itself).

Upon this whole ordeal I decided to re-evaluate my nonchelant backup strategy. Which currently is:

Backup our DB daily w/ transaction logs. (overwrite the backup daily on the tape).
Backup our DB weekly w/ transaction logs.

I rotate the tape weekly, on Monday morning. So there is a weekly backup and the last backup from monday morning on each tape.

Along with that I have some optimizations and shrinks I run.

Reading through the SQL BOL. It seems my strategy is ok, however if we lost the db at the end of the day, we'd lose a full days worth of work.

So. Here in lay the questions.

The BOL states to possibly back up twice a week, with differential daily backups and 4 hour transaction logs (example).

So, I sorta get that..

I make a device, "COMPANY.BAK", which writes to my external 'tape' unit.

I make a job to back up the complete DB on sunday. to "COMPANY.BAK". (Not sure if I tell it to truncate the transaction log?).
I make another job to make nightly differential backups to "COMPANY.BAK" with the 'append to media' switch on.
I make yet ANOTHER job to make a transaction log backup. Here's what I don't get. If I set this thing to run every 4 hours, since it's not a differential backup.. rather incremental.. doesn't it just overwrite itself each time? It doesn't append itself to the backup right? So how much data could be missing if the log is overwriting itself rather than appending to itself? Also, if I turn on truncate log on backup, aren't I missing possibly critical transactions from the log for a sucessful restore?

I guess I'm a bit befuddled here.How big are your backup files?
My default strategy is a nightly full backup and hourly transaction log dumps between 6:00 am and 8:00 pm (working hours). All backups and log dumps are to disk, but the new files are immediately copied to a network location.
That way, I have point in time recovery for database corruption using the local files, and hourly recovery from the network file copies should the server accidently get smashed by a loose gorrilla or fall into the vat of KY Jelly (perhaps I'm giving away too much about my workplace...).|||Ok. but how do you set the log file to backup every "X" hours? When I set it up, it seems like it'll overwrite the previous log backup?

My DB is in the 7GB range, right now my logfile is 1GB (dunno how to trim it, necessarily).

How do I keep a sane size log but not run into problems with restoring a broken DB (If this should ever happen?)

I'm lost, trying to be a dba when in fact I'm not.

Don't you also need to do database differentials? If you have a backup and 6AM, but crash at 7:45 am... and have no db diffs, dont you lose 1 3/4 hours of work? or can you roll that back with the logs?|||No, you do not need to do differentials. You can restore a database to any point in time using only the most recent full backup, the log dumps that have occured since then, and whatever can be recovered from the current transaction log.
Using the maintenance plan wizard you can create a job that runs the sqlmaint utility. This utility will create database backups and log backups, each with a unique name so that they are never overwritten, and can be set to delete files older than X days so that your disk doesn't fill up. My backup job command looks like this:
[EXECUTE xp_sqlmaint N'-PlanName AllDatabases -Rpt e:\SQLBackup\BackupDatabases.rpt -WriteHistory -BkUpDB e:\SQLBackup -BkUpMedia DISK -DelBkUps 5Days -CrBkSubDir'[/code]
You absolutely MUST read (or re-read) the BOL section on database and log backups so that you understand the difference between the two, and know what a differential backup is for. Then read the section on xp_sqlmaint. Then come back to the forum for any additional help.|||Ok.

I'm grasping this a bit better. Thank you.

What I guess I *STILL* don't get is the role of the transaction log. Say I back up at 6, crash at 7:45. If I have logs every 2 hours, I've lost everything from 6 - 7:45, right?

Ok, so, say I had logs in 15 minute intervals. So I could restore up to 7:30. But how does a "LOG" (to me a log is just a log of what's happened, like when I fish through my apache logs on my FreeBSD server). So how does a "LOG", restore data that wasn't in the database at the backup at 6am?

Does the LOG basically log every insert, delete etc query, with the data being changed?

So say my users add a new client to the DB at 7... when does it actually get written to the MDF, as opposed to sitting around in the LDF? At the next DB backup? At the next Transaction Log Backup?

When is it safe to get rid of logs? I'm assuming after a full or differential backup is done? Since everything that's been 'committed' is now backed up to a .BAK?

FWIW I have been reading the BOL administrators pocket consultant. Is this not the right one to use for backup references?|||Does the LOG basically log every insert, delete etc query, with the data being changed?

Yep. Every change made to a database is first written into the log. Only after the write to the log has been reported successful by the O/S will SQL Server (Or any other RDBMS worth getting) will write the change to the data files.

When to get rid of the logs is up to you. You >could< keep them for years, but they will only be as good as the full backup(s) before them. It all depends on what your users are willing to live with as far as data loss. Point in time recovery is also useful when the DBA (Database Arsonist) comes along and wipes out a table or two. He will usually not let you know about this, until he has tried to cover up his mistake with a few tries that usually only make things worse. This could take a couple hours depending on how many users there are, how many tricks the DBA knows, and how long it takes him to finally own up to making a mistake.

Since you only have a 1GB log, it probably does not take up a lot of space on the tape. Too many backups is a better ptoblem than too few, in my opinionated opinion ;-).|||Point in time recovery is also useful when the DBA (Database Arsonist) comes along and wipes out a table or two. He will usually not let you know about this, until he has tried to cover up his mistake with a few tries that usually only make things worse. This could take a couple hours depending on how many users there are, how many tricks the DBA knows, and how long it takes him to finally own up to making a mistake.Did we used to work together?|||Alright, thanks again folks.

This is my current strategy.

1) Backup db nightly at midnight to tape
2) Backup tlogs from 7a-7p in 1 hr increments (I dont need to backup the t-log right after the nightly db backup, right?)
3) Backup the system db's weekly (midnight on Sunday)
4) Run a reoganize optimization weekly (Sunday morning) on our primary db
5) Run a DB shrink on Sunday morning (weekly) on our primary db.|||Looks fine. I generally backup the system dbs nightly as well. Not necessary, but it costs practically nothing and I believe the simplicity increases reliability.
Now, have you ever TRIED restoring a database?
Do it (not on your production system).
Restore a backup.
Restore a backup and the first three or four transaction logs.
Try a point-in-time restore.
The time to learn how to restore databases is BEFORE you have to restore a database, and no un-tested backup strategy is a safe backup strategy.
Like it or not, you are the DBA for your group, and you are going to have to set aside a couple hours each week for admin.|||I don't see testing the restore part of your strategy. Make sure you have confidence that you can recover your databases by implementing disaster recovery drills ... or you might find that your tape has unrecoverable errors that prevent restores.

Lots of folks prefer backup first to disk, then save that file to either tape or another disk storage device. Also to disk and hold for a couple of days helps when you get the inevitable call "... I screwed up a table and need help restoring x number of rows or the business will go down the tubes in the next y hours"

And it will happen ... remember ... Murphy was an optimist! That's my creedo and guiding light!|||I would take out the shrink step, unless you are really hurting for disk space. Growing database files is a somewhat expensive in disk activity, so it should be avoided if possible.

The only other thing to mention is that backups of a database block each other, so if you try to run a full and a log backup at the same time, one of the backups will have to wait for the other to finish. Probably not a problem in your case, but good to know.

Did we used to work together?

Nope. But the same "DBA" has probably worked for both our companies.|||Ok. Thanks again.

I'll remove the shrink step, although the db seems rather swollen at this time (12gb, as opposed to 7 when it's shrunken).

As far as testing a restore. I have not. This is the only SQL server in the whole building. Does that mean I cannot test it?

Can I not try and restore the DB to another db name? such as foo-restore-test or something?

I was originally backing up to disk, and then running a nightly NTBACKUP job to move those to my tape(redundancy, yah?) but I found myself sort of hunting and pecking for time in the morning for the db to backup, and then yet another job to run (after I am SURE the backup was complete (usually hours later) .|||Backing up to disk, and then letting the filesystem backup collect the file is actually pretty common. A lot of DBA (The real sort) like to have the latest copy of the database handy, so they don't have to go sorting through tapes to find the one with the latest backup. Some places even send their tapes off site for storage, in case the whole building burns down.

You can restore the database with a new name, but you will also need to move the datafiles, or you will get "File Exists" errors when you try the restore. Hunt around in the backup/restore software for anything that looks like "move datafile".|||Alright. I'll look into backing up to disk and try and find an easy way to backup to my tape setup too. I'd like to the tape to get the hourly tlog backups too. Just in case, ya never know, right?

The NTBACKUP way just seems a bit... rudamentary. Since it'll put everything in a large backup archive, which I'd have to restore (ie let the system extract the data from the archive), and then run a sql restore.

I'd rather just have the tape mirror my disk backup on some sort of schedule. I'm not aware of a way to do that in SBS2k3 Premium without using NTBACKUP.|||Now that I think of it, wouldn't there be a way to have the SQL Maintenance manager duplicate the data? Or would that do the actual backing up part twice?

I see I can do it with the non wizarded backup, I can tell it to go to LOCAL and TAPE (just make two backup 'devices'). It seems to work... I tested it on the master db.

My only problem with that is I see no logging option there or way to tell it how many days to keep.|||Alright. So far everything is going O.K here. Except my backup failed this morning because the SQLMAINT isn't deleting backups older than "X" days, and my tape had 4 daily backups on it (instead of two). So, then my tran log this morning failed for the same reason because it tried to make an 8GB log (since the backup failed it couldn't mark to truncate everything before the backup, which included a dbreindex).

So, I moved the backups back to the local drive.. so the space problem wont occur anymore, but I need a way to 'mirror' the backups to my tape as they happen... both for creation and deletion.

What can I do for that?|||There should be some sort of setting for how long data remains protected on the tape, before something can overwrite it. If not, you can probably do the poor-man's version of this, and keep a set of tapes that you rotate through. When one set of backups is old enough, you just put that tape in and overwrite it all.|||Yeah I've got it set to keep daily backups for 2 days, tran logs for 2 days etc. I rotate tapes out weekly. But for some reason it's not deleting the backups... (if the drive is set to my tape, versus the hdd).

On top of that, watching the log files before I started backing up soley to tape. It creates the new .BAK before deleting the older .BAK's (this is when I had them storing to the hdd full time). Shouldn't it delete .bak's before adding them? Shrugs.

I'll fix that problem by telling SQLEM to write backups to the hdd again, no problems there. I just need a way to replicate it to my tape without actually running another backup process. It seems telling SQLEM to write to two devices stripes the backup, rather than duplicates.|||I believe the only way SQL Server can "delete" a file on tape is to overwrite it. Do you have any way to see the contents of the tape?|||Yeah, I can browse to it like it's a drive letter. It's not a 'tape' in the true essence. But SQLEM seems to treat it as such.|||The maintenance plan will probably try to delete the file from tape in the same way that the command line delete command works. Try to delete a file using the delete command. You may want to try this with an old tape, or a tape from a test system, if available. ;-)|||That's just it. SQLEM will deleted from C:\Backups\daily\bleh.bak just fine. But E:\backups\daily it just seems to ignore it.. It's sort of futile anyway, since I'd rather have it back up to the C: drive and replicate to the tape (thus once the SQLEM deletes 2 day old backups, the replication does the same). I just cant find a way to do it without using NTBACKUP. (yuck).|||[quote=Mindflux]
On top of that, watching the log files before I started backing up soley to tape. It creates the new .BAK before deleting the older .BAK's (this is when I had them storing to the hdd full time). Shouldn't it delete .bak's before adding them? Shrugs.
[\quote]

Not in this space-time continum. If you delete a previous backup, and the current backup fails, you would have no backup. This is microsoft's way of preventing you from shooting yourself in the foot!|||Ah, yeah. I get your point there.

Hadn't though microsoft would ever do something to help the user ;)