Understanding and summary of SQL Server database backup and restoration (1)

Many may have some knowledge about SQL Server backup and restoration, or may often use the backup and restoration functions, I believe that most of our developers except DBA only use the most basic backup and restoration functions, and they only have a general understanding of it. If you have a deeper understanding of it, is it better to understand its more comprehensive functions, and it will be handy when used. Because the management staff of small and medium customer companies often do not know the database or are not well-versed in it, our technical staff will be asked to immediately retrieve the lost data or hardware damage to mobile data, or other situations.

First, let's start with "recovery mode" of the database. If the recovery mode is set incorrectly, data cannot be restored.

SQL Server 2012 and SQL Server 2008 have not changed much about database backup and restoration.Time point selection UIAnd the relevant restoration options have been slightly changed, and the backup and restoration core have not changed. This is only a personal understanding and understanding, for your reference only. For details, please refer to the official documentation. The following uses SQL Server 2012 SSMS (SQL Server Management Studio) for a legend demonstration.

SQL Server 2008 database recovery modes include full recovery mode, large-capacity log recovery mode, and simple recovery mode.

◆ Full recovery mode. The default recovery mode. It records all the steps for operating the database. The full recovery mode can be used to restore the entire database to a specific time point, which can be the last available backup, a specific date and time, or a marked transaction.

◆ Large-capacity log recovery mode. It is a supplement to the full recovery mode. To put it simply, you need to perform a minimum log record for large-capacity operations to save the space of log files (such as data import, batch update, and select into operations ). For example, when hundreds of thousands of records are inserted into the database at a time, in full recovery mode, each insert record action is recorded in the log, making the log file very large, in the large-capacity log recovery mode, only necessary operations are recorded, but not all logs are recorded. As a result, the database performance can be greatly improved. However, due to incomplete logs, once a problem occurs, data may not be restored. Therefore, the recovery mode is generally changed to the large-capacity log recovery mode only when a large amount of data operations are required. After the data processing is complete, the recovery mode is immediately changed back to the complete recovery mode.

◆ Simple recovery mode. In this mode, the database automatically deletes non-active logs, which simplifies the backup restoration. However, because there is no transaction log backup, it cannot be restored to the failed time point. Generally, this mode is only used for databases with low requirements on database data security. In this mode, the database can only perform full and differential backup.

After learning about the restoration mode of the database, you should know the mode for setting your database. To back up a database, SQL Server 2008 provides four backup methods: full backup, differential backup, transaction log backup, file and file group backup.

◆ Full backup: Back up all contents of the entire database, including transaction logs. This backup type requires a large storage space to store backup files, and the backup time is also relatively long. When restoring data, you only need to restore a backup file.

◆ Differential backup: differential backup is a supplement to full Backup. Only the data changed after the last full backup is backed up. Compared with the full backup, the data volume of differential backup is smaller than that of full backup, and the backup speed is faster than that of full backup. Therefore, differential backup is usually used as a common backup method. When restoring data, you must first restore the complete backup made before, and then restore the differential backup made last time, in this way, the data in the database can be restored to the same content as the last differential backup.

◆ Transaction log backup: The transaction log backup only backs up the content in the transaction log.Transaction logs record all database changes after the last full backup or transaction log backup.Transaction logs record database changes within a certain period of time. Therefore, you must perform a full backup before backing up transaction logs. Similar to differential backup, the file generated by the transaction log backup is small and takes a short time. However, when restoring data, in addition to restoring the complete backup, each transaction log backup must be restored in sequence, instead of restoring only the last transaction log backup (This is different from differential backup.).

◆ File and file group backup. If multiple database files or file groups are created for the database during database creation, you can use this backup method. You can use file and file group backup to back up only some files in the database. This backup method is very effective when the database files are very large. Because only one or several files or file groups are backed up at a time, you can back up the database multiple times to avoid excessive backup time for large databases. In addition, because file and file group backup only backs up one or more data files, when one or more files in the database are damaged, only corrupted files or file group backup may be restored.

    Full BackupIt may be easy to understand. For example, if a full backup is performed at on January 1, January 1, 2012, the database status will be restored to on January 1, January 2012.

    Differential backupIs the data changes after the full backup is backed up. For example, after a full backup was performed at on January 1, January 1, 2012, the differential backup was performed on January 1, January 2 and January 1, January 3 respectively, in the differential backup on January 1, data changes were recorded from January 2, to January 2, in the differential backup on January 1, January 3, data changes were recorded from January 1, January 1-1 to November 3. Therefore, if you want to restore the status to January 3, you only need to restore the full backup made in January 1, and then restore the differential backup made in January 3.

    Transaction logBackup is a transaction log file as a backup object, which is equivalent to recording every operation in the database. Assume that after a full backup is performed at on January 1, January 1, 2012, the data in the database has changed January 2 times until on January 1, 100. If a differential backup is performed at this time, the differential backup records the database status after 100th data changes. If transaction log backup is performed at this time, the backup records the 100 data changes.

