SQL Server - Index Loss Scenario

Question

Note. Not sure if SO has a specific database site, so I ask this question here.

SQL Server 2008 Enterprise

I am working on a solution to many problems with a database that is 450 GB. I am trying to get the mdf size first, so backup can really take place. For some very large tables, I move the indexes to another group of files (which I hope can be transferred to a separate disk later), trying to reduce the size of the mdf.

My question is: let's say that I have all the indexes in a separate group of files, and not in the mdf part, and only mdf is copied. If the system went down and the backup had to be restored, what is the worst case scenario if none of the groups of index files were backed up?

I assume that everything will be fine, but SQL will have to rebuild the indexes, which can lead to significant slowdowns.

Note. Before you blow me up for bad practices, I inherited these problems and now I play firefighter.

Answer 1

Creating indexes can take some time, so if you need to recreate them all from scratch, you will potentially lose your definitions (unless they are properly documented) and they will have to spend a lot of time waiting for them to be created one at a time. Depending on the size of the tables, it may take some time to create only one.

I would suggest that as part of what you are doing, you are looking at what the actual indexes are doing, and if you can remove / reduce yours. Many times, developers and database administrators build too many indexes, and a few well-designed ones can replace several hastily-built indexes.

Answer 2

Check out Brent Ozar's answer for ServerFault: https://serverfault.com/questions/1/how-to-exclude-indexes-from-backups-in-sql-server-2008

In a disaster, you restore the primary filegroup on its own. Data is suddenly online, but not indexes. However, in order to return to normality, you will need to export this data to a new clean database and add indexes from there.

Check out the link for more details (and give it hints). He also has a link to a filegroup recovery tutorial.