[firebird-support] Re: nbackup strategy advice
Hello Paul. Thanks for your advice. My strategy was based on backup data once approach but this would produce to many files I now realize. But I still want to avoid to backup the entire database (N=0) on regular basis. Whats your opinion about this approach? First backup N=0 Every day N=1 for a month (replace file each time) Increment N next month. This would produce 12 files every year. Thanks, Hugo
Re: [firebird-support] Re: nbackup strategy advice
Den 2014-03-16 07:27 skrev hugo.lar...@yahoo.com såhär: Hello Paul. Thanks for your advice. My strategy was based on backup data once approach but this would produce to many files I now realize. But I still want to avoid to backup the entire database (N=0) on regular basis. Whats your opinion about this approach? First backup N=0 Every day N=1 for a month (replace file each time) Increment N next month. This would produce 12 files every year. That wouldn't work. I don't think you understand how Nbackup works. N=0 backs up everything. N=1 backs up every page that's changed since last N=0. N=2 backs up every page that's changed since last N=1. ... N=k backs up every page that's changed since last N=k-1. So, with your suggested scheme, first month would go like this: Day 1: backup entire database (N=0). Day 2: backup pages changed since day 1 (N=1). Day 3: backup pages changed since day 1 (N=1). Will include all pages copied day 2 + additional pages changed since day 2. Day 4: backup pages changed since day 1 (N=1). Will include all pages copied day 3 + additional pages changed since day 3. ... Day 28/30/31: backup pages changed since day 1 (N=1). Will include all pages copied the day before + additional pages changed the last day. If Month 2 you increment N to 2, you will get this: Day 1: backup pages changed since last day of month 1 (N=2). Day 2: backup pages changed since last day of month 1 (N=2). ... Day 28/30/31: backup pages changed since last day of month 1 (N=2). At the end of the year, your actual final backup sequence will be: N=0: Initial backup first day of the year. N=1: Last backup of month 1. N=2: Last backup of month 2. ... N=12: Last backup of month 12. It would probably make more sense to do it like this: First day of year: N=0, initial complete backup. First day of each month: N=1, will contain all pages changed since first day of year. First day of each week: N=2, will contain all pages changed since first day of month. Each day: N=3, will contain all pages changed since first day of week. If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. This way, you will have a daily backup that's complete, consisting of four parts (N=0, 1, 2 and 3). In general, Nbackup should be run with each value of N at a regular interval, with tighter intervals for higher values of N. Incrementing N over time as you suggested is not suitable. Note that Nbackup has no way of detecting any database corruptions, so if that happens it will go completely undetected. Might be a good idea to combine it with some client local gbak or gfix- v or gfix -v -full, as often as is viable. I might also mention the possibility to simply lock the database with Nbackup and copy the database file with rsync. This will probably have similar or better performance over a slow connection. That's what I do for out 200 Gbyte database, although that's on a single server with a backup volume attached via high bandwidth network. :-) Regards, Kjell
[firebird-support] Re: nbackup strategy advice
Hello Kjell, Thanks for your time analyzing and explaining my backup strategy and proposing yours. You wrote that my strategy wont work. Maybe i'm missing something or I explained wrong. As I understood your strategy is based on annually full backups (N=0) and maybe you understood that my approach is the same. My intention is that after new year N is again incremented to 13 and NOT starting over again from 0. So after two years N=24 with 24 files. First file is the full backup and the other files contains one month of data. Note that I replace the daily N file with the new one. This way (with understanding of how nbackup works), I avoid to ever having to do a full backup more than once. Whats your thoughts on this? BR, Hugo
[firebird-support] Re: nbackup strategy advice
Hello Thomas, Each shop, for example a restaurant has its own local database on the computer the Point Of Sale software is running on. The shops has ADSL connection with 1MBit upload speed and the database is around 50 MB and growing every day. Uploading such file to our server with this bandwidth disturbs other Internet activities and at night they turn of the system. So im trying to figure out an optimal backup solution. Thanks,
Re: [firebird-support] Re: nbackup strategy advice
Each shop, for example a restaurant has its own local database on the computer the Point Of Sale software is running on. The shops has ADSL connection with 1MBit upload speed and the database is around 50 MB and growing every day. Uploading such file to our server with this bandwidth disturbs other Internet activities and at night they turn of the system. * Who is the initiator of the backup? You, remotely? Or a scheduled job at the customer? * I guess there is a business use case why you want to transfer it onto your server once per day? Btw, you can use gbak in a way so that the backup process completely runs on the target server, even if gbak was invoked remotely. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: nbackup strategy advice
Kjell Rilbe wrote: First day of year: N=0, initial complete backup. First day of each month: N=1, will contain all pages changed since first day of year. First day of each week: N=2, will contain all pages changed since first day of month. Each day: N=3, will contain all pages changed since first day of week. If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. That's not necessary, and the higher level backup will add nothing on that moment. Suppose you make a level-2 backup every Sunday. Then it makes sense to schedule the level-3 backups daily from Mon-Sat. If you want to make a point-in-time restore later, it will involve 4 files if that point in time is on Mon-Sat, and 3 files if it is on a Sunday. (And 2 files if it is the first day of the month.) All you have to do is determine the most recent backup before the chosen point in time. If that is a level N, you need N+1 files for the restore (levels 0-N, each one being the most recent file of that level before time 'T'). Cheers, Paul Vinkenoog
Re: [firebird-support] Re: nbackup strategy advice
Den 2014-03-16 11:58 skrev Paul Vinkenoog såhär: Kjell Rilbe wrote: First day of year: N=0, initial complete backup. First day of each month: N=1, will contain all pages changed since first day of year. First day of each week: N=2, will contain all pages changed since first day of month. Each day: N=3, will contain all pages changed since first day of week. If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. That's not necessary, and the higher level backup will add nothing on that moment. Suppose you make a level-2 backup every Sunday. Then it makes sense to schedule the level-3 backups daily from Mon-Sat. If you want to make a point-in-time restore later, it will involve 4 files if that point in time is on Mon-Sat, and 3 files if it is on a Sunday. (And 2 files if it is the first day of the month.) All you have to do is determine the most recent backup before the chosen point in time. If that is a level N, you need N+1 files for the restore (levels 0-N, each one being the most recent file of that level before time 'T'). My idea was to avoid having to keep track of N for last backup. The script scheduling would be a bit simpler. And I do realize the N=1 backup right after the N=0 backup will store nothing. It will, however, make it possible to do the N=2 backup next day and have a complete N-chain. Skipping the N=1 backup and go right for N=2 won't work. or am I missing something? Kjell
Re: [firebird-support] Re: nbackup strategy advice
Hi Kjell, If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. That's not necessary, and the higher level backup will add nothing on that moment. Suppose you make a level-2 backup every Sunday. Then it makes sense to schedule the level-3 backups daily from Mon-Sat. If you want to make a point-in-time restore later, it will involve 4 files if that point in time is on Mon-Sat, and 3 files if it is on a Sunday. (And 2 files if it is the first day of the month.) All you have to do is determine the most recent backup before the chosen point in time. If that is a level N, you need N+1 files for the restore (levels 0-N, each one being the most recent file of that level before time 'T'). My idea was to avoid having to keep track of N for last backup. The script scheduling would be a bit simpler. And I do realize the N=1 backup right after the N=0 backup will store nothing. It will, however, make it possible to do the N=2 backup next day and have a complete N-chain. Skipping the N=1 backup and go right for N=2 won't work. or am I missing something? No, you're right. A level N backup is always based on the most recent level (N-1) backup. So, suppose the first of the month is on a Tuesday, and the level 1 backup is made. Level 2 backups are scheduled every Sunday and level 3 backups every working day. In that case, the level 3 backup of Wednesday the 2nd will be based on the level 2 backup of Sunday the 29th or 30th of the previous month. The most recent level 1 backup won't be in its restore chain. That's not intrinsically bad, but it's confusing. So after the monthly level 1, it makes sense to have it followed immediately by a 'weekly' backup, even if it's the 'wrong' day of the week. But the daily backups can start the next day, *unless* there are also hourly backups scheduled (N=4). I also realize now that I automatically assumed that nothing happens during weekends, but of course that depends entirely on the situation. Kind regards, Paul Vinkenoog