SQL Server Backup and Recovery series six filegroup backup and restore

Source: Internet
Author: User

I. Overview

A file backup is a backup of all the data in one or more files or filegroups. Using a file backup enables you to restore only corrupted files without restoring the rest of the database, thereby speeding up recovery. For example, if a database consists of several files located on a different disk, in which one of the disks fails, only the files on the failed disk are restored, and the other disk files do not need to be restored, which shortens the restore time.

Under the full recovery model, a complete set of file backups and log backups covering all file backups are combined, equivalent to a full database backup.

1.1 File backup has the following advantages:
(1) The damaged file can be restored quickly.
(2) When a very large database becomes unmanageable under a full backup, file backups increase the flexibility of planning and media processing.

1.2 File backup has insufficient:
(1) compared with a full database backup, the main disadvantage of file backup is that management is more complex. If a damaged file is not backed up, a media failure can cause the entire database to be unrecoverable. Therefore, a full set of file backups must be maintained, and one or more log backups must be maintained.
(2) Maintaining and tracking these full backups is a time-consuming task that exceeds the space required for a full database backup.

Two. File Backup policy

Restoring a database using file and log backups can be complex, so it is a good idea to perform a full database backup before the first file backup begins. After t0 creates the database, perform a full database backup T1 immediately after the first full database backup is created, you can start the transaction log backup. Transaction log backups are performed at scheduled intervals, and file backups are performed at intervals that best fit the database business requirements, with the following backup of primary filegroup A and secondary filegroup B. In the full recovery model, restoring a filegroup backup requires not only restoring the filegroup backup itself, but also sequentially recovering all log backups from the last full database backup to the target point in time of recovery. If you have a large number of log backups, consider giving the differential file backup again, but the backup plan is more difficult to manage.

Three. File restore

When a large database has several files and filegroups, if the corruption is only focused on one of the files or filegroups, SQL Server can save time by rebuilding the broken data filegroup. However, the transaction modification of the database will be distributed on each data file, if only one of the files is restored with backup, and the other files are not restored, then their state will be inconsistent, so that the database is unusable, in order for the newly recovered files to automatically restore the backup after the changes made, you need to With the transaction log . To restore one or more damaged files using a file backup, follow these steps:

(1) Create a tail-log backup of the active transaction log. For offline file restores, you must always make a tail-log backup before the file is restored. For online file restores, a log backup must always be performed after a file restore. Because the log file is corrupted for a day, the file is not yet in principle.

(2) Restore the file from the latest file backup of each corrupted file.

(3) Restore the most recent differential file backup (if any) for each restored file, as this restores faster

(4) Restore the transaction log backups sequentially, starting with the log file of the earliest backup in time, and ending with the tail log of step 1.

Four. Data initialization
--First step: Create a databaseCREATE DATABASE [filegrouptest]Go Use [filegrouptest]--Step Two: Create a filegroupALTER DATABASE [filegrouptest] ADDFILEGROUP[fg_test_id_01]ALTER DATABASE [filegrouptest] ADDFILEGROUP[fg_test_id_02]--Step Three: Create a file to add to a filegroupALTER DATABASE [filegrouptest] ADD FILE(NAME=N'Fg_testunique_id_01_data', FILENAME=N'D:\DATA\FG_TESTUNIQUE_ID_01_DATA.NDF', SIZE=1MB, FileGrowth=1MB) toFILEGROUP[fg_test_id_01]ALTER DATABASE [filegrouptest] ADD FILE(NAME=N'Fg_testunique_id_02_data', FILENAME=N'D:\DATA\FG_TESTUNIQUE_ID_02_DATA.NDF', SIZE=1MB, FileGrowth=1MB) toFILEGROUP[fg_test_id_02]--The fourth step is to create a table that is stored on different filesCREATE TABLEStudent (IDINT, Namevarchar( -),[Address] varchar( -)) on [fg_test_id_01]CREATE TABLETeacher (IDINT, Namevarchar( -),[Address] varchar( -)) on [fg_test_id_02]
CREATE TABLE School (ID int,name varchar (), [Address] varchar (+)) on [PRIMARY]

-- Get a good habit of doing a full backup first Backup Database  [filegrouptest]  to Backuptestdevice
Five. Backup Demo
--Insert data to two tablesInsert  intoStudentValues(1,'Zhang San','Shenzhen, Guangdong')Insert  intoTeacherValues(1,'John Doe','Foshan, Guangdong')--Log backupBackup Log  [filegrouptest]  toBackuptestdevice--Insert data to two tablesInsert  intoStudentValues(2,'Zhang 32','Shenzhen, Guangdong')Insert  intoTeacherValues(2,'Lee 42','Foshan, Guangdong')--Log backupBackup Log  [filegrouptest]  toBackuptestdevice--file group fg_test_id_01 backupBackup Database [filegrouptest] file='Fg_testunique_id_01_data'  toBackuptestdevice--Insert data to two tablesInsert  intoStudentValues(3,'Zhang 33','Shenzhen, Guangdong')Insert  intoTeacherValues(3,'Lee 43','Foshan, Guangdong')--Log backupBackup Log  [filegrouptest]  toBackuptestdevice--Insert data to two tablesInsert  intoStudentValues(4,'Zhang 34','Shenzhen, Guangdong')Insert  intoTeacherValues(4,'Lee 44','Foshan, Guangdong')--Log backupBackup Log  [filegrouptest]  toBackuptestdevice--file group fg_test_id_02 backupBackup Database [filegrouptest] file='Fg_testunique_id_02_data'  toBackuptestdevice
--inserting data to the primary filegroup tableInsert  intoSchoolValues(1,'Shenzhen University','Nanshan, Shenzhen, Guangdong')--PRIMARY Filegroup BackupBackup Database [filegrouptest] file='filegrouptest'  toBackuptestdevice

View the backup set as shown in: TYPE=F represents filegroup Backup Type

Six. Restore Demo
--Step 1: If the file Fg_testunique_id_01_data is corrupted, the database is online to restore the file Restore Database [filegrouptest] file = ' Fg_testunique_id_01_data '  from  with file =  -


-- at this point the Filegrouptest library is still available, but the student performance on the FG_TEST_ID_01 filegroup is not available and is offline Select *  from FileGroupTest.dbo.Student


-- These two tables are on different filegroups and can be used Select *  from FileGroupTest.dbo.School Select *  from FileGroupTest.dbo.Teacher

The BACKUP log and copy_only option creates a copy-only log backup that does not truncate the transaction log. A copy-only log backup has no effect on the log chain, so other log backups behave as if the copy-only backup does not exist.

-- Step 2: Make a new log backup to ensure that points are captured when the file is offline Backup Log  [filegrouptest]  to  with Copy_only


--Step 3: Restore log Backups onlineRestore Log [filegrouptest]  fromBackuptestdevice with file= the, NorecoveryRestore Log [filegrouptest]  fromBackuptestdevice with file= *, NorecoveryRestore Log [filegrouptest]  fromBackuptestdevice with file= -, recovery
-- offline Filegroup fg_test_id_01 online, student table available, database recovery complete Select *  from FileGroupTest.dbo.Student


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: