SQL Server - loss of indexes scenario

Note: Not sure if SO has a specific DB site so I'm putting this question here.

SQL Server 2008 Enterprise.

I'm working to resolve many issues with a database that is 450gb. I'm trying to first get the size of the mdf down so that backups can actually take place. For some very large tables I'm moving indexes to a different file group (which hopefully can be moved to a separate drive later on) in an attempt to get the mdf size down.

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

I'm assuming that things would be ok but SQL would have to rebuild the indexes which would cause massive slow downs for a bit.

Note: Before you blast me on bad practices, I've inherited these issues and I'm playing firefighter right now.

Creating indexes can take a while, so if you have to recreate them all from scratch you'd potentially lose the definitions (unless properly documented) and have to spend a lot of time waiting for them to build one at a time. Depending on the size of the tables, it can take a while for just one to be created.

I would suggest that as part of what you are doing, you look at what the indexes are actually doing and if you can remove/reduce how many you have. A lot of times developers and DBAs build too many indexes, and a few well thought out ones can take the place of several hastily thrown together indexes.

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

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.

Check out the link for more details (and to give him the upvotes). He also has a link to a tutorial on doing filegroup restores.