Full backup every hour
-
Any problems or things to be aware of in doing this?
The DB is currently 800mb, grows 2-3K per day. It's high availability, 24x7.
-
Are you also doing t-log backups?
-
Nope, it's set to Simple Recovery mode.
I realize I should probably switch to Full Recovery and do Diffs, T Log backups with Nightly full for the best/most complete recovery model.
Just wondering what the harm is if any in doing hourly full.
-
at 800mb it should not cause a problem.
the problem is if you have to recover to a point in time, without t-log backups you can't. For example if your database falls-over at 3.30pm your only option is to restore the full-backup from 3pm and you would lose 30mins worth of data. With t-log backups you can restore up until the failure.
-
Seems like a waste in my opinion..that's still 800MB worth of IO every hour. I would bring it to full, do full backups every night, and a trans log backup every 15 minutes or so. Add differentials in there if you want to, but I usually don't. You'd save a bunch of IO which might become an issue later if the db keeps growing.
-
I tend to agree with Derrick. Obviously space isn't an issue, though I'd be curious what you do with the backups and how long you keep them.
I'd add in log backups. I've done fulls during the day to speed recovery, but I don't know I'd do them every hour. That's a lot of IO for things that don' t necessarily change.
-
Thanks for the replies Gents!
So I'll switch to Full recovery and just do nightly Full backups.
Then I'll do T Log backups every 15 mins....why not do Diff backups every 15 mins? Is it an IO advantage again?
Also, if you don't do Diff or Full backups very often, you would have to restore potentially quite a few T Log backups in a recovery situation correct? Meaning you have to restore from the last Full, then apply each T Log in sequence, correct?
-
marc 96091 (10/4/2010)
Thanks for the replies Gents!So I'll switch to Full recovery and just do nightly Full backups.
Then I'll do T Log backups every 15 mins....why not do Diff backups every 15 mins? Is it an IO advantage again?
Also, if you don't do Diff or Full backups very often, you would have to restore potentially quite a few T Log backups in a recovery situation correct? Meaning you have to restore from the last Full, then apply each T Log in sequence, correct?
Differential backups contain all data changed since the last full backup, and txn log backups contain all data changed since the last transaction log backup.
Running a Differential backup will not clear out committed transactions in the log, so you would have to run a txn log backup anyway. Also, a differential backup will keep growing in size since it's based on changes since the last Full backup..so by hour 23, you have all 23 hours worth of changes in the one backup file.
On databases where I have 15min txn logs..yes it can be rough if you need to restore logs to set up mirroring, replication, etc, but you can easily write a script to create the restore commands for you if you do something like xp_cmdshell 'dir c:\logdir\' or whever your logs are, and then filter that data..I've done that previously and automated the writing of 300+ log restores with about 4 minutes of work.
-
With transaction log backups (and a full backup), you can restore to any point in time, but with differential backups, you can only restore to the point where the differential backup was made.
We normally do a full backup every night, and a transaction log backup every 15 minutes, 24x7.
-
We normally do a full backup every night, and a transaction log backup every 15 minutes, 24x7.
Hi
marc, in my openion, just falow Michael Valentine Jones's comment
Ali
MCTS SQL Server2k8 -
Definitely TLogs are the way to go for you. Depending on how much you trust your hardware, you might want to consider backing up to a network location, then spinning that off to tape regularly. While it doesn't matter a lot, I typically append my tlog backups to the full backup file to make a nice tidy package for portability. (The full backup is created WITH INIT to clear the file). RedGate SQL Backup lets you wildcard the source file names in their RESTORE command - that's proved itself handy - but I'm not sure you'd be too concerned about saving HD space considering the size of the db...
TLogs will give you the point-in-time recovery with STOPAT while differentials may or may not give you that depending on whether you're willing to store a pile of them in increasing sizes - AND keeping track of which one you'll need to restore from. (Remember: there are different types of disasters, and getting a call that "Herbie in Marketing fatfingered data an hour ago and we need it back" is one flavor to consider) Personally, I think if you're going to keep more than one differential around, you probably needed to use TLogs instead.
I'd suggest standing up a test server and practice disaster recovery. [Backing up] is fine but in a real DR situation you'll have at least two levels of management in "hover mode" behind your chair. Don't diminish their opinion of your skills by stumbling around. Don't forget about the current transactions in the log file that [haven't been dumped to a backup file yet]. Practice making the last tlog backup in case the server & database are still available. Document the recovery process with screenshots and simple instructions that your mom could follow. Or worse yet, someone completely non-technical like your boss. <lol>
Also, consider the logins on the new server. There won't be any for the database users, will there? <hint>
Ken's rules for Disaster Recovery. Rule #1: Only rookies panic.
-
Thanks Ken, this helps a lot. Actually thank all of you!
So Ken, I can append these Tlog backups to the Full backup...does that just create one file?
Also, is there anyway to make Tlog backups smaller? When I've run them in the past they've been several gigs for a whole days worth and I am trying to ship these off site.
Thanks again!
-
Ken Klaft-381933 (10/6/2010)
While it doesn't matter a lot, I typically append my tlog backups to the full backup file to make a nice tidy package for portability. (The full backup is created WITH INIT to clear the file).If you do this, and use a single file (I do not recommend this, myself) - then make very sure you have a copy of that file in another location that was copied AFTER your last transaction log backup and BEFORE you initialize the file (WITH INIT).
If you don't do this, then you have just thrown away all of your transaction log backups and destroyed your ability to recover to a point in time using a previous backup.
For example, using this approach - you backup your database to the now initialized file. An hour later, you find out that someone deleted a bunch of data that you need to get back - and they deleted that data 1 hour prior to your backup. How are you going to restore to that point in time?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
marc 96091 (10/6/2010)
Also, is there anyway to make Tlog backups smaller? When I've run them in the past they've been several gigs for a whole days worth and I am trying to ship these off site.
Thanks again!
There are third party tools that will compress backups, also SQL Server 2008 Enterprise or 2008 R2 Standard have native compression.
If these are going to tape then you really want to think about encrypting the backup to protect your data.
Hope this helps,
Rich[p]
[/p] -
Sorry for the delay in replying.
Re: appending
Run the following:
BACKUP DATABASE junk TO DISK = 'C:\junk.bak' WITH INIT
BACKUP TRAN junk TO DISK = 'C:\junk.bak'
Then do a RESTORE HEADERONLY FROM DISK = 'C:\junk.bak'
Re: smaller tlogs
If you do any database maintenance, you'll see larger tlogs around that time. Index processing may bump up the sizes so you might entertain doing a full backup right after all your maintenance. Maybe you [should] explore RedGate's SQL Backup. It works well.
Re: throwing away your files
<sigh> I communicated my idea poorly. While I hinted at 'a network location' I didn't specify the sequence of events to avoid screwing yourself by overwriting the full and all prior tlogs. You certainly want to have a copy of yesterday's full backup and all subsequent tlogs. A prudent thing to do would be to rename yesterdays full/appended file before writing a new one, incorporating a date stamp in the file name or making sure the target location is backed up to tape prior to the INIT. Either way (appending or separate files) has a strategy for disaster recovery specific to it. The point I was trying to make is to test that strategy and be comfortable with it. If separate files are your gig, rock on. Just practice with several different disasters in mind. Some involve recovering to the same server and some to a new server. Keep in mind the time line for backups. The full database is the [starting point] for a point-in-time recovery. In your case, you might be better off with separate files.
That'll teach me to reply with the phone ringing and users at my door...
Thanks for the reply James.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply