Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of SQL Server 2008 data backup and restore. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. Recovery model

Right-click the database properties-- options-- you can see the "recovery model". There are three SQL Server 2008 database recovery models: full recovery model, bulk-logged recovery model, and simple recovery model, as shown in the following figure.

Full recovery model

Is the default recovery model. It records every step of the operation of the database. Using the full recovery model, you can restore an entire database to a specific point in time, which can be the last available backup, a specific date and time, or a marked transaction.

Bulk log recovery model

To put it simply, it is necessary to minimize logging of high-volume operations to save log file space (such as importing data, batch updates, SELECT INTO, etc.). For example, when hundreds of thousands of records are inserted into the database at one time, every action of inserting records under the full recovery model will be recorded in the log, making the log file very large. Under the mass log recovery model, only the necessary operations will be recorded, not all logs. This can greatly improve the performance of the database, but because the logs are incomplete, the data may not be able to recover in case of problems. Therefore, it is common to change the recovery model to the bulk-logged recovery model only when a large number of data operations are required, and immediately change the recovery model back to the full recovery model after the data has been processed.

Simple recovery model

In this mode, the database automatically deletes the inactive logs, thus simplifying the restore of the backup, but because there is no transaction log backup, it cannot be restored to the point in time that failed. In general, this mode is only used for databases that do not have high requirements for database data security, and in this mode, databases can only do full and differential backups.

It can be seen that the difference between the three recovery models lies in the different ways to deal with "logs". In terms of the size of "logs": full recovery model > bulk-logged recovery model > simple recovery model.

2. Backup method

SQL Server 2008 provides four backup methods: full backup, differential backup, transaction log backup, file and filegroup backup.

Full backup

Back up everything in the entire database, including the transaction log. This type of backup requires a large storage space to store backup files, and the backup time is relatively long, so when restoring data, only one backup file is restored.

Differential backup

Differential backups are complementary to full backups, backing up only the data that has changed since the last full backup. Compared with the full backup, the amount of data of the differential backup is smaller than that of the full data backup, and the backup speed is faster than the full backup. Therefore, differential backup is usually used as a common backup method. When restoring data, you should first restore the previous full backup, and then restore the differential backup made *, so that the data in the database can be restored to the same content as the differential backup.

Transaction log backup

Transaction log backups only back up the contents of the transaction log. The transaction log records all changes to the database since the last full backup or transaction log backup. The transaction log records changes in the database over a certain period of time, so a full backup must be made before the transaction log backup. Similar to differential backups, transaction log backups generate smaller files and take less time, but when restoring data, in addition to restoring the full backup, you have to restore each transaction log backup in turn. Instead of restoring only one transaction log backup (this is the difference from a differential backup).

File and filegroup backup

You can use this backup method if you created multiple database files or filegroups for the database when you created the database. Using the file and filegroup backup method, you can back up only some files in the database, which is very effective when the database files are very large. Because only one or several files or filegroups are backed up at a time, you can back up the database multiple times to avoid long backup time for large databases. In addition, since file and filegroup backups only back up one or more of the data files, when one or some files in the database are corrupted, only corrupted files or filegroup backups may be restored.

3. Give an example

Full backup

For example, if a full backup is made at 8: 00 a.m. on January 1, 2012, the database state can be restored to the same database state as at 8: 00 a.m. on January 1, 2012.

Differential backup

Differential backup is the change of data after a full backup. For example, after a full backup was performed at 8 a.m. on January 1, 2012, a differential backup was performed on January 2 and January 3, respectively, so the data changes from January 1 to January 2 were recorded in the January 2 differential backup, and the data changes from January 1 to January 3 were recorded in the January 3 differential backup. Therefore, if you want to restore to the state of January 3, just restore the full backup made on January 1 first, and then restore the differential backup made on January 3.

Transaction log backup

Transaction log backup takes the transaction log file as the backup object, which is equivalent to recording every operation in the database. Suppose that after a full backup is made at 8: 00 a.m. on January 1, 2012, by 8: 00 a.m. on January 2, the data in the database has changed 100 times. If a differential backup is made at this time, then the differential backup records the database status after the 100th data change, while if the transaction log backup is done at this time, the 100 data changes will be backed up.

To give another example, for example, after a full backup was made at 8: 00 a.m. on January 1, 2012, transaction log backups were made on January 2 and January 3, then the transaction log backup on January 2 recorded the data changes during the period from January 1 to January 2. The transaction log backup on January 3 records data changes from January 2 to January 3. Therefore, if you want to restore the data to January 3, you need to restore the full backup made on January 1, then restore the transaction log backup made on January 2, and * restore the transaction log backup made on January 3.

