SQL Server Backup and restore detail strategy

First, the Knowledge point

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

Differential backup: A differential backup is for full backups: All files that have changed since the last full backup. (During a differential backup, only those selected files and folders that are marked are backed up.) It does not clear the tag, that is, after the backup is not marked as a backed-up file, in other words, the archive attribute is not cleared.

Incremental backup: An incremental backup is for the last backup (regardless of which backup): After the previous backup, all the changed files. (During an incremental backup, only marked selected files and folders are backed up, which clears the tag, that is, when you back up the tag file, in other words, clear the archive attribute.) )

Transaction log backup: A full database backup and last differential backup (if any) that was performed before a specific transaction log backup. Any transaction log backups performed after a full database backup or a differential backup that was performed before a specific transaction log backup (if you restored a differential backup). If you set the recovery mode to "simple", you will not be able to use the "transaction log" backup. SQL Server 2000 and SQL Server 2005: Create transaction log backups, you must use full recovery or bulk-logged recovery models.

Partial backup: A backup created by specifying read_write_filegroups is called a partial backup. In the simple recovery model, only file group backups are allowed for read-only filegroups. Restored data backup type: Database backup, partial backup, or file backup. For a database or partial backup, the log backup sequence must start at the end of the database backup or partial backup. For a set of file backups, the sequence of log backups must be continued from the beginning of the entire set of file backups.

File backup: "File Backup" contains all the data in one or more files (or filegroups).

Log chain: A sequential sequence of log backups is called a "log chain." The log chain starts with a full backup of the database. Typically, a new log chain is started only when the database is backed up for the first time, or after the recovery mode is switched from the simple recovery model to the full or bulk-logged recovery model. Unless you choose to overwrite an existing backup set when you create a full database backup, the existing log chain will remain unchanged. When the log chain remains unchanged, the database can be restored from any full database backup in the media set, and then all subsequent log backups prior to the corresponding recovery point are restored. The recovery point can be the end of the last log backup, or it can be a specific recovery point in any log backup.

Example of a backup scenario: A site performs a full database backup at the night of Sunday. A transaction log backup set is made every 4 hours during the day and the first day of backup is rewritten with the day's backup. Differential backups are made every night. If a data disk for a database fails at Thursday 9:12, the site can:

1) Back up the current transaction log, (failure has occurred, how to back up the current transaction log?)

2 Restore database backup starting from Sunday night;

3 Restore the differential backup starting from Wednesday night, roll the database forward to this moment;

4 Restore the transaction log backups from 4 to 8 in the morning to roll the database forward to 8 in the morning;

5 The log backup after the failed restore. This will cause the database to roll forward to the moment the failure occurred.

Second, restore steps

Create a database called Testbackup, create a table called Table1, this time a full backup, backup file is: Testbackupdb-full.bak, then create a table Table2 after a differential backup, The backup file is: Testbackupdb-diff.bak, then a transaction log backup is made after the table Table3 is created (if the database has the recovery model set to simple, then the transaction log is not visible in the Backup type option), The backup file is: Testbackupdb-log.bak;

Create a database called TestBackup2 to test the restore of the backup files for the Testbackup database.

(Figure 1: Creating a library structure)

(Figure 2: Backup type)

Here 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 Figure 4, Figure 5, when the restore succeeds, the list of data is shown in Figure 6, because the Restore status option: Do nothing with the database and do not roll back uncommitted transactions. You can restore additional transaction logs. (RESTORE with NORECOVERY)

(Figure 3: Entering SSMs restore)

(Figure 4: Restore General)

(Figure 5: Restore Options)

(Figure 6: Full backup restore)

Step 2: Restore the differential backup file Testbackupdb-diff.bak, as shown in step 1, this time the database is the same as the state of Figure 6.

Step 3: Restore the transaction log backup file Testbackupdb-log.bak, as shown in Figure 7, to enter the transaction log's restore operation interface; see Figure 8 for a restore (the restore state is default to restore with RECOVERY, in the option of the specified transaction). So there is no mention of this option here). After the restored TESTBACKUP2 database, the restored database TestBackup2 as shown in Figure 9.

(Figure 7: Entering the transaction log)

(Figure 8: Transaction log)

(Figure 9: restored database)

Third, upgrade

Usually the article is written here should be finished, but fortunately, to introduce you to the table after the partition of the restore operation, from the above operation to include only MDF and LDF files, but if more than a few ndf files, these restore the same? So I call this part of the content for the upgrade.

Scenario One: If there is a corresponding partition file, as long as the restoration of the "restore to" file name can be restored.

Scenario Two: If you just created a new partitioned filegroup and file, this time, then restore the backup will appear in Figure 10 error (Do not know is not in the SQL Server 2005 problem), to solve this problem there are two methods, the first: Restart the database service and restore; second: Set restricted access to the database Set to "single";

(Figure 10: Error)