1

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.

Akash Kava
  • 467
  • 3
  • 8
  • 19

3 Answers3

2

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.

Ed Leighton-Dick
  • 1,094
  • 1
  • 7
  • 12
  • Please read correctly, log shipping requires shared folder on same LAN our server is on remote location, in different data center. – Akash Kava Sep 09 '09 at 15:06
  • Not necessarily. If you configure it using SSMS, yes, you do have to use a shared folder. However, I know of a number of DBAs who use custom log shipping (setup using scripts) to constantly update data to a remote server for disaster recovery. It takes more coordination to make it work, but I've heard it works well. (I don't have a remote data center or I'd be using it myself.) Here's just one example of how to implement this: http://www.sqlservercentral.com/articles/Administration/customlogshipping/1201/ – Ed Leighton-Dick Sep 14 '09 at 17:04
  • Thanks for your answer, I will try something with log backup.. lets see if it works well or not.. – Akash Kava Jun 24 '10 at 15:19
  • Ok how about if I only take log backup every 15 minutes since full backup, i dont take differential at all, will that work? – Akash Kava Jun 24 '10 at 15:20
  • Yes, but if you don't do the differential backups, you'll need to restore the full backup plus all log backups if you have a problem requiring a restore. Since you have remote servers, the differential backup might be useful so you have to restore fewer files (full + last diff + log backups since last diff). – Ed Leighton-Dick Jun 28 '10 at 18:47
1

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.

Massimo
  • 70,200
  • 57
  • 200
  • 323
  • Cant i purge the logs also regularly every 24 hours? – Akash Kava Sep 09 '09 at 13:25
  • How? You can use DBCC for this, but taking a transaction log backup, even if you're going to delete it immediately after, is the preferred way to do this. – Massimo Sep 09 '09 at 13:30
1

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.

BradC
  • 2,220
  • 4
  • 27
  • 36