SQL Server Enterprise Platform Management Practice book notes--about how SQL Server databases are restored

Original: SQL Server Enterprise Platform Management Practice reading notes--about how SQL Server databases are restored

This article is the backup method of the previous article, this article is about the restore scenario, in SQL Server more than 2005 existing restore scenarios are generally divided into the following 4 levels of data restoration:

1, the database full restore level:

Restore and restore the entire database. Database is offline during restore and restore operations

2. Data file level

Restore and restore a data file or a set of files. During a file restore, the filegroups that contain the files automatically become offline during the restore process. Any attempt to access an offline filegroup will result in an error. But other filegroups can remain online.

3. Data page level

Under the full or bulk-logged recovery model, you can specify to restore a particular data page or pages to a database, without having to recreate the entire data or the entire file. For large databases that only have a small portion of the page damaged, this restore can greatly save the restore time.

4. Paragraph level

In large databases, a number of files or filegroups are often included. The use of piecemeal restore, you can make the database after the restoration of some files or filegroups, this part of the data can be accessed, so as to reduce the database offline time.

Here's one thing to note:SQL Server does not allow users to back up or restore individual tables

Restore Scheme	under the simple recovery model	under the full/bulk-logged Recovery model
Database Full restore	This is the most basic The Restore policy. A full restore of the Database may involve simple restores and restores of the complete database. Additionally, a full database restore may involve restoring a full database backup, , and restoring and restoring differential backups	This is a basic restore strategy. A full database restore of involves restoring a full or differential backup (if any), , and restoring all subsequent log backups, in order. Complete database restore by restoring and restoring the last Log backup (restore with RECOVERY)
file restore	Restore only corrupted read-only files, but not The entire database, so is not very practical	Can restore one or more files without restoring the entire database. You can perform a file restore when the database is in an offline State or the database remains online (for SQL Server 2005). During a file restore, the filegroup that contains the file being restored is always offline. Other filegroups may be accessed
page restore	do not use	To restore a damaged page, either offline or the database remains online, on the page During the restore process, the page you are restoring is always offline. Must have a complete chain of log backups (containing the current log file) and must reply to all of these Log backups to keep the page consistent with the current log file
piecemeal restore	by filegroup Level and from the primary filegroup and all read-write secondary filegroups, Restore and restore the database in phases	restore and restore databases in stages by filegroup level and starting from primary filegroup

No matter how the data is restored, the SQL Server database engine guarantees the logical consistency of the entire database before recovering the database. For example, after you restore a file, you must restore a full set of log file backups so that the transaction in the file is sufficiently long to roll forward, consistent with the database, to recover the file and bring it online.

1. Complete database restore

Restoring a database completely from scratch is the most commonly used restore operation. In a simple case, a restore operation requires only a full database backup, a differential database backup, and subsequent log backups. It is easy to construct a correct restore sequence. For example, to restore an entire database to the point of failure. You need to back up the active transaction log (the "tail" of the log) first. Then, restore the latest full database backup, the latest differential backup (if any), and all subsequent days back up in the order in which the backups were created. If the source database is in simple mode, there is no response to the log backup. Recovery work is limited to restoring a full database backup, as well as the last differential backup.

In this mode, one of the challenges users often encounter is how to recover a database to a specific recovery point after a disaster. For example, a key data table was mistakenly deleted at 12:01 noon. How do I restore it to the 12 o'clock State?

SQL Server is well-supported for this type of requirement, but it is implemented by recovering log files to a specified recovery point. So, it has several preliminary requirements. And before a disaster occurs, the database must meet the conditions:

1, the recovery model of the database must be the full recovery model

2, before the disaster, the database has done a full database backup (or a set of full file backup)

3. After the last full database backup, if any log backups have been made, these log backups can now be found for each.

Databases that meet the above requirements, you can use the Backup recovery method to restore the database to any point in time after the full backup.

The recovery steps are as follows:

1. Back up the active transaction log (also known as the tail of the log). This action creates a tail-log backup. If the active transaction log becomes unavailable after a disaster, all transactions in that log section are lost

2, restore the latest full database backup, and do not do transaction recovery

Execute with the following statement: RESTORE DATABASE database_name from Back_dervice with NORECOVERY

3. If there is a differential backup, restore the most recent differential backup without transaction recovery

