Sql-server – How to exclude indexes from backups in SQL Server 2008

backupindexessql serversql-server-2008

Our nightly full (and periodic differential) backups are becoming quite large, due mostly to the amount of indexes on our tables; roughly half the backup size is comprised of indexes.

We're using the Simple recovery model for our backups.

Is there any way, through using FileGroups or some other file-partitioning method, to exclude indexes from the backups?

It would be nice if this could be extended to full-text catalogs, as well.

Best Answer

If you switch over to full recovery mode, you can do this with filegroups, but it's really, really clumsy. You leave the data in the primary filegroup, and put the indexes in a separate (non-default, that's the key) filegroup.

Then you stagger your backups so that you're doing filegroup backups of the primary every night, and transaction log backups every X minutes.

When disaster strikes, you restore the primary filegroup by itself. The data is suddenly online, but the indexes are not. However, to get back to normalcy, you'll need to export that data into a new clean database and add indexes from there. You can't bring the database completely online without restoring all of the filegroups, and you can't say "I don't need that other filegroup anymore anyway."

For more about how this works, check out my video tutorial on filegroup restores.

Related Solutions

Sql-server – Sql Server 2008 Backups not quite running

Look in the main SQL server logs and the history for the maintenance plan itself to find more info. The job history itself likely won't provide you with the best information.

Sql-server – How to create non-clustered indexes on a SQL Server 2008 database? Preferably without code

First non-clustered indexes are not new in SQL Server 2008. They have been around for ever. Filtered indexes are new in SQL Server 2008.

There is no way to have the system figure out what indexes need to be created before any queries are run against the SQL Server.

There are two ways to create indexes in SQL Server. One is with T/SQL, and the other is using SQL Server Management Studio which has a graphical editor. The graphical editor will simply generate T/SQL and run the T/SQL against the database.

The T/SQL to create a non-clustered index on the two decimal columns would look something like it looks below.

CREATE INDEX IX_CustomerDecimal1_CustomerDecimal2 on dbo.CUSTOMER
(CustomerDecimal1, CustomerDecimal2)
INCLUDE (CustomerTextComments)
WITH (FILLFACTOR=70)

Not if you plan on using the two CustomerDecimal fields independently when searching the database table then you'll need a separate index for each.

CREATE INDEX IX_CustomerDecimal1 on dbo.CUSTOMER
(CustomerDecimal1)
INCLUDE (CustomerTextComments)
WITH (FILLFACTOR=70)

CREATE INDEX IX_CustomerDecimal2 on dbo.CUSTOMER
(CustomerDecimal2)
INCLUDE (CustomerTextComments)
WITH (FILLFACTOR=70)

In either case you'll need to add what ever columns you are returning to the list of included columns so that you minimize the IO being generated.

Best Answer

Related Solutions

Sql-server – Sql Server 2008 Backups not quite running

Sql-server – How to create non-clustered indexes on a SQL Server 2008 database? Preferably without code

Related Topic