Reduce the Database size

Hi,
I have a database with size 20 GB.
The DB contains 550 tables, 2110 stored procedures.
I need to write this DB in a DVD. So I have to reduce the DB size around 6 GB.
It is not necessary that all the data should be in DB. Some sample data is
enough.
Is there any way to do it with less administrative effort?
Please advise me,
Soura
Soura - this is such a propriety requirement, you'll have to code this
yourself. One posibility is to restore a backup of the database and choose a
central table to the schema and do a delete. If you previously turn on
cascading deletes throughout the database then this'll be a convenient way
of removing related data. Do this the required amount of times and then
shrink the database before copying the database to the DVD.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for your reply.
I enabled 'cascade delete' and saved it through Enterprise manager
and closed that window but then i re-opened that the table design the
'cascade delete' is unchecked ie., it is not saved.
Please guide me,
Soura
"Paul Ibison" wrote:

> Soura - this is such a propriety requirement, you'll have to code this
> yourself. One posibility is to restore a backup of the database and choose a
> central table to the schema and do a delete. If you previously turn on
> cascading deletes throughout the database then this'll be a convenient way
> of removing related data. Do this the required amount of times and then
> shrink the database before copying the database to the DVD.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||SouRa,
please can you run the following type of script in query analyser to change
the FK to be a cascade-delete one, and post back any error messages you are
seeing.
ALTER TABLE dbo.Territories WITH NOCHECK ADD CONSTRAINT
FK_Territories_Region FOREIGN KEY
(
RegionID
) REFERENCES dbo.Region
(
RegionID
) ON DELETE CASCADE
NOT FOR REPLICATION
GO
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for your reply Paul.
Can i have a query to run for whole database.
Advise me
Soura
"Paul Ibison" wrote:

> SouRa,
> please can you run the following type of script in query analyser to change
> the FK to be a cascade-delete one, and post back any error messages you are
> seeing.
> ALTER TABLE dbo.Territories WITH NOCHECK ADD CONSTRAINT
> FK_Territories_Region FOREIGN KEY
> (
> RegionID
> ) REFERENCES dbo.Region
> (
> RegionID
> ) ON DELETE CASCADE
> NOT FOR REPLICATION
> GO
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||There are scripts out there that you could modify slightly to do this
(http://www.sqlservercentral.com/scri...p?scriptid=971) but
I'd get EM to help you out here. Have EM script out all the FKs to a
textfile. Do a find-and-replace on
") NOT FOR REPLICATION" with ") ON DELETE CASCADE NOT FOR REPLICATION"
and ") GO" with ") ON DELETE CASCADE GO".
Have EM also create a drop script for the FKs.
At this stage you should have everything you need.
Obviously try this out on a test database first
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .