| Author |
Topic |
|
NewSQLAdmin
Starting Member
4 Posts |
 Posted - 2011-11-09 : 04:54:56
|
| Hi,I have SQL server with over 30 DBs, we don't have any backup policies on these DBs.I tried to understand the diffrence between Diff backup and T-Log backup.I knew the Log backup will give me restore to certain time feature but I really don't care.I just need to restore the DB to last backup time.So, here is to ways:1- Full backup weekly Sun 10 P.M, Diff backup daily Mon-Fri 10 P.M.2- Full backup weekly Sun 10 P.M, Log backup daily Mon-Fri 10 P.M.Does it same? if I had crash DB in Wed for each ways:1- Restore Full backup from Sun, then restore Diff backup from Mon and Tue diff backup.2- Restore Full backup from Sun, then restore Log backup from Mon and Tue log backup.Second Question, I read in some site that Truncate_Only Log like cutting off your hand from bear trap!If I had Full backup what the need of transaction logged before Full backup? transaction after Full backup I know it is a must but transaction before log I don't need it any more, Am I right?BR |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-09 : 05:01:58
|
| full + diff + log by default if you want to be as much up to date as possible.The advantage of logs is that they are independent of full/diffs. You can restore any full followed by all the following logs - handy if you have a corrupt or lost backup.Any diff can be applied to the previous full so reducing the number of logs to be applied.I wouldn't do dialy log backups as it risk the log file growing - every couple of hours minimum down to 10- mins for active systems.1 shuold be Restore Full backup from Sun, then restore Diff backup from Mon or Tue. You can't restoer both diffs as they contain all the changes from the previous full.If you are considering fulls once a week then I guess you don't have a very active system - note if you are rebuilding indexes the diff can get as big as the full and take longer.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
NewSQLAdmin
Starting Member
4 Posts |
Posted - 2011-11-09 : 14:26:32
|
| Hi,Thanks nigelrivett for reply, our server is for test so I just want to clarify that my backup knowledge is correct!So could you please tell me if way 1 is same as way 2?Also please tell me if I do full backup it means that previous transactions are no more required!BR |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-09 : 15:30:02
|
There are a number of reasons why we use LOG BACKUPS, rather than SIMPLE Recovery Model and/or DIFF backups.1. We can recover to point-in-time. This is helpful if, say, we accidentally delete some data (in which case we recover the LOG backups to a new, temporary, database and copy-back the deleted rows to the main database)Restore to a new, temporary, database and investigate a fraud that was perpetrated; or "how did this happen" - which might just be debugging, rather than the result of user-cockup.2. After some breakdown (powercut, hardware failure, etc.) the ability to restore to very near the time of the failure (we take LOG backups every 15 minutes) so that we don't have to repeat a whole day's / week's data entry / work.3. in the event of database corruption, LOG backups give us a good chance of being able to restore last CLEAN Full Backup and all LOG backups since, and having zero data loss.4. We use LOG backups for Log Shipping to a Disaster Recovery Server so that in the event of total server loss we have minimal data loss and good business-continuityBy comparison Differential backup gives you:1. Ability to restore last FULL backup and a later DIFFERENTIAL backup.2. Smaller backups than doing a FULL backup every time3. But risk that both FULL and DIFFERENTIAL backup must be available, and "able" to be restored (this is no different to having FULL backups and LOG backups - they must both be uncorrupted / damaged  ) |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-10 : 05:47:09
|
| 1. should just be restoring the full and the Tue diff. apart from that they should give the same result.Often for test systems you set the recovery model to simple and just use full backups.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-11-10 : 15:59:32
|
quote:
Originally posted by nigelrivettOften for test systems you set the recovery model to simple and just use full backups.
Unless you need to model your live system and see how your maintenance scripts work. For development systems, I agree - but test/qa/uat systems you often need to see how changes will affect your transaction logs.Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-10 : 21:12:00
|
| "For development systems, I agree"I don't! We much more frequently restore our DEV systems to point-in-time, to reverse some catastrophe!, than we ever do on production systems |
|
|
|
NewSQLAdmin
Starting Member
4 Posts |
Posted - 2011-11-11 : 05:02:54
|
| @Kristen thanks a lot for your replies, It really helped me :) .@nigelrivett thanks a lot for replies, finally I got the answer :) but what about "Full backup does that mean previous log no more needed?".@jeffw8713 this is interesting, test/dev SQL server for test SQL DBs before migrate it to production SQL server, Also it is for test DBs behaviors after installing any patches or apply huge changes.Thanks guys for your help.I really appreciate it.BR |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-11 : 05:26:20
|
| If you restore that full backup then you don't need the previous logs.The advantage of saving those logs is if that full is corrupt then the following diffs are useless - but you can go back to the previous full if you have all of the following logs.The fulls and diffs just make the restores quicker - i.e. reduce the number of logs to apply.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
NewSQLAdmin
Starting Member
4 Posts |
Posted - 2011-11-11 : 06:15:24
|
| @nigelrivett thanks a lot for your kind help.BR |
|
|
|
|
Differential backup VS Transaction log backup