RESTORE DATABASE Databse_name with NORECOVERY

4. Start with the first transaction log backup created after restoring the backup, and use NORECOVERY to restore the log in turn.

RESTORE LOG database_name from Back_logdervice

5, restore the database to a point in time, this step can also be used in conjunction with the restoration of the last log backup

RESTORE databse databse_name with stopat= ', RECOVERY

Here we create a new test library and then simulate the entire process of performing a backup and restore, the script is as follows

--Do a database full backup back to DISK = N (TestDB)‘F:\SQLTEST\TestDB.bak‘With noformat,noinit, NAME = N‘testdb-Full Database backup', SKIP, Norewind, nounload, STATS =10go--Second emulated incremental backup DATABASE [TestDB] to DISK = N‘F:\SQLTEST\TestDBUpdate.trn‘With differential, Noformat, noinit, NAME = N‘testdb-Differential Database backup', SKIP, Norewind, nounload, STATS =10go--Third transaction log backups backup log [TestDB] to DISK = N‘F:\SQLTEST\TestDB29141107.trn‘With Noformat, noinit, NAME = N‘testdb-Transaction Log Backups', SKIP, Norewind, nounload, STATS =10go--Fourth transaction log backups backup log [TestDB] to DISK = N‘f:\sqltest\testdb29141108.trn with Noformat, noinit, NAME = N "testdb-transaction log backup  ", SKIP, Norewind, nounload, STATS = 10< Span style= "color: #000000;" >go-- Last transaction end log (tail log), and the database is in "restore" state backup log [TestDB] to DISK = N ' 

Then we follow the sequence described above, the restore process, the code is as follows

--Restore the first full-amount backup from a backup restore DATABASE [TestDB] from DISK = N‘F:\SQLTEST\TestDB.bak‘With FILE =1, NORECOVERY, nounload, STATS =10go--Restore a second incremental backup restore DATABASE [TestDB] from DISK = N‘F:\SQLTEST\TestDBUpdate.trn‘With FILE =1, NORECOVERY, nounload, STATS =10go--Restore a third transaction log backup restore log [TestDB] from DISK = N‘F:\SQLTEST\TestDB29141107.trn‘With FILE =1, NORECOVERY, nounload, STATS =10go-- Restore a fourth transaction log backup restore log [TestDB] from DISK = N ' f:\sqltest \testdb29141108.trn '  with FILE = 1, NORECOVERY, nounload, STATS = 10go-- resumed the fifth tail transaction log backup, and adopted recovery on-line the library restore log [TestDB] from DISK = N "f:\sqltest\testdblastlog.trn '  with FILE = 1, RECOVERY, nounload, STATS = 10go   

If you back up the log at the end of the transaction log, if you are prompted to log in now, you can use the following script to set the database to single user, then revert to multi-user mode

-- set into single user mode ALTER DATABASE TESTDB set single_usergo--to Multi-user mode ALTER DATABASE TESTDB set Multi_usergo 

This type of backup restore is a simple and easy-to-use approach and is the most widely used restore scenario for your application. If the database is a large database, this solution has basically been able to meet the requirements.

But this program also has its own shortcomings, the biggest drawback is to do a full backup of the database recovery. This step is a costly step in both space and time.

1. In time, SQL Server takes a long time to rebuild the entire database. And in this process, the database is inaccessible. The length of time, basically determined by the speed of the hard disk. An up-TB database, it may take nearly a day to make a full recovery. This waiting time is unacceptable to many systems.

2, in space, the size of a full backup and the database has been used basically consistent size. If the backup is to be placed on the hard disk, it basically needs to provide twice times more space. A copy of the database, a copy of the backup.

In fact, a lot of time, the space problem is not big, because now the storage has been raised to a large, but the time of waiting is often not acceptable to people. At this time, the database administrator can take a look at the severity of the disaster, as well as the backup files in hand, as well as the structure of the data, to see how to shorten the recovery. The following scenarios require the database to pre-select the full recovery model, the operation is more complex, but good use can greatly shorten the database offline time.

2. File restore

A database will have several files and filegroups. If the damage is only concentrated on one of the files or filegroups, and most of the other data files are not corrupted, it is a waste of time to rebuild all the files using the traditional database restore scheme. If SQL Server just rebuilds the broken data file or filegroup, it can save a lot of time.