4. the choice of backup mode.

After understanding the above database backup methods, you can use the above methods to back up the database for your own database. Reasonable backup of the database needs to consider several aspects, the first is data security, the second is the backup file size, * is the time range for backup and restore.

The change of data is small.

For example, if the amount of data in the database that changes every day is very small, you can do a full backup once a week (Sunday) and a transaction log backup every day (before leaving work), then if there is a problem with the database, you can restore the data to the state of the previous day (at the end of the day).

Of course, you can also do a full backup once a week (Sunday) and a differential backup every day (before leaving work), so that if there is a problem with the database, you can also restore the data to the state it was when you got off work the day before. It's just that when you do differential backups in the last few days of the week, the backup time and files will increase. But this also has an advantage, when the data is corrupted, as long as the full backup of the data and the previous day's differential backup of the data can be, do not need to restore every day's transaction log backup, the recovery time will be relatively short.

The change of data is large.

If the data in the database changes frequently and the data lost for an hour is a very serious loss, it is not feasible to back up the data with the above method. At this time, three backup methods can be used alternately to back up the database.

"for example, make a full backup at the end of the day, a differential backup every eight hours between two full backups, and a transaction log backup every hour between two differential backups." In this way, once the data is corrupted, the data can be restored to the state of less than one hour, and at the same time, the time of database backup data and the size of backup data files can be reduced.

The database file is large

It was also mentioned earlier that when database files are too large and not easy to back up, you can back up database files or filegroups separately and back up a database multiple times. In reality, there is another situation that can be used to back up database files. For example, in a database, the data in some tables change little, while the data in some tables change frequently, then consider storing these data tables in different files or filegroups, and then back up these files and filegroups with different backup frequency. However, files and filegroups are used for backup, and it takes several times to restore the entire database, so do not use this backup method unless the database files are too large to back up.

Tail log backup

In view of the above backup scheme, can you see that the data is still incomplete? For example, a full backup was made at 12:00 last night, and a transaction log backup was made every other hour. * A transaction log backup is 12:00 today. It is now 12:10 today. It is found that the database data has been lost or destroyed. But * A transaction log backup is at 12:00 today, if I restore the database to 12:00 at this time. Then the operation data that has not been corrupted after 12:00 to 12:10 will be lost (for example, the database has three tables, the data of one table is corrupted, and the data of the other two tables are changed by other users). At this point, you need to use "tail log backup". The principle of tail log backup is to start from the point in time of a transaction log backup and back up all subsequent operations. When you restore, you can find the correct data for operations after 12:00.

Note: when making a tail log backup, the database will be forced to stop the database, and if the database is not stopped and the user continues to operate, the tail log backup will be meaningless. SQL Server 2012 if you have made changes to the data after backing up the transaction log once, that is, the transaction log (that is, the LSN (log sequence number) recorded in the current log file) is greater than the * * LSN,SQL Server recorded in a transaction log backup to distinguish the log records through LSN), and the tail log has not been backed up yet, it will prompt you and require you to do a tail backup first.

5. Sample operation

First make a full backup [MyTest.bak], and then make two transaction log backups based on this backup file, * * one transaction log backup time is [2012-8-4 23:07]. The following image: backup file name is [MyTest.bak], * * A transaction log backup time is [2012-8-4 23:07]

Below: after selecting the backup file "MyTest.bak", you can see that the backup file appears in the "backup set to restore" list, listing the full backup file and two transaction log backups, respectively.

If the transaction log backup time of the above backup files is [2012-8-4 23:07], you can restore to any point in time from the beginning of the * full backup to the transaction log backup time of * *. This fully validates the transaction log backup method mentioned above (if a tail log backup is performed later The trailing log record is listed in the * * line in the backup set to restore list during restore.

If there are two transaction log backups, namely "transaction log 1" and "transaction log 2", the check box of "transaction log 1" is removed during restore, and "transaction log 2" is also automatically removed. But only "transaction log 2" is allowed, which fully verifies what has been mentioned above: "when restoring data, in addition to restoring the full backup, you must restore each transaction log backup in turn." Instead of restoring only one transaction log backup, unlike a differential backup, you can restore only one differential backup based on a full backup.

If you insert a piece of data at [2012-08-04 23:36:53], [2012-08-04 23:37:44] makes another transaction log backup 3, and then restore the time point to [2012-08-04 23:36:00], there will be no data after that point.

[note: when restoring, the database will be prompted to be in use. Generally, you have to disconnect all connections before you can restore. I usually use scripts to bring the data back online, and all connections will be disconnected.]

This is the end of this article on "sample Analysis of SQL Server 2008 data backup and restore". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.