Over time, our databases get bigger and bigger. One possible solution to reduce this problem is the use of backup compression. Mind you – wouldn’t it be better to start by reducing the volume of information to back up? In databases, most of the information to be backed up is in these three types of object:

* HEAPs. A heap contains all the data pages for a table for which no cluster index has been defined.

* Clustered indexes. These contain in their leaves all the data pages associated with the table for which they are created. One can say that the table is now represented as a clustered index.

* Non-clustered indexes. These contain duplicated information about your tables in a sorted tree structure. The main objective is to facilitate searches, although these indexes may also implement restrictions (e.g. a unique restriction).

In this post I’ll explain a little-used strategy which consists of only backing up the two first types of object, leaving the last type out of the backups. Depending on the degree of indexation, this strategy may result in savings of up to 50% of the space required for the backups. You’ll use the AdventureWorksDW database as an example, and start by analysing the space used by the tables and indexes:

image_thumb_1_2AB175C9

In total there’s 43688 KB of data and 24720 KB of indexes. If you were to perform a full backup without compression, this would generate a file of approximately 71 MB.

Next, you’ll create an exclusive filegroup for the indexes and add a file to it

ALTER DATABASE AdventureWorksDW

ADD FILEGROUP FG_Secondary;

 

ALTER DATABASE AdventureWorksDW

ADD FILE (NAME = N’Secondary’, FILENAME = N’C:Secondary.ndf’)

TO FILEGROUP FG_Secondary;

 

Next, you’ll move all the non-clustered indexes to that secondary filegroup by using a CREATE INDEX command for each index, using the DROP_EXISTING option and the ON clause to specify the secondary filegroup. For example:

CREATE NONCLUSTERED INDEX [IX_ProspectiveBuyer_ProspectAlternateKey] ON [dbo].[ProspectiveBuyer] ([ProspectAlternateKey] ASC)

WITH DROP_EXISTING ON [FG_Secondary]

 

When the operation is complete, you can check that the secondary file is about 17MB. Remember that it has been decided not to move the unique restrictions to this filegroup, nor those primary keys which have been implemented as non-clustered indexes.

Once all the non-clustered indexes are in the secondary filegroup, you’ll perform a full backup, but only of the primary filegroup:

BACKUP DATABASE [AdventureWorksDW]

FILEGROUP = ‘PRIMARY’

TO DISK = ‘C:backup_primary.bak’

The size of this backup will be approximately 54 MB, i.e., 24% smaller than the full backup with all the indexes.

If you want to restore this backup after a disaster, you’ll indicate that you only want to restore the primary filegroup:

RESTORE DATABASE [AdventureWorksDW]

FILEGROUP = ‘PRIMARY’

FROM DISK = ‘c:backup_primary.bak’

WITH RECOVERY

Once it has been restored, the information will be accessible and you can consult it:

SELECT TOP 10 * FROM [AdventureWorksDW].[dbo].[FactSalesQuota]

image_thumb_2_2AB175C9

But you will have problems if what you want is to modify, insert or delete data:

update [AdventureWorksDW].[dbo].[FactSalesQuota]

set salesamountquota=salesamountquota+1

where EmployeeKey=272

Msg 8607, Level 16, State 1, Line 1

The table ‘FactSalesQuota’ cannot be modified because one or more non-clustered indexes reside in a filegroup which is not online.

 

If you try to delete the index or even regenerate it with the DROP_EXISTING option, this will result in errors:

DROP INDEX [IX_FactSalesQuota_EmployeeKey] ON [dbo].[FactSalesQuota]

Msg 3741, Level 16, State 1, Line 1

Cannot drop the index ‘dbo.FactSalesQuota.IX_FactSalesQuota_EmployeeKey’ because at least part of the table resides on an offline filegroup.

CREATE NONCLUSTERED INDEX [IX_FactSalesQuota_EmployeeKey] ON [dbo].[FactSalesQuota]

(

[EmployeeKey] ASC

)WITH DROP_EXISTING ON [Primary]

Msg 1931, Level 16, State 3, Line 1

The SQL statement cannot be executed because filegroup ‘FG_Secundario’ is offline. Use the sys.database_files or sys.master_files catalog view to determine the state of the files in this filegroup and then restore the offline file(s) from backup.

 

You must therefore use less traditional methods. Start up the instance in single user mode (parameter –m) and connect using the administrator connection (DAC). Once connected, you’ll obtain the list of the secondary indices to be regenerated:

select s.* from sys.allocation_units u

join sys.partitions p on

container_id = partition_id

join sys.indexes s on

s.index_id=p.index_id and s.object_id=p.object_id

where u.data_space_id=2

image_thumb_3_15BFF356

Once located, delete them:

delete sys.sysindexes

from sys.sysindexes ind

join

(

    select s.* from sys.allocation_units u

    join sys.partitionson

    container_id = partition_id

    join sys.indexes s on

    s.index_id=p.index_id and s.object_id=p.object_id

    where u.data_space_id=2

) Secondaries

on Secondaries.object_id=ind.id

and Secondaries.index_id=ind.indid

If you get a message that it’s not possible to modify catalogue tables, enable these updates:

Exec sp_configure ‘allow updates’,1

go

reconfigure with override

 

Once the indexes have been deleted, shut down the instance and restart it in multiuser mode (without the –m parameter) and now you will have no problems regenerating the indexes. For example, you could generate them in the primary filegroup, although the usual thing would be to create another secondary filegroup, in order to be able to use the same process in the future, if this were necessary:

CREATE NONCLUSTERED INDEX [IX_FactSalesQuota_EmployeeKey] ON [dbo].[FactSalesQuota]

(

[EmployeeKey] ASC

)ON [PRIMARY]

GO

Once all the indexes have been constructed, check that you now have no problem modifying data. In fact, modifications work correctly the moment you have, as the DAC, deleted all the indexes which were not accessible because of the secondary FG:

image_thumb_4_43AD460E

To summarize, it is possible to make a partial “only data” backup of your databases. To do this, use the functionality of partial backups at the filegroup level. Unfortunately, after restoring the principal filegroup, there is no simple way of “disabling” the indexes or of regenerating them. In future product versions, it would be very useful if we can execute a CREATE INDEX with DROP_EXISTING, or we can reconstruct an inaccessible/damaged index directly, in a filegroup which is indeed available, without the need to make low-level modifications.

 

Rubén Garrigós

Rubén Garrigós is an expert in high-availability enterprise solutions based on SQL Server design, tuning, and troubleshooting. Over the past fifteen years, he has worked with Microsoft data access technologies in leading companies around the world. He currently is a Microsoft SQL Server and .NET applications architect with SolidQ. Ruben is certified by Microsoft as a Solution Expert on the Microsoft Data Platform (MSCE: Data Platform) and as a Solution Expert on the Microsoft Private Cloud (MSCE: Private Cloud). As a Microsoft Certified Trainer (MCT), Ruben has taught multiple official Microsoft courses as well as other courses specializing in SQL Server. He has also presented sessions at official events for various Microsoft technologies user groups.

Latest posts by Rubén Garrigós (see all)