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:

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





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



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)



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:



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:



FROM DISK = ‘c:backup_primary.bak’


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

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

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


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

Once located, delete them:

delete sys.sysindexes

from sys.sysindexes ind



    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


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



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:

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