| Author |
Topic |
|
jonjsilver2
Starting Member
14 Posts |
 Posted - 2010-09-08 : 16:06:52
|
| So I am new to SqlServer and am trying to implement a backup/restore, but have a few questions.1. Is there a difference between partial and Differential backups?2. The database is Full. I can see doing a full each day, then the transaction logs every hour, Why would I ever want to do a differential?3. If I am saving the backup files off on a different machine, why would I ever set the expire info4. Should I set this up as jobs? Any downside to this?thanks,jon |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-09 : 02:28:31
|
2a. We use hosting in our ISP's racks. Getting a file restored may take some time, so we want to keep the longest backup chain on-disk that we can. We take a FULL once a week and DIFFs daily to best use the disk space and give us the longest recovery interval (before we have to request a from-tape restore.)2b. I'm with Tara. TLog backups every 15 minutes. Why wait for (and possibly lose) a whole hours data? Also, an index rebuild can generate a HUGE Log file in an hour :) We increase Tlog backup to every 2 minutes during index rebuild (to stop the LDF file growing wildly)3. I think EXPIRE is probably related to tape management systems - so that they can reuse tapes when the contents are "flagged" as having expired. I see lots of backup commands being used that have commands in them that clearly only ever were intended for Tape backup and I always wonder why the DBA put them in the script! We log all backups to a Table (in an Admin database) and use that to delete backup files after their retention period [and to only delete a FULL when all its DIFF and TLog backups have themselves been deleted]4. We have it as a job, but we also have our own Sproc that we use for backups (i.e. we NEVER use the BACKUP command directly). The Backup Sproc is used for adhoc backups and ensures that the file is put in the right place, included in the Admin database, gets deleted after the appropriate retention period, and some twit doesn't take a FULL backup, which subsequent DIFF backups will depend on, and delete it afterwards thinking "That went OK I don;t need that backup now"  |
 |
|
|
jonjsilver2
Starting Member
14 Posts |
Posted - 2010-09-09 : 09:16:50
|
| Ms SQL Goddess, Thank you for your reply,1. I have no idea what partial means, I see that in the documentation and have been trying to figure it out. Sometimes it sounds like they mean partial but sometimes not. 2. If I do a differential backup, does that mean that I no longer need the tlog backups taken before that time? thanks again,jon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-09 : 12:58:55
|
| 2 Although ... if your Full backup, or the DIFF after it, was corrupted you could go back to an earlier Full backup (and optionally a DIFF based on that earlier Full backup) and then restore ALL Tlog backups sinceSo its worth keeping them just in case your latest backup becomes corrupted.If your database becomes corrupted it is likely that any Full backup you then make will be corrupted too. The Tlog is completely separate, so there is a chance that that will not become corrupted, so having both Full+Diff, and keeping Tlog backups to be able to restore from an earlier Full backup, is a good idea.(You may also need the Tlog backups in order to restore to point-in-time - e.g. to review data at a point-in-time for fraud investigation, or to see "how" half the customer got deleted!! etc) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-09 : 16:34:11
|
| I think you are right Tara, just put my point in case the other possible needs for a Restore had not been consideredYou have a short retention policy IIRC? Does that mean your files on Tape too? or just those on disk? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-10 : 03:24:49
|
| That's what I remembered you saying. We have had to restore from months ago and make investigations to establish the cause of some data changes - to establish if it was fraud / accident / program error etc. Presumably you could not perform that sort of investigation without long term backups?Sorry ... off topic ... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-10 : 08:25:53
|
| Wow! That saves some hassle. Doing a fraud back-trace takes forever! |
|
|
|
|
Basic Backip/Restore