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.

  • There's no easy way around it that I can think of.  You're going to need an intermediate database that you would then drop the tables that you don't want included and then run through a loop and delete the necessary rows in the appropriate tables.  I would then shrink the database, back it up, and move it to your laptop.  Obviously, all of this could be setup in a DTS package that could be automated, but I think your initial setup is going to have to be created manually.


    David

  • How about a cursor-driven "SELECT TOP 1000 * INTO..." statement. You could script the source DB, remove the CREATE TABLE statements, run the cursor code then use the script to recreate indexes and such. The source_tables cursor would be modified as required to include/exclude any tables.

    declare @table_name varchar(100)

    declare @sql varchar(100)

    declare source_tables cursor for

    select name from sysobjects where type = 'U'

    and name <> 'dtproperties'

    order by name

    open source_tables

    fetch next from source_tables into @table_name

    while @@fetch_status = 0

     begin

     select @sql = 'select top 1000 * into target_db.dbo.' + @table_name + ' from ' + @table_name

     print @sql

     execute (@sql)

     fetch next from source_tables into @table_name

     end

    close source_tables

    deallocate source_tables

     

    Donhttp://www.biadvantage.com

  • Thanks for the help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply