Elaine,

There are a number of routes you can go. One of the easiest is to detach the database, make a copy of the file and re-attach it and re-attach the copy when you need it - or immediately under a different name. You can then delete from the current one and delete from the copy as appropriate. The problem is 3 fold - 1: you need to delete a lot of records from each db (and you need to be familiar enough with the schema to know what to delete) 2: They need to go through this process each time they want a backup - or they need to copy data across databases (after creating scripts or a procedure to do this 3: The DB is unavailable while detached and being copied. (If you do this, don't forget to update statistics after doing the deletes.) Other options are to bcp out, use DTS to copy the tables or inactive portions of them, or there is a wizard for copying a DB. In BOL, it is mentioned for copying between servers but you might be able to do it on 1 server.

. . . Tom

On 07/26/2002 10:36:55 AM "Fang, Elaine" wrote:
> Hi list,
>
> We have a customer saves 2 years worth of data ---- they only use 1 year of
> data frequently, while the other year's data was only be there in case they
> need them. Now they feel their db is kind of too big and ask us if we can keep
> the size of the db down while still keep 2 years of data somewhere so they can
> access it when needed.
>
> Here is what I thought:
> They can export part of the data (which they don't use much) to either another
> db or a text file which could be saved on tapes. After make sure the data are
> exported successfully, they can permanantly delete this part of data from their
> db thus make the db size about half of what they have now. And in case they
> need to use the old data, they can just import that part of data from either
> the other db or external device.
>
> How is that sound to you? As to export to another db or text file, which one is
> more preferable if customer has no preference? I think text file maybe easier
> for the customer since in order to export to another db, they need to create
> schema on that db first before export data (since export won't create schema),
> right?
>
> Any other suggestions?
>
> Thanks a lot,




-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page