Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: Need script that can shrink copy of DB to fit on a notebook

    I need a script that will take a 40GB 300+ table database and shrink it to the 1st 1000 rows in each table and delete security tables like tblchargecard. Want to get size to about 1gb to fit on a notebook for development. Any suggestions would be appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    declare a table variable with two columns, table_name and Table_rowcount.

    From a join between sysindexes and sysobjetcs table, get the table names and their respective rowcounts into this table variable.

    update the table_rowcount columns with table_rowcount-1000

    write a script to automatically generate delete statements for each table, each delete statement being preceded by set rowcount table_rowcount and followed by set rowcount 0 statement.

    run this generated script.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Creative, but I think that will crash if you have relational integrity established, and especially if you are using cascading deletes.

    If your database does have cascading deletes, (as it should) then just delete everything but, say, every 10th record, out of the highest level tables in the schema. (You can use something like WHERE Right(PrimaryKey, 1) <> 0 if you have numeric keys, for instance.) Do this in a copy of the database, of course!

    As far as "delete security tables like tblchargecard", you'll have to specify those in your script.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Posts
    6
    Thanks for the help from both of you. will give this a try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •