SQL Server 2008: backup wisely. Part 1: Theory
Good afternoon friends. In this article, I would like to tell you what you should think about before setting up a database backup system. Despite the fact that the use of this approach with MS SQL Server is primarily considered, the principles outlined here are easily projected onto any other technology. Well, let's go.
If it so happens that you are responsible for maintaining the safety of the databases and the smooth functioning of the SQL server, then surely the first thing you think about will be backups - trite because if all of a sudden something happens to your databases Somehow trouble, but you do not have backup, you are waiting for not the most pleasant future. But along with the bright idea of ​​the need for backups, there are a million of the most diverse questions: where to start? to reserve all bases together, or is it better to each separately? to do a full backup? Or maybe it is better to reserve only logs? Well, let's try to figure it out.
')
First of all, you should decide on two fundamental questions:
The first question is more or less clear - it is needed in order to determine the global strategy. Obviously, the more important a particular database is, the earlier and more often backups should be made for it. If the company uses all databases with approximately the same degree of intensity, then the best option would most likely be to create a unified backup plan. On the other hand, if you have some databases that are used much more often and are loaded much more than others, you may want to consider planning their backups independently of each other.
But the second question is not so smooth. At least because in response, the absolute majority of managers will tell you about the inadmissibility of data loss for any period of time. In the meantime, we all perfectly understand that the organization of a fail-safe infrastructure with a reduced to zero chance of data loss is a difficult, expensive and unsuitable business for small and medium-sized companies. Try to explain your position and get some more definite answer - a day, an hour, half an hour, five minutes, or any other number you can work with.
Only when you have specific and unambiguous answers to these questions, can you properly evaluate the pros and cons of different approaches to creating backups.
A full backup is the foundation on which your entire backup system will hold. By and large, this is a copy of the database, which includes everything in general - tables, data, indexes, triggers, statistics, stored procedures and many more good stuff. Moreover, if you choose the option of dynamic backup (that is, during data backup, users can fully work with the saved database), then all changes that users make while backup is created will also be saved. You can easily return the database to the state in which it was at some particular moment, if you have a full backup made at that time.
The main question related to full backups is how often should they be done? There is no universal answer. It is clear that full backups must be performed at least once a week. If your databases are not too large and there are no special problems with the space for backups, it is possible that reserving once a day would be a better option. On the other hand, too frequent full backups will load your server and will require an unreasonable amount of disk space for storing backups. The exact figure depends on the size of your databases, the performance and server load, the answer to “fundamental question number two” and how often you will make other types of backups, for example, log backups or differential backups.
A log backup differs from a full backup in that it includes only database changes (that is, INSERT, UPDATE and DELETE operations) since the last backup, whether it be a full backup, a differential backup, or a previous backup. Since the amount of stored data is extremely small, this type of backup is much faster, requires less resources and takes up less disk space. Unfortunately, there are also some shortcomings. First of all, backup log is useless if you do not have at least one full backup. This is explained by the fact that no information about tables, indexes, stored procedures, and so on is stored in this log. The second major drawback is that if since the last full backup you managed to make a hundred log backups, and then there was a trouble, before you restore the hundredth cherished, you will need to restore not only the full backup, but also the previous ninety-nine log backups, besides in the right order. Agree, there is not very much pleasant in such a prospect. Another important feature is that the backup log is available only for those databases that have FULL or BULK LOGGED recovery mode specified.
In order to be able to create backups of the log, this logging itself must be enabled. Although this option works by default and provides many great features, there are situations where it would be wiser to refuse to use it. First of all, it is better to disable logging if you are going to add to the database or change a lot of data in it. Such an operation may take more than one hour, but with disabled logging it will be executed much faster. Once the data is added, you can safely make a full backup of the database and enable logging. The second piquant point is that the log should be very closely monitored - if the memory that is available for recording the log ends, the system stops all ongoing transactions until the memory is released. To avoid such a sad situation, you need to make a backup of the log as often as possible - then as soon as you make a more recent backup, the system marks the saved portion of the log as available for overwriting, thereby restoring the occupied disk space.
How often to make backups of the log? Again, there is no universal answer. On the one hand, the more often you backup the log, the more flexibility you get during the recovery and the less data you lose in case of any incidents. On the other hand, if you make a full backup once a day, and a backup of the log once a minute, then with the worst scenario, you will have to restore almost one and a half thousand backups before the base returns to the state that can be considered the last worker. Not only that such a prospect does not in itself inspire for feats, but also at this time there will be leaders, directors and all who are not lazy, asking about when everything will be ready and why for so long. I think that backing up the log more than once every five minutes will almost always be a bad idea. There is an opinion that the backup of the log less than once per hour also does not make sense, but this statement seems somewhat controversial to me. In any case, if you keep within these limits, most likely it will be a good decision.
Differentiated backup is something between a full backup and a backup of a log. It saves the changes made since the last full backup to the present. Its main advantage over the backup of the log is that to restore the database completely, you only need to restore the full and last differentiated backups. The disadvantage is that you cannot restore the intermediate state of the database at some point in time - the history of database changes in the differential backup is not saved. As with the backup log, such a backup is useless if you do not have a full copy of the database.
Finally, I would like to give you some tips that have no place in the main narrative, but which are too important to keep silent about them.
On this, perhaps, everything. I want to immediately note that in this article I deliberately did not consider such moments as backup files, backup without cleaning the log and much more. Next time I will try to make a more “practical” article, talk about the various ways to automate the creation of backup copies and give examples of code. I hope you learned something new and useful for yourself. Have a nice day.
The first steps
If it so happens that you are responsible for maintaining the safety of the databases and the smooth functioning of the SQL server, then surely the first thing you think about will be backups - trite because if all of a sudden something happens to your databases Somehow trouble, but you do not have backup, you are waiting for not the most pleasant future. But along with the bright idea of ​​the need for backups, there are a million of the most diverse questions: where to start? to reserve all bases together, or is it better to each separately? to do a full backup? Or maybe it is better to reserve only logs? Well, let's try to figure it out.
')
First of all, you should decide on two fundamental questions:
- How big is the role of each separate database for the company? and
- Data loss over what time period can be considered acceptable?
The first question is more or less clear - it is needed in order to determine the global strategy. Obviously, the more important a particular database is, the earlier and more often backups should be made for it. If the company uses all databases with approximately the same degree of intensity, then the best option would most likely be to create a unified backup plan. On the other hand, if you have some databases that are used much more often and are loaded much more than others, you may want to consider planning their backups independently of each other.
But the second question is not so smooth. At least because in response, the absolute majority of managers will tell you about the inadmissibility of data loss for any period of time. In the meantime, we all perfectly understand that the organization of a fail-safe infrastructure with a reduced to zero chance of data loss is a difficult, expensive and unsuitable business for small and medium-sized companies. Try to explain your position and get some more definite answer - a day, an hour, half an hour, five minutes, or any other number you can work with.
Only when you have specific and unambiguous answers to these questions, can you properly evaluate the pros and cons of different approaches to creating backups.
Full backup
A full backup is the foundation on which your entire backup system will hold. By and large, this is a copy of the database, which includes everything in general - tables, data, indexes, triggers, statistics, stored procedures and many more good stuff. Moreover, if you choose the option of dynamic backup (that is, during data backup, users can fully work with the saved database), then all changes that users make while backup is created will also be saved. You can easily return the database to the state in which it was at some particular moment, if you have a full backup made at that time.
The main question related to full backups is how often should they be done? There is no universal answer. It is clear that full backups must be performed at least once a week. If your databases are not too large and there are no special problems with the space for backups, it is possible that reserving once a day would be a better option. On the other hand, too frequent full backups will load your server and will require an unreasonable amount of disk space for storing backups. The exact figure depends on the size of your databases, the performance and server load, the answer to “fundamental question number two” and how often you will make other types of backups, for example, log backups or differential backups.
Log backup
A log backup differs from a full backup in that it includes only database changes (that is, INSERT, UPDATE and DELETE operations) since the last backup, whether it be a full backup, a differential backup, or a previous backup. Since the amount of stored data is extremely small, this type of backup is much faster, requires less resources and takes up less disk space. Unfortunately, there are also some shortcomings. First of all, backup log is useless if you do not have at least one full backup. This is explained by the fact that no information about tables, indexes, stored procedures, and so on is stored in this log. The second major drawback is that if since the last full backup you managed to make a hundred log backups, and then there was a trouble, before you restore the hundredth cherished, you will need to restore not only the full backup, but also the previous ninety-nine log backups, besides in the right order. Agree, there is not very much pleasant in such a prospect. Another important feature is that the backup log is available only for those databases that have FULL or BULK LOGGED recovery mode specified.
In order to be able to create backups of the log, this logging itself must be enabled. Although this option works by default and provides many great features, there are situations where it would be wiser to refuse to use it. First of all, it is better to disable logging if you are going to add to the database or change a lot of data in it. Such an operation may take more than one hour, but with disabled logging it will be executed much faster. Once the data is added, you can safely make a full backup of the database and enable logging. The second piquant point is that the log should be very closely monitored - if the memory that is available for recording the log ends, the system stops all ongoing transactions until the memory is released. To avoid such a sad situation, you need to make a backup of the log as often as possible - then as soon as you make a more recent backup, the system marks the saved portion of the log as available for overwriting, thereby restoring the occupied disk space.
How often to make backups of the log? Again, there is no universal answer. On the one hand, the more often you backup the log, the more flexibility you get during the recovery and the less data you lose in case of any incidents. On the other hand, if you make a full backup once a day, and a backup of the log once a minute, then with the worst scenario, you will have to restore almost one and a half thousand backups before the base returns to the state that can be considered the last worker. Not only that such a prospect does not in itself inspire for feats, but also at this time there will be leaders, directors and all who are not lazy, asking about when everything will be ready and why for so long. I think that backing up the log more than once every five minutes will almost always be a bad idea. There is an opinion that the backup of the log less than once per hour also does not make sense, but this statement seems somewhat controversial to me. In any case, if you keep within these limits, most likely it will be a good decision.
Differential Backup
Differentiated backup is something between a full backup and a backup of a log. It saves the changes made since the last full backup to the present. Its main advantage over the backup of the log is that to restore the database completely, you only need to restore the full and last differentiated backups. The disadvantage is that you cannot restore the intermediate state of the database at some point in time - the history of database changes in the differential backup is not saved. As with the backup log, such a backup is useless if you do not have a full copy of the database.
Instead of postscript
Finally, I would like to give you some tips that have no place in the main narrative, but which are too important to keep silent about them.
- Use DBCC CHECKDB to check each database before copying, this will prompt you in the time of impending problems.
- Use the BACKUP WITH CHECKSUM option to make sure everything went well. The disadvantage of this solution is that for large databases, checksum verification can seriously load the system.
- If you have problems with the disk space available for storing backups, use the BACKUP WITH COMPRESSION option. Compression allows you to reduce the final file size with backup several times, usually in 3-5. As is the case with checksum checking, this operation has a very serious effect on performance, especially for large databases. Remember that the compression option is available only for Enterprise Editions of SQL Server 2008 and Enterprise and Standard Edition 2008R2. All other versions, including the developer versions, will not be able to compress the backup when backing up, or “unclench” it when restoring.
- Create a separate file for each backup, do not store all backups in one file. In this case, if something happens to this file, you will lose one backup, and not all.
- Naturally, do not store the backup on the same disk where your database is stored.
- If there is a threat that someone else will have access to your backups, use password protection or encryption.
- Automate the process of creating backups. It's simple, and you will be sure that you always have a fresh copy of the database.
- Backups are useless if you can't use them. Therefore, regularly practice in restoring databases, so that if necessary you can do everything quickly and accurately in a stressful situation.
On this, perhaps, everything. I want to immediately note that in this article I deliberately did not consider such moments as backup files, backup without cleaning the log and much more. Next time I will try to make a more “practical” article, talk about the various ways to automate the creation of backup copies and give examples of code. I hope you learned something new and useful for yourself. Have a nice day.
Source: https://habr.com/ru/post/131300/
All Articles