InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:24:58 GMT20http://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxIf your differential backup is almost the same size and takes almost the same amount of time, then almost your entire database is getting updated in between. If you are experiencing htis, then you have soemthing wrong with your system or you're not taking the backups frequently enough.What is the backup schedule?Mon, 15 Feb 2010 23:30:24 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspx[quote][b]GT-897544 (2/15/2010)[/b][hr][quote][b]Bru Medishetty (2/10/2010)[/b][hr][quote][b]GT-897544 (2/10/2010)[/b][hr]I agree with Gail, but diff backup takes almost same time and space as full backup again it depends. Personally i would prefer full back every night and log backup once every hour, with SQL 2008 compression you will save lot of space on full backups.EnjoY![/quote]I don't agree with you on the time taken for a diff backup. The time taken perform a Diff backup will be far lower than the time taken perform a Full backup. Since the differential backup backups only the changes since the last full backup it would be only backing up those modifications. If there is a DB which is 450 GB (grown over a period of 2 years), and there are changes worth 1 GB for each day, the daily Diff backup will be of the size of that much (1 GB) on day one and tends to grow, since diff backups are cumulative. At any point it would be still be lower than the full backup.And regarding, log backups every one hour, that needs to decided by the amount of data modifications since the transaction log file will keep increasing without log backups.[/quote] I Knew that, but diff backup takes almost same time and space as full backup [u][b]again it depends[/b][/u] (I was refereing if it was almost same size).[/quote]I had never seen a differential backup (worth a 7 days of data) being equal in size with the full backup size.. Can you quote under what conditions would that be equal (with respect to the scenario I mentioned or with your own example)Mon, 15 Feb 2010 14:19:31 GMTBru Medishettyhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspx[quote][b]Bru Medishetty (2/10/2010)[/b][hr][quote][b]GT-897544 (2/10/2010)[/b][hr]I agree with Gail, but diff backup takes almost same time and space as full backup again it depends. Personally i would prefer full back every night and log backup once every hour, with SQL 2008 compression you will save lot of space on full backups.EnjoY![/quote]I don't agree with you on the time taken for a diff backup. The time taken perform a Diff backup will be far lower than the time taken perform a Full backup. Since the differential backup backups only the changes since the last full backup it would be only backing up those modifications. If there is a DB which is 450 GB (grown over a period of 2 years), and there are changes worth 1 GB for each day, the daily Diff backup will be of the size of that much (1 GB) on day one and tends to grow, since diff backups are cumulative. At any point it would be still be lower than the full backup.And regarding, log backups every one hour, that needs to decided by the amount of data modifications since the transaction log file will keep increasing without log backups.[/quote] I Knew that, but diff backup takes almost same time and space as full backup [u][b]again it depends[/b][/u] (I was refereing if it was almost same size).Mon, 15 Feb 2010 13:48:38 GMTGTRhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspx100% true... was isolated to my environment :w00t:Sat, 13 Feb 2010 09:33:19 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspx[quote][b]ashish.kuriyal (2/13/2010)[/b][hr]What is important is your log backup must be at regular frequency as per your database size[/quote]Frequency of log backups is not related to DB size. It's related to DB activity and the maximum amount of data that the business is willing to lose in a disaster.Sat, 13 Feb 2010 09:28:20 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxThanks to all for their valuable update. So what I understand is Differential backup is important but not hard and fast. What is important is your log backup must be at regular frequency as per your database size which will not only help to minimise the log file space on drive but also smallest the file, quickest to resote :-DAlso if required apply differential backup if full backup is taking too much cost of disk I/O and taking time...:w00t:Sat, 13 Feb 2010 05:45:08 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspx[quote][b]GT-897544 (2/10/2010)[/b][hr]I agree with Gail, but diff backup takes almost same time and space as full backup again it depends. Personally i would prefer full back every night and log backup once every hour, with SQL 2008 compression you will save lot of space on full backups.EnjoY![/quote]I don't agree with you on the time taken for a diff backup. The time taken perform a Diff backup will be far lower than the time taken perform a Full backup. Since the differential backup backups only the changes since the last full backup it would be only backing up those modifications. If there is a DB which is 450 GB (grown over a period of 2 years), and there are changes worth 1 GB for each day, the daily Diff backup will be of the size of that much (1 GB) on day one and tends to grow, since diff backups are cumulative. At any point it would be still be lower than the full backup.And regarding, log backups every one hour, that needs to decided by the amount of data modifications since the transaction log file will keep increasing without log backups.Wed, 10 Feb 2010 17:25:14 GMTBru Medishettyhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxI agree with Gail, but diff backup takes almost same time and space as full backup again it depends. Personally i would prefer full back every night and log backup once every hour, with SQL 2008 compression you will save lot of space on full backups.EnjoY!Wed, 10 Feb 2010 16:29:48 GMTGTRhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxTrue Gail, completely agree on that. I dint want to complicate him and advised as a possible solution that he'd better has a differential backup during the mid day which may reduce some labor intensive work.Mon, 08 Feb 2010 08:45:51 GMTmaechismo_8514http://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxMaybe, maybe not. Depends how big the DB is, how long it takes to backup full, diff or log, how long the restores take, what the downtime allowance of the DB is, how much storage space is available, how long backups are retained, how much of a performance impact the diff backup has, etc.There's no absolute right or wrong answer.Mon, 08 Feb 2010 08:35:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspx[quote][b]GilaMonster (2/8/2010)[/b][hr][quote][b]Krishna Potlakayala (2/8/2010)[/b][hr]It is better and recommended to have atleast one differential backup during the mid-day.[/quote]Why?[/quote]Woudn't be a better option to restore Full backup then differential which was taken during the mid day and then all subsequent transaction log backups, instead of going through those bunch of Tlogs for every 15 mins. :)Mon, 08 Feb 2010 08:26:40 GMTmaechismo_8514http://www.sqlservercentral.com/Forums/Topic861474-146-1.aspx[quote][b]Krishna Potlakayala (2/8/2010)[/b][hr]It is better and recommended to have atleast one differential backup during the mid-day.[/quote]Why?Mon, 08 Feb 2010 06:36:33 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxAdding additional differential backup on your existing backup policy may impact your performance(schedule it at non-peak hours).. and not to mention,you will have to preserve it to achieve the recovery..Mon, 08 Feb 2010 06:16:23 GMTTushhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxThanks for so quick response. But my only point is running differential backup for big database and highly transactional database, will impact the performance.... Mon, 08 Feb 2010 03:42:52 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspx[quote][b]Dinesh Asanka (2/8/2010)[/b][hr]in your senario, you don't need diff backups.[/quote]That's a wrong piece of advise.[quote] But there are places which has only one full backup for a week. take diff. backup daily mid night. log backups during day time[/quote]Totally depends on his business requirements and the recovery period that they have set to. Differential backups are an added advantage on database recovery at a DR scenario and also it does not lead to confusion. It is better and recommended to have atleast one differential backup during the mid-day.Mon, 08 Feb 2010 03:37:04 GMTmaechismo_8514http://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxAgain it depends on how critical the data is and you need to justify taking differential backup.In your environment taking differential backup will only help when you need to recover the data on production. It will help you to reduce the down time by reducing the restore of T-log backups.HTHMon, 08 Feb 2010 02:34:16 GMTfree_mascothttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxin your senario, you don't need diff backups. But there are places which has only one full backup for a week. take diff. backup daily mid night. log backups during day timeMon, 08 Feb 2010 02:02:14 GMTDinesh Asankahttp://www.sqlservercentral.com/Forums/Topic861474-146-1.aspxHi Guys, Just a quick question, is it advisable to have differential backup in production environment and what should be its frequency if you taking full backup daily midnight and log backup after every 15 min?Regards AshishMon, 08 Feb 2010 01:50:29 GMTcrazy4sql