Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
verinow
Starting Member
5 Posts |
 Posted - 2006-11-29 : 14:28:33
|
| Hi, We make a a full database backup nightly. I have a job that backups our transaction log every hour. Each time I create a new log bu file. The worst case scenario is that I would need to restore the database and 15 logs. Should I be backing up the logs to the same file or do as I'm doing now?Also, We are not using differential backups. As I understand it, in event of restore, we would apply the differential backups to our last full bu and then apply the logs created after the last diff backup. Do people use differential backups because it faster to recover using them opposed to restoring from multiple, or large, log backups?Thanks,Mark |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-29 : 14:41:49
|
| It is better to create a new backup file for each log backup with a timestamped name for each file.I recommend doing transaction log backups a lot more often. at least every 15 minutes. It will help keep your transaction log file to a reasonable size, and provide more security in the event of failure.CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 02:17:23
|
| +1 to more frequent transaction backups. Ask yourself: Why are you prepared to lose one hours data when you could have more frequent backups and only lose a few minutes?And during index rebuild more frequent Tlog backups will reduce the total amount logged at any one time. That in turn will reduce the need to extend the LDF file. And that in turn will mean that your overall filesizes can be smaller, which will mean that a RESTORE [to new Database/Server] will be quicker.We use differential backups daily, and full backups weekly. This is to reduce the disk space on the server [used for backups] as our databases are at the ISP and in the event of a restore the ISP would take several hours to get the backup off tape.However, if you do daily full backups and you get a corruption you can just revert to an earlier full backup and apply all the TLog backups since then. More "risky" with differential backups.We also take differential backups before any "risky" database upgrades - as you say, its quicker to restore than applying all the TLog backups.I recommend that you do a TLog backup before your Full backup, and that you do database maintenance just before that. The database maintenance is likely to create large log files, and reapplying them [in the event of a restore] just wastes time!Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-11-30 : 05:06:01
|
| Kristen, I haven't try myself, but even tho you have a differential backup taken, and that file is gone for any reason, you still can get the DB back from the last good full backup and all the TLog files, since differential will only take the difference between the last full backup uptil that time, TLog should be still good? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 05:26:37
|
| Indeed, but that is a week ago, and given that my DIFF is bad I reckon there is higher risk that some TLogs will be bad too. If I go back a week and repeat a whole week's worth of TLog files and I can't get as far as last night THEN I will be wishing that I had taken a Full backup last night instead of a diff - and maybe all, or at least the early part, of the TLogs from last night are fine ...So I reckon DIFFs comes at a somewhat higher risk than FULLs. But you are right and, as Poker players would say, I still have "two outs" !Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-11-30 : 23:29:54
|
| True, but then if DIFF becomes bad, there is always chances that TLog will be bad either you have DIFF or not, so :) And I wonder how often ppl test TLog. From my exp, most company doing restore test is only working with FULL backup, some is better then deals with the whole process (ie. from tape to disk then restore the full backup), but I hardly seen any company that do TLog up to a given time, only do that when it really happens and found out a TLog fail...haha :) |
|
|
|
verinow
Starting Member
5 Posts |
Posted - 2006-12-01 : 08:32:25
|
| Thanks for all of your repsonses. This is my first post on this message board and I can't believe the help. I will backup every 15 minutes into seperate log files. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-01 : 09:49:35
|
"and I can't believe the help."That bad was it? Sorry mate ...  |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-12-04 : 05:27:25
|
| Haha, I haven't really start replying until last month :) |
|
|
|
|
|
|
|
|
Advantages - multiple log bu files?