SQL Server Performance Forum – Threads Archive
Transaction Log backup for 6.5 compatibilityI am a dba working on databases that are currently running on sql 7.0 with 6.5 compatibility. The current backup system is a full backup nightly and diff backups in the morning every 2 hours between 8 and 6. This is causing a lot of disk space and the servers do not have enough space and hence planning to migrate to transaction log backup. The truncate log on check point is turned on and this has to be turned off for the transaction log backup to work. Need to get some opinion if the transaction log backup will cause any problems since we need this to be restored across the network to backup servers. What is the advantage of diff vs transaction log backup in this situation
diff backups will definitely take more disk space than log backups because each diff backup contains data since last full backup whereas each log backup contains data since last log backup. And when it comes to restore you need only last full backup and the most recent diff backup whereas with log backups you need to restore all the log backups since last last full backup.
Rely on Transaction log backup where you can define your own log shipping concept which will work without any issues. Make sure you take out Trunc.log at chkpt. option if you opt to take Tlog backups. I also suggest to keep interval of one hour or half-an hour between Tlog backups. _________
Concur with Satya on this. Make sure you have a proactive mechanism to identif if any transaction log backup was missing or not copied across network. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard