472,958 Members | 2,568 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 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.


Feb 17 '07 #1
1 1929
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
Books Online for SQL Server 2000 at
Feb 17 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: karolina | last post by:
Hi, I building a little site using PHP and mysql. Now I want to make full text searches on some columns in one table. The problem is that I have 25 colums in the table that I want to put at...
by: Gary N | last post by:
Sr. Database Administrator Permanent Position/Pittsburgh Pa $70-95K with full benefits Description A client of A.C.Coy's is looking for a Senior Database Administrator to join their team on a...
by: Sean Shanny | last post by:
To all, The facts: PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI drives in hardware RAID 0 configuration. Database size with indexes is currently 122GB. DB size...
by: Jimmy Chen | last post by:
Recently I've done a db2 backup and restore/recovery, but the process for recovering the database was different than what I thought to be. here is what I did: DB2 is set in online mode -...
by: Rob | last post by:
I need to allow a user with the default "Full Data User" permissions on the front-end database to relink to a table in a backend to which he has the same permissions. (I'd like to do read, update,...
by: Rendladas | last post by:
Dear all, please help me. How to recover the deleted fields in postgre sql table with values from bakup. please help with each step/syntax. thanq, ...Das
by: hbkiwi | last post by:
Hi, Just playing with Access fairly new to it (like days old). Also it's my first post here so if I'm in the wrong place, sorry.. What I'm wanting to know is basically I have lets call it Table...
by: Michel Rouzic | last post by:
I made a program that accepts as parameters an input file name that we'll call file1, and an output file name that we'll call file2, and opens on its own a configuration file called file3. My...
by: Sham | last post by:
I am trying to perform the following query on a table that has been indexed using Full Text Search. The table contains multiple columns than have been indexed. (Below, all xml columns are...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.