SQL Server backup and Restoration

SQL Server backup and Restoration

I. knowledge points

Full backup: backs up all selected folders and does not rely on the file archive attribute to determine which files to back up. (During the backup process, any existing tags are cleared, and each file is marked as backed up. In other words, the archive attribute is cleared ). Full backup is also called full backup.

Differential backup: Differential backup is for full backup: All files that change after the last full backup are backed up. (During differential backup, only the marked files and folders are backed up. It does not clear the mark, that is, it is not marked as a backup file after the backup, in other words, it does not clear the archive attribute ).

Incremental Backup: the Incremental backup is for the last backup (no matter what type of backup): All files that have changed since the last backup. (During the Incremental backup process, only the marked files and folders are backed up. The Marked files are cleared, that is, the files are marked after the backup. In other words, the archive attributes are cleared .)

Transaction Log backup: full database backup and last differential backup performed before a specific transaction log backup (if any ). All transaction log backups performed after the full database backup or differential backups performed before the specific transaction log backup (if you have restored the differential backup ). If you set the recovery mode to simple, you will not be able to use transaction log backup. SQL Server 2000 and SQL Server 2005: Create a transaction log backup. You must use a full recovery or large-capacity log recovery model.

Partial backup: the backup created by specifying READ_WRITE_FILEGROUPS is called "partial backup ". In simple recovery mode, only file group backup can be performed on read-only file groups. Type of restored data backup: database backup, partial backup, or file backup. For database backup or partial backup, the log backup sequence must start from the end of the database backup or partial backup. For a group of file backups, the log backup sequence must start from the beginning of the entire group of file backups.

File backup: "file backup" contains all data in one or more files (or file groups.

Log chain: continuous log backup sequence is called "log chain ". The log chain starts from the full backup of the database. Generally, a new log chain starts only when the database is backed up for the first time, or when the recovery mode is switched from the simple recovery mode to the full recovery mode or the large-capacity log recovery mode. The existing log chain remains unchanged unless you select to overwrite the existing backup set when creating a full database backup. When the log chain remains unchanged, you can back up and restore the database from any complete database in the media set, and then restore all subsequent log backups before the corresponding recovery point. The recovery point can be the end of the last log backup or a specific recovery point in any log backup.

Example of a backup solution: a site backs up a complete database on Sunday evening. Create a transaction log backup set every four hours during the day and rewrite the backup of the previous day with the backup of the current day. Differential backup is performed every night. If a data disk in the database fails at AM on Thursday, the site can:

1) back up the current transaction log. (If a fault has occurred, how can I back up the current transaction log ?)

2) restore the database backup from Sunday night;

3) restore the differential backup from Wednesday night and roll the database to this point;

4) restore the transaction log backup from AM to roll the database forward to am;

5) restore the log backup after the fault. This will cause the database to roll forward to the moment when a fault occurs.

Ii. restoration steps

Create a database named TestBackup, create a table named Table1, this time for a complete backup, backup file: TestBackupDB-full.bak; then create table Table2 for differential backup, backup file is: testBackupDB-diff.bak; then create table Table3 and back up transaction logs (if the database sets recovery mode to simple, then the transaction log will not be visible in the Backup Type option), the backup file is: testBackupDB-log.bak;

Create a database named TestBackup2 to test the restoration of the backup file of the TestBackup database.

(Figure 1: Create a database structure)

(Figure 2: Backup Type)

Below we can restore three backup files: TestBackupDB-full.bak, TestBackupDB-diff.bak, TestBackupDB-log.bak:

Step 1: restore the full backup file TestBackupDB-full.bak, as shown in option 4, Figure 5, the data list is shown in 6 after the restore is successful, because the recovery status option: no action is performed on the database, do not roll back uncommitted transactions. Other Transaction logs can be restored. (Restore with norecovery)

(Figure 3: Go to SSMS to restore)

(Figure 4: conventional restoration)

(Figure 5: Restore options)

(Figure 6: Full backup restoration)

Step 2: Restore the differential backup file TestBackupDB-diff.bak, as shown in step 1, when the database is still in the same state as Figure 6.

Step 3: Restore the transaction log backup file TestBackupDB-log.bak, 7 enter the transaction log restore operation interface; as shown in figure 8, the options include the specified transaction time for restoration (the restoration status during the restoration process is restore with recovery by default, so this option is not mentioned here ). The restored database TestBackup2 is shown in the restored database TestBackup29.

(Figure 7: transaction log entry)

(Figure 8: transaction log)

(Figure 9: restored database)

Iii. Upgrade

Generally, this article should be written here, but I am very lucky to introduce how to restore the table after partitioning. From the above operations, only the mdf and ldf files are included, but if there are a few more ndf files, are these restores the same? So I call this part of content an upgrade.

Scenario 1: if there is a corresponding partition file, you only need to change the file name [restored to] during restoration.

Scenario 2: If you have just created a partition file group and a file, then restoring the backup will result in Figure 10 (I don't know if it is a problem with SQL Server 2005 ); there are two ways to solve this problem: restart the Database Service and restore the database; and set [restrict access] to [Single] for the database ];

(Figure 10: Error)