SQL Server Restore Backup

First, Knowledge points

full backup: backs up all selected folders and does not rely on the archive properties of the files 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). 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 was backed up. (During a differential backup, only those selected files and folders that are marked are backed up.) It does not clear the tag, i.e. it is not marked as a backed up file after backup, in other words, the archive attribute is not cleared.

Incremental Backup: incremental backups are for the last backup, regardless of the backup: All files that have changed since the last backup were backed up. (During an incremental backup, only the marked selected files and folders are backed up, and it clears the tag, which is: Mark the file after backup, in other words, clear the archive attribute.) ）

transaction Log backups: full database backups and last differential backups (if any) that were performed before a particular transaction log backup. Any transaction log backups that are 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 the full recovery or bulk-logged recovery model.

Partial Backup: A backup created by specifying read_write_filegroups is called a "partial backup." Under the simple recovery model, filegroup backups are only allowed on read-only filegroups. Restored data backup type: Database backup, partial backup, or file backup. For database or partial backups, the sequence of log backups must start at the end of a database backup or a 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 model is switched from the simple recovery model to the full or bulk-logged recovery model. The existing log chain remains the same unless you choose to overwrite the existing backup set when you create a full database backup. When the log chain remains intact, you can restore the database from any full database backup 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.

A backup Scenario example: A site performs a full database backup in the evening of Sunday. Make a transaction log backup set every 4 hours during the day, and rewrite the backup of the first day with the day's backup. Differential backups are performed every night. If a data disk for a database fails in Thursday 9:12, the site can:

1) Back up the current transaction log; (already faulted, how do I back up the current transaction log?) ）

2) Restore the 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 backup from 4 to 8 in the morning to roll the database forward to the morning at 8 points;

5) Restore the log backup after the failure. This will roll the database forward to the moment the failure occurs.

Second, Restore Steps

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

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

(Figure 1: Creating the 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 Options 4, Figure 5, the data list is shown in 6 after the restore succeeds because the recovery status option: Do not perform any operations on the database and do not roll back uncommitted transactions. You can restore other transaction logs. (RESTORE with NORECOVERY)

(Figure 3: Enter SSMs restore)

(Figure 4: Restore General)

(Figure 5: Restore option)

(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 still the same as the state of Figure 6.

Step 3: Restore the transaction log backup file testbackupdb-log.bak,7 into the transaction log of the restore operation interface; see the option in Figure 8 for the time of the specified transaction (restore status is the default RECOVERY, so this option is not mentioned here). After the restored TESTBACKUP2 database, the restored database TestBackup29 as shown.

(Figure 7: Entering the transaction log)

(Figure 8: Transaction log)

(Figure 9: The restored database)

Third, Upgrade

Usually the article is written here should be finished, but fortunately, I would like to introduce you how to partition the table after the restore operation, from the above operation only includes the MDF and LDF files, but if a few more 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 when the "revert to" file name can be restored.

Scenario Two: If you have just created a new partition file group and file, this time to restore the backup will appear in Figure 10 error (do not know that the problem is not in SQL Server 2005); To solve this problem there are two methods, the first: Restart the database service and restore; second: Set the database's "Restricted access" Set to "single";

(Figure 10: Error)

SQL Server Restore Backup (RPM)