However, transactional modifications to the database are distributed across data files. If you use a backup to restore only one of the files, and the other files do not recover, then their status must be inconsistent. There must be many changes that are not included in the recovered files. Such a database is not available. In order for the newly recovered files to automatically recover the changes made after the backup, SQL Server needs to use the transaction log. First, you must do a log backup of the current database (the so-called tail-log backup) before you recover. Restoring all log backups, SQL Server can take advantage of rolling forward methods to restore data files to a consistent point in time.

The procedure is as follows:

1. Create a tail-log backup of the active transaction log

This step is a critical step in file restore. For offline file restores, you must use a tail-log backup before file restore. For online file restores, a log backup must always be performed after a file restore. This log backup is critical to restoring a file to a state that is consistent with the rest of the database. If the operation cannot be performed because the log is corrupted, the file restore cannot proceed and the entire database must be restored.

This is also why file backup is not a complete guarantee of data integrity

2. Restore the appropriate files from the latest file backup of each corrupted file.

3. For each restored file, restore the most recent differential file backup, if any.

4. Restore the transaction log backups sequentially, starting with a backup that overwrites the oldest restored files, ending with the tail-log backup created in step 1.

Although the log backups recovered here are for the entire database, the recovery of the transaction log backups is faster because only the changes made to the restored files are processed here. This will save you a lot of time compared to restoring an entire database.

Here we have a detailed example of this backup restore process, I first set up a database of several filegroups, as follows:

Let's start by backing up these filegroups, which are usually done in a scheduled task, and we'll start by simulating the process:

--Back up all data files in turn backup DATABASE [Sales] FILEGROUP = N‘PRIMARY' To DISK = N‘F:\SQLTest\SalesOneFile.bak‘With Noformat, noinit, NAME = N‘sales-full file Group backup', SKIP, Norewind, nounload, STATS =10Gobackup DATABASE [Sales] FILEGROUP = N‘FG1' To DISK = N‘F:\SQLTest\SalesFileTwo.bak‘With Noformat, noinit, NAME = N‘sales-full file Group backup', SKIP, Norewind, nounload, STATS =10Gobackup DATABASE [Sales] FILEGROUP = N‘FG2' To DISK = N‘F:\SQLTest\SalesFileThree.bak‘With Noformat, noinit, NAME = N‘sales-full file Group backup', SKIP, Norewind, nounload, STATS =10Gobackup DATABASE [Sales] FILEGROUP = N‘FG3' To DISK = N‘F:\SQLTest\SalesFileFour.bak‘With Noformat, noinit, NAME = N‘sales-full file Group backup', SKIP, Norewind, nounload, STATS =10Gobackup DATABASE [Sales] FILEGROUP = N‘FG4' To DISK = N‘F:\SQLTest\SalesFileFive.bak‘With Noformat, noinit, NAME = N‘sales-full file Group backup', SKIP, Norewind, nounload, STATS =10go--Add an incremental backup file first backup DATABASE [Sales] FILEGROUP = N‘FG4 f:\sqltest \salesfilefiveupdate.bak '  with Noformat, noinit, NAME = N ' sales-full filegroup backup  " SKIP, Norewind, nounload, STATS = 10go-- Back up a transaction log file backup log [Sales] to DISK = N ' f:\sqltest\ Saleslog.trn" sales-transaction log backup  ", SKIP, Norewind, nounload, STATS = 10< Span style= "color: #000000;" >go                 

Assuming this time, the FG4 file group is broken, we want to use the file recovery mode, data recovery, we take the way of online recovery

--Restore filegroups Online FG4 is the fourth file. Restore DATABASE [Sales] File = N‘File4' From DISK = N‘F:\SQLTest\SalesFileFive.bak‘With FILE =1, NORECOVERY, nounload, STATS =10go-- After the above steps the file has been restored, but the filegroup FG4 norecovery status-- We need to make a tail backup of the log at this time to ensure that the transaction is performed correctly when the file is offline-- we use copy_onlybackup log [Sales] to DISK = n< Span style= "color: #800000;" > ' f:\ Sqltest\saleslog.trn '  with Norecoverygorestore LOG [ Sales] from DISK = N ' f:\sqltest\saleslastlog.trn ' 

