SQL Server uses filegroup backup to reduce storage space used by backup files

For DBAs, backing up and refreshing resumes is two of the most important tasks, and if a failure occurs and the backup is not available, then the importance of refreshing the resume appears, wow! Of course, backup is the most important thing for DBAs (not one), and in a conditional scenario, we should keep multiple full and log backups on multiple servers, and even some companies will require complete data to be retained on tape or over large storage to ensure that data can be recovered long before.

So there is a difficult choice: backup space and backup shelf life, the disk is cheap also want money, especially some stingy boss would prefer to spend hundreds of thousands of strokes individual also do not prefer to invest a little more on the hardware, the server should not be used all the resources are happy, in the case of limited backup space, How we properly design a backup strategy and a " backup verification " strategy becomes particularly critical.

Read an article a long time ago describing a DBA to reduce the amount of storage space used by a database in the following ways:

1. Use full and log backups to restore the database to a specific point in time (for example, 0 o'clock in the morning per day)

2. Delete all nonclustered indexes on the user database, and then compress the backup

3. Save the backup as an archive.

From a business perspective, it is not possible to restore a library to a specific point in time and produce it using a new, restored library, even if it needs to be recovered, so for early backups we care only about the data and not on the indexes that are built on the data, even if the query requires it. You can also re-establish the index before you query. That's the starting point for the DBA, where a nonclustered index on many databases can account for 50% or even 70% of the database (I've seen dozens of nonclustered indexes on a table, some of them contain indexes, occupy more than four or five times times the data), and dropping nonclustered indexes can be a great way to reduce the storage space that backups occupy.

=============================================================

Of course, the above nonsense is not the focus of today, the focus of today is filegroup backup.

At weekends, friends Paddy mentions a backup strategy that splits data and indexes into different filegroups (which should be used by many DBAs), and then backs up only the "data" filegroup, minimizing the storage space used to "data backup", while ensuring the need to recover data.

Demo Demo:

First create the database TestDB1001 and create two filegroups to hold the data and index separately

CREATE DATABASE [TestDB1001]Containment=NONE on  PRIMARY(NAME=N'TestDB1001', FILENAME=N'D:\SQLDATA\TestDB1001.mdf'), FILEGROUP[Fg_data](NAME=N'testdb1001_data1', FILENAME=N'D:\SQLDATA\TESTDB1001_DATA1.NDF'), FILEGROUP[Fg_index](NAME=N'testdb1001_index1', FILENAME=N'D:\SQLDATA\TESTDB1001_INDEX1.NDF'  ) LOG  on(NAME=N'Testdb1001_log', FILENAME=N'D:\SQLDATA\TestDB1001_log.ldf')GO

PS: For easy presentation, file growth attributes or other related information is removed, demo code do not

Then create the table and insert the data, noting the different filegroups used by the clustered and nonclustered indexes

 UseTestDB1001GOCREATE TABLETB001 (C1INT IDENTITY(1,1) not NULL, C2INT)GOALTER TABLETB001ADD CONSTRAINTpk_tb001PRIMARY KEY(C1) onFg_dataGOCREATE INDEXIDX_C2 onTB001 (C2) onFg_indexGOINSERT  intoTB001 (C2)SELECT 1  fromSys.objects

Make a filegroup backup of the database, backing up only primary and fg_data two filegroups:

BACKUP DATABASE = N'PRIMARY', FILEGROUP='fg_data' to  DISK= N'D:\SQLDATA\TestDB1001_F1.bak'

Make a first log backup of the database:

BACKUP LOG TestDB1001  to DISK = N'D:\SQLDATA\TestDB1001_L1.bak'

To demonstrate the need, insert the data for the second time:

INSERT  into TB001 (C2) SELECT 2  from sys.objects

Then make the first differential backup

BACKUP DATABASE = N'PRIMARY', FILEGROUP='fg_data' to  DISK= N'D:\SQLDATA\TestDB1001_D1.bak'with differential

To demonstrate the need, insert data for the third time:

INSERT  into TB001 (C2) SELECT 3  from sys.objects

Then make a second log backup:

BACKUP LOG TestDB1001  to DISK = N'D:\SQLDATA\TestDB1001_L2.bak'

After the backup is complete, we verify the viability of the backup restore,

First filegroup restore, note that when restoring, you do not need to make index-related file information when restoring because the FG_INDEX filegroup is not backed up

RESTORE DATABASE [TestDB1002] FILE =N'TestDB1001',  FILE =N'testdb1001_data1' from  DISK =N'D:\SQLDATA\TestDB1001_F1.bak'  with  FILE = 1, MOVE N'TestDB1001'  toN'D:\SQLDATA\TestDB1002.mdf', MOVE N'testdb1001_data1'  toN'D:\SQLDATA\TESTDB1002_DATA1.NDF', MOVE N'Testdb1001_log'  toN'D:\SQLDATA\TestDB1002_log.ldf', Nounload, STATS= Ten, norecovery,partial

Then restore the differential backup:

RESTORE DATABASE [TestDB1002]  from DISK = ' D:\SQLDATA\TestDB1001_D1.bak '  with NORECOVERY

Finally restore the log backup:

RESTORE DATABASE [TestDB1002]  from DISK = ' D:\SQLDATA\TestDB1001_L2.bak '  with RECOVERY

Verify that the data is healthy:

SELECT C2,COUNT(1 from TB001GROUP by C2

The validation of the data proves that the method is feasible.

========================================================

In a filegroup restore, where the partial option is critical, it directly affects whether the subsequent log backup is available, and if the partial option is not specified:

Using the WITH RECOVERY option to restore the differential backup without error, the database is still in "restoring" mode and the Restore information is:

72 pages have been processed for database ' TestDB1002 ', file ' TestDB1001 ' (located on file 1). 16 pages have been processed for database ' TestDB1002 ', file ' Testdb1001_data1 ' (located on file 1). 3 pages have been processed for database ' TestDB1002 ', file ' Testdb1001_log ' (located on file 1). Only part of the file "Testdb1001_index1" is restored through a database or file restore operation. You must successfully restore the entire file before you can apply this backup set. This RESTORE statement successfully performed some operations, but the database could not be brought online because one or more RESTORE steps were required. The previous message explained why the recovery could not be resumed at this time. RESTORE DATABASE ... file=<name> successfully processed 91 pages and took 0.059 seconds (11.983 MB/s).

Use the WITH RECOVERY option to restore a log backup with a direct error message:

Msg 4320, Level 16, State 13, line 1th through a database or file restore operation, only part of the file "Testdb1001_index1" is restored. You must successfully restore the entire file before you can apply this backup set. Msg 3119, Level 16, State 1, line 1th found a problem while planning the RESTORE statement. The previous message provides more information. Msg 3013, Level 16, State 1, line 1th, restore DATABASE is terminating abnormally.

Therefore, when restoring a filegroup backup, be sure to use the partial option.

========================================================

After finishing the call, thank Paddy again for the lift point.

Recently seldom write blog, also did not collect sister's motivation, sister quality down, you will next ...

SQL Server uses filegroup backup to reduce storage space used by backup files