471,071 Members | 9,562 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

Full bakup of Data

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.

Thanks
~Shiju

Feb 17 '07 #1
1 1867
shiju (sh**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 17 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by karolina | last post: by
4 posts views Thread by Michel Rouzic | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.