Open Forum

Copy DB to New Company, Clear Non-Setup Tables

  • 1.  Copy DB to New Company, Clear Non-Setup Tables

    SILVER CONTRIBUTOR
    Posted Jan 23, 2021 10:40 PM
    Before I go building one out on my own, does anybody out there have a SQL script that they run to "clear" all the transaction tables in a database in order to use it for a new company?  We have several companies and are starting a new one that will be similar in setup to the others, we want to basically take one of the existing companies and wipe it preserving the setup tables so we can start fresh.  I've done it before with smaller companies that don't use as many tables, but the one I'm about to clone is a much larger database and has more usage so figured I'd ask before I went to town.  Thanks all!

    Joe


    ------------------------------
    Joe Miller
    Director of Systems Architecture
    Harrison, Walker & Harper
    Paris TX
    ------------------------------


  • 2.  RE: Copy DB to New Company, Clear Non-Setup Tables

    TOP CONTRIBUTOR
    Posted Jan 25, 2021 08:46 AM
    Hi Joe!

    I found this link on the internet because that's exactly what I'm working on this morning.

    select t.name TableName, i.rows Records
    from sysobjects t, sysindexes i
    where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
    order by TableName;

    Another approach:

    select t.name TableName, i.rows Records
    from sysobjects t, sysindexes i
    where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
    order by TableName;

    select schema_name(tab.schema_id) + '.' + tab.name as [table],
    sum(part.rows) as [rows]
    from sys.tables tab
    inner join sys.partitions part
    on tab.object_id = part.object_id
    where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
    group by schema_name(tab.schema_id) + '.' + tab.name
    order by sum(part.rows) desc

    Once I've got it cleared out, I will back up and restore into TEMPLATEDB so it's available for re-use when this comes up again.

    Stay safe!

    ------------------------------
    Gail Jones-Nemeth
    Financial Systems Analyst
    Creative Associates Int'l
    Washington DC
    ------------------------------



  • 3.  RE: Copy DB to New Company, Clear Non-Setup Tables

    SILVER CONTRIBUTOR
    Posted Jan 25, 2021 08:55 AM
    So I got impatient over the weekend and started working on it myself.  I have something similar below, but it provides you some columns that you can then utilize to run selects against each table as you review as well as the delete script so that you can copy/past them into a new SQL window.  Doesn't do the whole job, but it helps quite a bit in figuring things out.

    SELECT
    DB_NAME() as DBName,
    SCHEMA_NAME(t.schema_id) SchemaName,
    t.[name] TableName,
    SUM(p.row_count) TotalRows,
    'SELECT ''' + t.[name] + ''' AS TableName, * FROM ' + t.[name] + ' --' + convert(varchar,SUM(p.row_count)) + ' rows' AS SelectSQL,
    '--DELETE FROM ' + t.[name] + ' --' + convert(varchar,SUM(p.row_count)) + ' rows' AS DeleteSQL

    FROM
    sys.tables t
    INNER JOIN sys.dm_db_partition_stats p
    ON t.object_id = p.object_id
    AND t.type_desc = 'USER_TABLE'
    AND p.index_id IN (0,1)

    --WHERE
    -- t.[name] = 'ARTran'

    GROUP BY
    t.schema_id, t.[name]
    HAVING
    SUM(p.row_count) > 0
    ORDER BY
    --TotalRows DESC, t.schema_id, t.[name]
    t.[name]

    ------------------------------
    Joe Miller
    Director of Systems Architecture
    Harrison, Walker & Harper
    Paris TX
    ------------------------------



  • 4.  RE: Copy DB to New Company, Clear Non-Setup Tables

    TOP CONTRIBUTOR
    Posted Jan 25, 2021 09:26 AM
    I would take the opposite approach and use DB Maintenance to create a new database and then insert rows into the relevant tables using a subset of values from the "template" database; e.g., setup tables should not be just copied as columns like CpnyID and LastBatNbr will have incorrect values.

    Project series tables present a mix of problems when "emptying" a database. Several must be populated in an empty database, and some may contain many rows that represent "settings" in a database.

    Using DELETE (rather than TRUNCATE) will not reseed identity column ("RecordID") values in tables like ARTran.

    ------------------------------
    Jonathan Van Houtte
    Architect
    XLstatements, LLC
    Clifton Park NY
    ------------------------------



  • 5.  RE: Copy DB to New Company, Clear Non-Setup Tables

    SILVER CONTRIBUTOR
    Posted Jan 25, 2021 09:41 AM

    Good point Jonathan.  I did go back through all remaining tables and reset columns like that myself, but starting from an empty database is certainly another way to go!

     

    Joe

     


    Joe Miller | Director of Systems Architecture
    HCS | 2510 S Church St, Paris, Texas 75460 | www.hwh1887.com
    Toll: 1.800.442.TEAM  | P: 860.839.6970 | M: 860.839.6970 | F: 903.784.1471 | jmiller@hwh1887.com

     

    Confidentiality Note: This e-mail message and all attachments are intended solely for the use of the addressee and may contain privileged and confidential information.
    Any unauthorized review, distribution, copying, or other use of this message or its attachments is strictly prohibited.
    If you have received this message in error, please notify the sender immediately by replying to this message and delete it from your computer.






  • 6.  RE: Copy DB to New Company, Clear Non-Setup Tables

    Posted May 11, 2021 02:28 PM
    Hi everyone, is there a way to differentiate the catalogs from the transaction tables? this is because I need to leave the catalog screens intact I hope you can help me, thanks for the excellent work

    ------------------------------
    Manuel Vela
    Tec
    ------------------------------



  • 7.  RE: Copy DB to New Company, Clear Non-Setup Tables

    Posted Jan 25, 2021 02:09 PM

    Way back when I had to do the same thing on a regular basis, I did something like this to get the list of tables I needed to be concerned with, including any 3rd party or custom tables.

    Then just figure out which master record or setup tables have company/sub specific info and make sure that also gets changed.

     

     

    DBCC UPDATEUSAGE(0)

     

    SELECT      T.name TableName,i.Rows NumberOfRows
    FROM        sys.tables T
    JOIN        sys.sysindexes I ON T.OBJECT_ID = I.ID
    WHERE       indid IN (0,1)
    ORDER BY    i.Rows DESC,T.name