S
S
squidw2017-02-09 00:10:27
Backup
squidw, 2017-02-09 00:10:27

How to organize a three-level backup, storage and recovery using MS SQL Server? How to calculate the volume for backups?

Now only full copying is done on the target IS, I don’t want to do it on the main database yet I made a copy for myself, I play around on the test one. In principle, I have already set up the reservation in accordance with the model I need, but there are unpleasant critical details that I did, it seems to me, a bit of a crutch, so read to the end at least so as not to duplicate my earlier actions with your advice.
The backup model that I organized: 1 time per month - full, 1 time per week on weekends - differential (differential), from Mon to Fri, by execution time every 30 minutes from 09:00 to 18:00 - transaction logs.
The storage model that I plan to introduce: full - 6 for half a year from each month, differentials - 4 for the last month, transaction logs - all for the last week from Mon to Fri.
1) When testing when I did manually in SSMS: full, diffs and logs, they are saved in a "set". As a manual test, I did the following:
1 full
1 diff 1
log
2 diff
2 log
3 log
Why?
1a) The second point, taking into account the fact that it all falls into the set, then when clearing full or differentials, they do not differ, why? I assume that given the fact that both the full and the differential have the bak extension, they are perceived in the same way and there are no "marks" for their difference, as I understand it? That is, if in the service plans on differentials I do to clear everything that is earlier than 4 weeks, then I will lose the full ones for the previous months, since they will be in the same folder in one set with differentials.
2) Based on paragraphs 1 and 1a, I made 3 folders separately for full, differentials, magazines. Everyone goes to their place as a result, when cleaning the data, the diffs will only clean their own folder, full only their own in accordance with the maintenance plans using the "clean after maintenance" object. First crutch!
3) Proceeding from point 2, I do a test recovery from the device. At a recovery stage from full I do "overwrite basis". With the option for full and diff RESTORE WITH NONRECOVERY through the recovery partition files and filegroups.
The problem specifically with logs begins. When manually restoring logs one at a time, it makes sense through files and file groups, but given the fact that there will be a lot of them, if you have to restore closer to the end of the day at the end of the week, you can hesitate to manually restore logs one at a time. When trying to restore transaction logs as a group through files and filegroups - an error, in the screenshot below.
c51752b1b67a4233bfa5a590a6594d75.png
According to the logic of things, as described in the documentation and on the Internet, you need to restore transaction logs through the recovery section, so at first I did it, then I already dabbled with "files of file groups". When I tried to restore the transaction logs through the recovery partition, it was initially let, but I constantly restored it back and forth on this base 3-5 times and he still remembered my recovery slightly inside out. First, he saw the log that was made before the restore, then the actual logs that I previously restored one by one, now they are seen as a group, but even then they are not restored, the screenshot is below.
f693a4e9f3634148b680c19bfd571aa7.png
That is, if you don’t play with recovery, but in reality on a working base I will reach the moment of restoring transaction logs after a full and differential, in the recovery section “transaction logs” will be empty and will have to be restored one at a time. The second crutch!
4) A separate question is how to calculate the size of backups if they have not yet configured a reservation in order to have an idea which screw to allocate for it. It is clear that with this model, the question will arise how much the data changes. But if you just take a calculator and abstract information security, then what will be the calculation, for example, the base is 70 GB, the base changes per day, say, by 15%.
- What is the size of a full copy with such data when compressed?
- What is the size of the diff?
- What is the size of the magazine?
It seems to me that the calculation in the forehead will not work, because I may have missed some details of the SQL SERVER itself?
4a) The second point, how can you track how specifically IB changes per day? Is there any mechanism so that this is not checked by eye, they say, here is 15%, that is, do not pre-complete, differential, log and then calculate the required volume on your knees, and so that MSSQL itself gives out how much IS has changed compared to the beginning say day?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question