SQL Server database backup and restore awareness and summary

Start with the database "recovery Model", because the database cannot be restored if the recovery mode is set incorrectly.

SQL Server 2012 does not substantially change database backup and restore with SQL Server 2008, only minor changes to the restore point selection UI and associated restore options, and the backup and restore core has not changed, it is personal mastery and understanding, for reference only, Please also use the Official document as the basis. The following is illustrated with SQL Server SSMS (SQL Server Management Studio).

The SQL Server 2008 Database Recovery model is divided into three types: The full recovery model, the bulk-logged recovery model, and the simple recovery model.

The full recovery model. is the default recovery mode. It will fully record every step of the operation of the database. Using the full recovery model, you can restore the entire database to a specific point in time, which can be the most recent backup available, a specific date and time, or a marked transaction.

Bulk-Logged recovery model. It complements the full recovery model. To put it simply, it is necessary to minimize logging of bulk operations and save space for log files (such as importing data, batch updating, SELECT into, and so on). For example, when you insert hundreds of thousands of records in a database, in the full recovery model, the action of each inserted record is recorded in the log, making the log file very large, and in the bulk-logged recovery model, recording only the necessary actions and not logging all, which can greatly improve the performance of the database, However, because the log is incomplete, the data may not be recoverable once a problem occurs. Therefore, the recovery model is typically changed to the bulk-logged recovery model only when a large number of data operations are required, and the recovery model is changed back to the full recovery model immediately after the data has been processed.

Simple recovery model. In this mode, the database automatically deletes inactive logs, thus simplifying the restore of backups, but cannot revert to a failed point in time because there is no transaction log backup. Typically, this mode is used only for databases where the security requirements for database data are not high. And in this mode, the database can only do full and differential backups.

After you understand the above database restore mode, you should generally know how to set your own database to a different mode. Backup database, SQL Server 2008 provides four ways to backup: Full, differential, transaction log, file, and filegroup backups.

Full backup: Backs up all the contents of the entire database, including the transaction log. The backup type requires a larger storage space to store the stored files and a longer backup time, as long as you restore the data, only one backup file is restored.

Differential backups: A differential backup is a complement to a full backup that backs up only the data that has changed since the last full backup. For a full backup, the data for the differential backup is smaller than the full data backup, and the backup is faster than the full backup. Therefore, differential backups are often used as a common backup method. When you restore the data, you restore the previous full backup, and then restore the last differential backup, so that the data in the database is restored to the same content as the last differential backup.

Transaction log backups: transaction log backups only back up content in the transaction log. The transaction log records all the changes to the database after the last full or transaction log backup. The transaction log records changes in the database over a period of time, so you must make a full backup before you make a transaction log backup. Like differential backups, transaction log backups generate smaller files and take less time, but when restoring data, in addition to restoring the full backup first, restore each transaction log backup in turn instead of restoring only the last transaction log backup (which is different from differential backups).

File and filegroup backups. You can use this backup method if you created multiple database files or filegroups for your database when you created the database. File and filegroup backups allow you to back up only some of the files in the database that are effective when the database file is large, because you can back up the database multiple times, avoiding large database backups, by backing up only one or several files or filegroups at a time. Also, because file and filegroup backups back up only one or more of the data files, a corrupted file or filegroup backup may be restored only if one or more of the files in the database are corrupted.

A full backup might be better understood. For example, a full backup at 8 points on the morning of January 1, 2012 will allow you to revert to the state of the database by January 2012, at 8 in the morning of 1st, at the time of the restore.

Differential backups are data changes after a full backup is backed up. For example, after a full backup at 8 in the morning of January 1, 2012, a differential backup was made on January 2 and January 3, and the variance backup of January 2 recorded data changes from January 1 to January 2, In the January 3 differential backup, the data was recorded for the period from January 1 to January 3. Therefore, if you want to revert to the state of January 3, just restore the full backup of January 1, and then restore the differential backups made on January 3.

A transaction log backup is a transaction log file as a Backup object, which is equivalent to recording every operation in the database. Assuming that after a full backup at 8 on the morning of January 1, 2012, the data in the database has changed 100 times until 8 o'clock in the morning January 2, and if a differential backup is made, then the differential backup records the state of the database after the 100th data change, and if a transaction log backup is done at this time, The backup will be the 100 data changes.

Take another example, for example, after a full backup on the morning of January 1, 2012 at 8, and a transaction log backup on January 2 and January 3, the transaction log backups in January 2 record data changes from January 1 to January 2, In the transaction log backup of January 3, data changes were recorded during the period from January 2 to January 3. Therefore, if you want to restore the data to January 3, you need to restore the full backup of January 1, and then restore the transaction log backups done January 2, and finally restore the transaction log backups made by January 3.

With the above database backup methods in view, you can use the above methods for your own database to back up the database. A reasonable backup of the database needs to consider several aspects, first is data security, followed by the backup file size, and finally do backup and restore can withstand the time range.

