locked
Create test database with stub of data from production database RRS feed

  • Question

  • Hello,

    I want to create a testdatabase on a developer laptop. The database is a copy from production, but it is too big for the laptop. Therefore I want to create the schema on the laptop and then transfer round about 10% of the prodution data to the laptop's testdatabase. Consistency and relations should be preserved to get valid test results.

    Is there any tool or script that could help me or will I have to write the INSERT statements manually (will be a huge amount).

    Thanks

    Thursday, February 5, 2015 12:38 PM

Answers

  • I believe per my knowledge, there won't be any tool which will understand your business and your all applicable valid business flows end to end. It depends upon nature and size of application.

    So the short answer is we need to do Manual way, as we need to maintain Consistency and relations should be preserved to get valid test results.

    Few years ago, I came accross such situation where I was having these same task, and I have proceed as follows per discussion with all my seniors and peers.

    1. Shortlisted all Master tables and simply export the insert table scripts from production to a Test DB ( Those table which are used for populating the Dropdowns in our application, like  County, Designations, Regions, Departments etc).

    2. Navigate to application pointing to new Test DB having only master tables. And all we need is data entry in an smart and efficient way.

    3. Start with your basic entity, like in my case I started with creating a Patient, then entering Patient details and its appointment details and then diagnostic details , discharge details etc.

    4. Here it is important to note, that we need to follow each and all business flows. The QA team and/or BA support is important to not miss any flows.

    5. Over the time out Test DB was ready with couple of Patients, we did backup and this is ready for a light version or sample production DB.


    Thanks
    Manish
    ___________________________________________________________________________________
    Please click Mark as Answer if my post solved your problem and click Vote as Helpful if this post was useful.

    Friday, February 6, 2015 7:39 PM

All replies

  • Right click on the database  and click Generate Script

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 5, 2015 1:53 PM
  • Hi,

    thanks for you reply, but this is not my question. I already have a schema local.

    I want to transfer data to local test database, but not the whole data, only 10% of it and keep the relations valid.

    Thursday, February 5, 2015 2:56 PM
  • Hi R2LS,

    You can use SQL Server Import and Export Wizard to achieve the requirement. With the wizard, you can construct a Transact-SQL query to retrieve the expected data from your production database tables and copy the data to your local test database.

    For more details, please review the following articles.
    SQL Server Import and Export Wizard
    https://msdn.microsoft.com/en-us/library/ms141209.aspx  

    SQL Server Import and Export Wizard
    https://msdn.microsoft.com/en-us/library/ms140052.aspx

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Friday, February 6, 2015 9:17 AM
  • Hi,

    I know the Import and Export Wizard. But I do not want to copy all data. I only need a cull selection of test data, maybe 10% of it.

    If I write a query to copy only 10% from tableA to tableB, the relations do not match any more. Therefore I need some logic to copy 10% of tableA and all appropriate data in related tables.

    Friday, February 6, 2015 9:53 AM
  • You can use replication and use row filters for the articles to publish partial data. There are some caveats as some data types (such as varchar(max)) are not supported. See https://msdn.microsoft.com/en-us/library/ms151775.aspx

    Satish Kartan www.sqlfood.com

    Friday, February 6, 2015 10:41 AM
  • I believe per my knowledge, there won't be any tool which will understand your business and your all applicable valid business flows end to end. It depends upon nature and size of application.

    So the short answer is we need to do Manual way, as we need to maintain Consistency and relations should be preserved to get valid test results.

    Few years ago, I came accross such situation where I was having these same task, and I have proceed as follows per discussion with all my seniors and peers.

    1. Shortlisted all Master tables and simply export the insert table scripts from production to a Test DB ( Those table which are used for populating the Dropdowns in our application, like  County, Designations, Regions, Departments etc).

    2. Navigate to application pointing to new Test DB having only master tables. And all we need is data entry in an smart and efficient way.

    3. Start with your basic entity, like in my case I started with creating a Patient, then entering Patient details and its appointment details and then diagnostic details , discharge details etc.

    4. Here it is important to note, that we need to follow each and all business flows. The QA team and/or BA support is important to not miss any flows.

    5. Over the time out Test DB was ready with couple of Patients, we did backup and this is ready for a light version or sample production DB.


    Thanks
    Manish
    ___________________________________________________________________________________
    Please click Mark as Answer if my post solved your problem and click Vote as Helpful if this post was useful.

    Friday, February 6, 2015 7:39 PM