For example, after a complete backup was performed at on January 1, January 1, 2012, transaction logs were backed up on January 1, January 2 and January 3, therefore, the transaction log backup on January 1, records data changes from January 2, to January 2, in the transaction log backup on January 1, January 3, data changes were recorded from January 1, January 2-1 to November 3. Therefore, if you want to restore data from January 1, January 3, you must first restore the complete backup made on January 1, January 1, then the transaction log backup made on January 1, January 2, and finally the transaction log backup made on January 1, January 3.

After learning about the above database backup methods, you can back up your database using the above methods. To back up a database properly, you must consider the following aspects: data security, backup file size, and backup and restoration.

For example, if the daily data volume in the database is small, you can perform a full backup every week (Sunday) and a transaction log backup every day (before work), once a database problem occurs, data can be restored to the previous day (when off work.

Of course, you can also perform a full backup on Sunday and a differential backup before getting off work every day from Monday to Saturday. In this way, if a database problem occurs, you can also recover the data to the status of the day before and after work. Only when differential backup is performed in the next few days of a week, the backup time and backup files will increase. However, this also has the advantage that when data is damaged, you only need to restore the full backup data and the differential backup data of the previous day. You do not need to recover the transaction log backup of each day, recovery takes a short time.

If the data in the database changes frequently and the loss of data for an hour is a very serious loss, it is impossible to back up the data using the above method, in this case, you can use three backup methods to back up the database.

For example, perform a full backup every day after work, perform a differential backup every eight hours between the two full backups, and perform a transaction log backup every one hour between the two differential backups. In this way, once the data is damaged, the data can be restored to the state within the last hour, while reducing the database backup time and the size of the backup data file.

As mentioned above, when the database file is too large to be easily backed up, you can back up the database file or file group separately to back up the database multiple times. In actual operations, you can also use the backup of database files. For example, in a database, data in some tables is rarely changed, while data in some tables is often changed, you can consider storing these data tables in different files or file groups, respectively, then, the files and file groups are backed up at different backup frequencies. However, if you use files and file groups for backup, you must restore the entire database multiple times before restoring the data. Therefore, do not use this backup method unless the database files are too large to be backed up.

For the above backup scheme, can we see that the data is still incomplete? For example, a full backup was made at yesterday night, and a transaction log was backed up every hour. The last transaction log backup was made at noon today, and now it is noon today, if the database data is lost or damaged, the last transaction log backup is noon today. If I restore the database, the operation data that has not been damaged since PM to PM will be lost (for example, the database has three tables, and the data of one table is damaged, the data of the other two tables is changed by other users ). In this case, you need to use [tail log backup]. The tail log backup principle is to back up all subsequent operations from the last transaction log backup time point, during restoration, you can find the correct data after.

Note: During tail log backup, the database will be forced to stop. If you do not stop the database and continue the operation, tail log backup will be meaningless. SQL Server 2012 if you have modified the data after the last transaction log backup, that is, the Transaction Log (that is, the LSN (log serial number) recorded in the current log file) this is greater than the maximum LSN recorded in the last transaction log Backup. SQL Server uses LSN to identify log records, it prompts and requires you to back up the tail first.

    Instance section:

◆ Perform a full backup [MyTest. bak], and then perform two transaction log backups based on the backup file. The last transaction log backup time is]

Figure 1-1: The backup file name is MyTest. bak, and the last transaction log backup time is]

Figure 1-2: Select the backup file "MyTest. after bak, you can see the backup file in the "backup set to be restored" list, which lists the complete backup file and two transaction log backups respectively.

The last transaction log backup time of the backup file is [], so you can restore it to the beginning of the first full backup, at any time point during the last transaction log backup time, this fully verifies the transaction log backup method described above (if tail log backup is performed later, the last line in the "backup set to be restored" list will list the tail log records ).

If there are two transaction log backups: "Transaction log 1" and "transaction log 2", remove the "transaction log 1" check box during restoration, "Transaction log 2" is automatically removed, but only "transaction log 2" is allowed, which fully verifies the aforementioned:"When restoring data, in addition to restoring the complete backup, you must restore each transaction log backup in sequence, instead of just restoring the last transaction log backup.",Compared with differential backup, you can only restore the last differential backup based on the complete backup..

Figure 1-3: I inserted a piece of data [23:36:53] and [23:37:44] performed another transaction log backup 3. Then I restored the time point to [23:36:00 ], after restoration, the data after this time point will no longer exist.

[Note: During restoration, the system will prompt that the database is in use. Generally, you must disconnect all connections before restoring the database. I usually use scripts to bring data offline and then bring it online. All connections will be disconnected]

Alter database MyTest set offline with rollback immediate -- offline
Alter database MyTest set online with rollback immediate -- online

   

◆ If you last performed a full backup, it is not allowed to restore it to a certain time point based on the full backup.

Figure 2-1: The backup file name is MyTestA. bak and the backup time is]

Figure 2-2: Select the last full backup file MyTestA. The backup file is displayed in the "backup set to be restored" list, then click "timeline" to go to the "backup timeline" interface, and change the last full backup time from "22:33:41" to any previous time point, for example, "22:32:41 ", then confirm and you will see the effect of Figure 2-3.

Figure 2-3: restoring to a certain point in time based on the full backup is not allowed, because no differential backup or transaction log backup is performed after the full backup. The list of backup sets to be restored is empty and cannot be restored.