Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gmetaj
Starting Member
33 Posts |
 Posted - 2004-05-19 : 10:07:11
|
| Are incremental backups efficient without a full backup. Below is my scenario. I have a database that is fairly large that I want to backup. Let's say the size of the DB is 100 M and I apply differential backups every night. THe size of the DB grows by 1M every day due to changes/updates and new additions to the db. In BOL it says that differential backups are backups since last full backup, however i am thinking about using WITH INIT, DIFFERENTIAL option when I do the backup. I would like to know if my logic is correct. Day 1 Db Size = 100M Day 2 Db Size = 101M Incremental Backup Size = 1 MDay 3 Db Size = 102M Incremental Backup Size = 1 MDay 4 DB size = 103M Incremental Backup Size = 1 M.......Day 29 DB size = 128M Incremental backup size = 1 MDay 30 DB size = 129M Incremental backup size = 1 MI was wondering if apply BACKUP DbName to Disk = "Location"WITH INIT, DIFFERENTIAL Will my backup file grow larger and larger every day, that is on day 30 will the backup file be about 1M or 29M. I apply the changes to another database every night and do not want to do a full backup. can i continue doing BACKUP DbName to Disk = "Location"WITH INIT, DIFFERENTIAL without having to do only a full backup ever. Thank you! |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-19 : 10:16:03
|
| You could as long as you have the original full backup. I would recommend a full backup at least once a week though. One strategy I've used is this:Full Backup - SundayDifferential Backup - Monday through SaturdayTransaction Backup - Every 30 minutesThe differential saved a lot of space and time. The good thing about this strategy is the restore phase. I could get current by restoring:Full---->Diff (for Wednesay as example)---->Transaction (to 1:00pm for example)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
daveman6610
Starting Member
8 Posts |
Posted - 2004-05-19 : 10:17:45
|
| Differential backups will backup every database page that has been changed since the last full backup. So if your scenario, if your 100M database adds 1MB on day one, the size of the differential will be a minimum of 1MB, but will be larger if you change existing data and indexes as well.Differentials are not a replacement for full backups. They merely act as a stop-gap for faster recovery between full backups. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-19 : 10:21:21
|
| init only affects the file that is being written - it overwrites rather than adds. I always create a new file including the datatime for backups>> Will my backup file grow larger and larger every dayIt will contain all changes since the last full backup so will keep getting bigger until the next full backup. How much bigger depends on the changes that are being made to the data.You can keep doing differentials but they will eventually take longer than the full.You would usually keep more than one differential just in case.If you never take another full backup then you will lose the database if that backup gets corrupted.I would suggest taking a full backup at least weekly.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
gmetaj
Starting Member
33 Posts |
Posted - 2004-05-19 : 12:53:02
|
The problem that I have is that I am trying to use backups to implement updates on a database (Read Only) on the internet. I need to send incremental changes from Original Database in the inhouse server to that same database on the internet. The size of the database is rather large and i cannot afford (timewise) to transfer the whole backup file over the internet even weekly. I need to trasnfer only the changes that have happened to the database inhouse to the database on the internet. I apply the differential backup files to the database located on another server by trasnfering the file via ftp. If my backup file grows indefinetly due to the lack of a full backup then i need to look for another solution. Any recommendations for this scenario?Thanks a lot in advance.quote:
Originally posted by nr
init only affects the file that is being written - it overwrites rather than adds. I always create a new file including the datatime for backups>> Will my backup file grow larger and larger every dayIt will contain all changes since the last full backup so will keep getting bigger until the next full backup. How much bigger depends on the changes that are being made to the data.You can keep doing differentials but they will eventually take longer than the full.You would usually keep more than one differential just in case.If you never take another full backup then you will lose the database if that backup gets corrupted.I would suggest taking a full backup at least weekly.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-19 : 13:10:47
|
| Use transaction logs instead then.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-19 : 14:21:33
|
| Regardless of whether you use transaction logs or diffs though, you still should do a "baseline" restore once a week or so from a full backup. This will become very important to you if you ever have a disaster. You don't want to have to recover a full backup from two months ago, then go through two months of transaction log backups to catch up.Look at the schema I posted earlier and think about that. It uses a combination of full, diff, and tran backups.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Are incremental backups possible