Posted by Erland Sommarskog on 02/17/07 10:59

shiju (shiju.samuel@gmail.com) writes:
> A full DB backup of one of my database takes around 3 GB. Half of this
> is index. All the indexes are in different filegroup. I am wondering
> if I can only backup the data. For Index I can always run the script
> to regenerate it.
>
> By doing this I can reduce the size of the backup file.

You can indeed backup an individual filergroup. In SQL 2000, I don't
think this is much help, because in case of a disaster you cannot get
database online by only restoring the backup of the data filegroup.

This is possible in SQL 2005, so possibly you could recover the database,
drop all indexes, and recreate them from scripts.

But before you settle on this solution, make a test to see that it really
works. I would not be surprised if you are told that you cannot drop
indexes, because they are not online. You may also find that you will also
have to drop foriegn keys to be able to drop primary keys.

And you have to ask yourself: if the database goes capoot while you are
in the midst of something else, and users are yelling that they need the
database back pronto, do you really want to be entangled in a complex
restore operation?

A databasee of 3GB is not a very big database, and neither is a backup of
it. Restoring a 3GB database in a single operation is a breeze. I would
definitely not consider a 50% reduction of the backup size to be
worth the extra pain in a stressful disaster situation.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]