create a copy of company with same structure but no data

(0) Share

Report

Posted on by Josh Sones

295

I can create new companies all day. I can also copy production company db to a test company. Heck, I can even create brand new instances on new sql servers.

I just want to copy the structure of the production db to a new test db without any records. Our developers and qa will have unrestricted access to the data as a necessity in this test domain. Per gov't regulations, company policies, and just common sense, there is data that they should not be able to see (salaries, customer info, financials, etc, etc...)

So, is there a tool or process that can do this? Is there a matrix of tables with data to delete from the production copy or to import from prod into a company db? Is there a script to scramble/cleanse this data?

I am aware of the clear data function, but I do not know what to clear and what not to clear in order to achieve the objective.

We are sql 2008 x64 gp 10.0

Any insight would be appreciated.

*This post is locked for comments

I have the same question (0)

All responses (16)

Answers (0)

Warren R. Pease 730 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

Check out the Professional Service Tools (available from your partner). It has a copy company function. You can specify which set of setups and master files can be moved over. This may work for what you want to do.

Warren

Was this reply helpful? Yes No
Suggested answer

Howard Swerdloff 845 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

Hi Josh,

Take a look at the following link. It has information on how to copy just the setup information.

support.microsoft.com/.../872709

Was this reply helpful? Yes No
Suggested answer

Ian Grieve 22,784 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

In March last year PSTL was made freely available for GP 10 and 2010 via your partner and will largely automate the copy between companies.

The KB posted by Howard gives you the relevant tables to do the job manually but will be more work.

Ian

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

RE: create a copy of company with same structure but no data

If all you want are empty tables, all you have to do is use GP Utilities to create the compnay and then create a sql script to issue a truncate table command on each table. This will leave the table structures in place but with no data. Is this what you need?

Was this reply helpful? Yes No
Josh Sones 295 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

Thank you for your reply. That tool may work, but then which ones do I copy over? For instance, if I copy customer master that will bring customer data, but that is exactly what I do not want. But if I do not copy it will customer setup info be missing too. I just want the new database setup exactly like the other, no data.

Was this reply helpful? Yes No
Josh Sones 295 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

Thank you for your reply Howard. So in that kb article it lists the Customer Master as a table to copy, but as I stated in my reply to Warren R. the problem is that is copying that table is doing exactly what I do not want which is to have all the customer data in the new db.

Was this reply helpful? Yes No
Richard Whaley 25,195 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

Well, it depends on what you mean by customer data. The customer master contains name and address info as well as credit limits, et cetera. It does NOT contain histories of purchases, payments, or account balances.

Now, if you need a complete database for testing without real customer data, why not install the TWO demo database?

Was this reply helpful? Yes No
Josh Sones 295 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

Perhaps I am asking how to do this the wrong way. Instead, is there a way to 'sanitize' the data? It would be great to simply maintain all the data, but just 'rearrange' it so that it is not real. (i.e. change customer name John to Moe, customer address from 20 memory lane to 1234 abcd Ave. or salary from 100k to 300.00, etc, etc...)

-or-

if I do follow kb872709, which tables can I clear out and still have the company db be a functional and ready to work and create new test fake records after I have copied them?

Thanks.

Was this reply helpful? Yes No
Suggested answer

Howard Swerdloff 845 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

Sanitizing the data as you put it would be very time consuming. I think you are better copying individual tables from the kb article and entering some sample data. The items to copy would be anything that says "setup" in the table name. I would also copy the chart of accounts (this will not contain any balances or transactions) and things like vendor class master, customer class master, etc. Those make it easier to add new dummy customers and vendors.

Was this reply helpful? Yes No
Josh Sones 295 on at

Like (0)

Report

RE: create a copy of company with same structure but no data

demo db might be fine, but I am worried that any development done and tested against it would not be 100% valid when moving to production because of the suttle differences between the two db's. I am offbase with that thinking? I apologize, but if you haven't realized yet I am pretty much a gp noob (I know <gasp> , right?)

Are there any limitations with the demo db? (i.e. restricted module, cannot load 3rd party module, etc, etc...)

Was this reply helpful? Yes No