For example, if the amount of data that changes daily in a database is small, you can make a full backup every week (Sunday), and then do a transaction log backup every day (before work), and once the database is in trouble, you can restore the data to the previous (off-duty) state.

Of course, it's also possible to make a full backup in Sunday, make a differential backup from Monday to Saturday every day before work, so that once the database has a problem, the data can be restored to the same state as it was when the clock was off. Just a few days after a week. When you make a differential backup, the time of the backup and the files that are backed up are incremented. But this also has the advantage, when the data is corrupted, as long as restores the full backup the data and the previous day differential backup data, does not need to restore the daily transaction log backup, restores the time to be relatively short.

If the data in the database changes more frequently, the loss of one hours of data is very serious loss, the above method to back up the data is not possible, at this time can alternate use of three backup methods to back up the database.

For example, make a full backup every day after work, make a differential backup every eight hours between two full backups, and do a transaction log backup every other hour between two differential backups. As a result, data corruption can restore data to a state within the last one hours, while reducing the time that the database backs up data and the size of the backup data file.

As mentioned earlier, when a database file is too large to be backed up, you can back up the database files or filegroups separately and back up the database multiple times. In real-world operations, there is also a situation where you can use a backup of a database file. For example, in a database where the data in some tables changes very little, and some of the table's data changes frequently, you might consider storing them in separate files or filegroups, and then backing up the files and filegroups with different backup frequencies. But use files and filegroups for backups, and restore data multiple times to restore the entire database, so do not use this backup unless the database file is large to backup.

For the above backup solution, can you see that the data is incomplete? For example, yesterday night 12 o'clock made a full backup, every hour to do a transaction log backup, the last transaction log backup is today noon 12 o'clock, now is the noon today 12:10, found that the database data was lost or destroyed, The last transaction log backup is 12 o'clock noon today, if I restore the database to 12 points at this time, then the operational data that was not compromised after 12 o ' 12:10 will be lost (for example, the database has three tables, one table data is corrupted, and the other two table data is changed by other users). At this point to use the "tail log backup", the principle of tail log backup from the last transaction log backup from the point of time, all subsequent operations are backed up, restore can be found after 12 points of the correct operation of the data.

Note: When a tail log backup is made, the database forces the database to stop, and if the database is not stopped and the user continues to operate, the tail log backup will lose its meaning. SQL Server 2012 If you have made changes to the data after you last backed up the transaction log, the transaction log (that is, the LSN (log sequence number) of the current log file record is greater than the maximum lsn,sql recorded in the last transaction log backup The server distinguishes log records through LSN and does not have a tail-log backup, which prompts and requires you to do a tail backup first.

Instance section:

First a full backup of "Mytest.bak" and then a two transaction log backup based on this backup file, the last transaction log backup time of "2012-8-4 23:07"

Figure 1-1: Backup file named "Mytest.bak", last transaction log backup time "2012-8-4 23:07"

Figure 1-2: After selecting the backup file "Mytest.bak", you can see the backup files in the "Backup set to Restore" list, which lists the full backup files and two transaction log backups, respectively.

The last transaction log backup time for the above backup file is "2012-8-4 23:07", so you can restore to any point in time since the start of the first full backup to the time of the last transaction log backup, which will fully validate the transaction log backup method mentioned above ( If you have a late tail log backup, the last row in the backup set to restore list lists the tail log records at restore time.

If the transaction log backups are two times, the transaction log 1, the transaction log 2, the transaction log 1 check box is removed at restore time, and transaction log 2 is automatically removed, but only "transaction log 2" is allowed, which is fully validated as described above: "When restoring data, In addition to restoring the full backup first, restore each transaction log backup in turn instead of restoring only the last transaction log backup, no more than the differential backup can be based on a full backup, and only the last differential backup is restored.

Figure 1-3: I "2012-08-04 23:36:53" insert a data, "2012-08-04 23:37:44" again do a transaction log backup 3, and then restore the Point-in-time to "2012-08-04 23:36:00", after the restoration, The data after this point in time is gone.

"Note: When you restore, you are prompted that the database is in use, and you typically disconnect all connections before you can restore." I typically use a script to take the data offline and back online, and all connections will be disconnected.

ALTER DATABASE MyTest set offline with ROLLBACK immediate--offline

ALTER DATABASE MyTest set online with ROLLBACK immediate--on-line

If you are doing a full backup last time, it is not allowed to restore to a point in time based on a full backup.

Figure 2-1: Backup file named "Mytesta.bak", Backup Time "2012-8-4 22:33"

Figure 2-2: First select the last full backup file Mytesta, at this point you can see the backup set to restore list showing the backed-up files, and then click the Timeline button to go to the backup timeline interface and change the last full backup time from "22:33:41" to any point in time before it. If you change to "22:32:41" and then determine, you will see the effect of Figure 2-3.

Figure 2-3: Restoring to a point in time based on a full backup is not allowed because no differential or transaction log backups have been made since the full backup. You can see that the backup set to restore list appears empty and cannot be restored.