The way to restore this file is completed, if you say a read-only file, only need a restore to restore the backup file group can, do not need any logs, it is important to note that in the SQL Server2005 version of the system "maintenance plan" does not support the way file backup. There is no finer granularity of recovery, let's look at the next recovery method.

3. Page Restore

There is also a damage, such as a classic 824 page read error, which is not as severe as before. Every file in the database can be opened, but some of the pages are broken.

For this scenario, the DBCC CHECKDB can be used for data repair. If the DBCC command cannot fix the data without losing data, or if the database is not repaired even if the database is allowed to be dropped, the administrator can only perform a full recovery of the database. But in this case, recovering the entire database for a few pages is costly. After SQL Server2005, a page restore feature was introduced that allows you to restore only a specified number of pages, which can significantly save database recovery time. Can be said to be a first-aid functional items.

Page restore is used to repair quarantined corrupted pages. Restoring and recovering a page less quickly may be faster than restoring a file, thereby reducing the amount of data that is offline in the restore operation. However, if you want to restore more than just a few pages in a file, it is more efficient to restore the entire file. For example, if a large number of pages on a file indicate that the file has unresolved failures, consider restoring the file directly.

Typically, the page you are restoring is flagged as "suspect" because of an error encountered while accessing the page. Suspicious pages are identified in the suspect_pages table in the msdb database. You can restore multiple database pages immediately. The process is the same as the file restore process above. After the page is restored, all log file backups are also resumed. Each time you pass the log redo, the roll forward sets go one step further.

Of course there is still a limit, page restore can only restore the data page. Page cannot be restored cannot be used to restore the following content:

• Transaction log
• Allocation pages: Global allocation Map (GAM) pages, shared global allocation Map (SGAM) pages, and page free space (PFS) pages. These system pages are corrupted and page restore cannot be restored
• Page 0 of all data files (file Start page)
• Page 1:9 (data start Page)
• Full-Text Catalogs (fulltext search catalog)

Page restores also meet the following requirements:

• The database must use the full recovery model. Can not succeed if you use the bulk-logged recovery model. This feature is not available in the simple recovery model.
• Pages in a read-only filegroup cannot be restored
• The restore sequence must begin with a full backup, a file backup, or a file group backup to restore the page. So if there is not a copy of the data page damaged before the backup, also cannot be restored
• Page restore requires a continuous log backup to the current log file, and all log backups must be resumed before the page can be restored to its current normal state. So if the database has ever done a truncation log action, or a log backup is not found now, it is not possible to page recovery.
• Database backups and page restores cannot be performed at the same time.

The steps for page restore are as follows:

1. Gets the page ID of the damaged page to restore. A checksum or a torn write error will return the page ID and provide the information needed to specify the page. You can query the suspect_pages table in the msdb database. or monitor the event and the error message reported in the SQL Server errorlog file to find the page ID with the bad page.

From msdb: Suspect_pages

2. Start a page restore from a full database backup, file backup, or filegroup backup that contains the page. In the Restore DATABASE statement, use a page clause to list the page IDs of all the pages that you want to restore.

Page= ' Filepage '

3. Apply the most recent differential backup.

4, apply the subsequent log backup.

5. Create a new database tail-log backup.

6, restore the new tail day backup. After you apply this new log backup, the page restore is complete and you can start to access the page normally.

For example: We have a library, file B's file ID is 1, the ID of the corrupted page is 57, 202, and before this inventory in B file backup, and there are two log backups, we perform the following script sequence restore

RESTORE DATABASE <database> page='1:57,1:916' from<file_backup_of_file_B>with Norecoverygorestore log  <database> from     <log_backup> withNorecoverygorestore Log < Database> from <log_backup>with norecoverygo-- This step is critical, be sure to back up the database tail log backup logs <database> to < new_log_backup>go-- restore tail log of last backup restore log <database> from <new_log_backup> with Recoverygo          

This approach is the most straightforward and fastest solution for the 824 issue, but is not provided as an interface until the SQL SERVER 2012 version and can only be executed using SQL statements.

Database backup mode selection, can refer to this article: http://www.cnblogs.com/zhijianliutang/p/4063697.html

SQL Server Enterprise Platform Management Practice book notes--about how SQL Server databases are restored