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.
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]
Five. Backup Demo-- Get a good habit of doing a full backup first Backup Database [filegrouptest] to Backuptestdevice
--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 TypeSix. 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