Home / server / Questions / 63661
Accepted
Akash Kava
Asked: 2009-09-10 01:37:38 +0800 CST

Log Backup required even if we take Full + Differential File backup?

  • 772

Here is what I am doing,

  1. Take full FILE backup at 1.00 AM
  2. Take differential FILE backup every 15 minutes
  3. And we ftp all data to other location and restore everything there

So now from My Source database to Destination database, every 15 minutes, its replicated remotely on other node. Now we do have full files, do we still need to backup transaction logs? We are willing to sacrifice last 15 minutes of data in case of disaster, because thats the best we can manage remotely.

Because even if anything crashes, last 15 minutes of data will never be recovered in case of hardware failure event, and to bring everything up will take more then a day, where else other server can start taking over and go on.

So ideally, Imagin a situation as below,

1.00 AM full file backup transferred to server B
1.15 AM differential file backup transferred to server B
1.30 ... same as above
1.45 ... same as above
1.50 ... crash, machine down...

at this position, can Server B continue from 1.45 restored database?

Because if machine crashes at 1.50, it will not be able to transfer Log backups anyway, and we have Full backup and differential backups restored on server B already.

So question redefined, if we have correct Full + Differential File backups, do we still need log backups?

As long as till 1.45 data is consistent we are good to go.

sql-server

3 Answers

  1. Best Answer

    I think you might be using the differential and log backups incorrectly. Differential backups backup everything since the last full backup; log backups backup everything since the last log backup. The big difference in your case is that the differential backups will grow throughout the day, and will therefore take longer at, say, 4:00 PM than they did at 8:00 AM.

    This is a more common way to do what I think you're trying to accomplish:

    • Full backup at 1:00 AM
    • Differential backup every 8 hours
    • Log backup every 15 minutes

    If you set things up this way, you can use log shipping to restore each log backup to server B as it is created, which would probably be quicker than using the differentials to do the same thing. If you needed to do a complete restore of the database to server B, you'd restore the full backup, then the most recent differential backup (since it contains everything since the last full), then the log backups since the most recent full or differential backup.

    • 2

  2. In this scenario, you don't need transaction logs backups in order to restore your database; but you still need to do them in order to purge the logs, otherwise they will grow to fill all the available disk space.

    • 1

  3. As long as the database was in Simple Recovery mode, then no, you wouldn't need log backups. That should work in your scenario.

    If you leave the database in Full Recovery mode, however, you'll need to take the log backups just to allow the log to recycle itself. That doesn't make sense in this case, though. Just use simple mode.

    • 1