locked
Whats the best way to transfer test data to a live SQL database? RRS feed

  • Question

  • User2090306728 posted

    Although I have a rough idea how the tables in my SQL Server database will be structured, I may need to add fields to them depending on some of the test data I have. Theres quite a lot, so I'm planning on adding quite a few records and modifying the tables when required before I go live.

    I'm using MVC so, as my question above, whats the best way to transfer the test data to the live database when I'm finished? Obviously I will need all unique ids to remain intact so my SQL joins don't mess up.

    Should I CREATE each table with no primary and foreign keys, use multiple INSERTs for all my test data, then perform an ALTER TABLE to add the keys back to the live database for each table or is there an easier way to do this?

    Any help would be greatly appreciated.

    Thanks

    Friday, November 10, 2017 2:17 AM

All replies

  • User347430248 posted

    Hi pizzaboy,

    I suggest you to take a backup of your live database.

    so now you have the exact copy of your live database.

    now you can do your testing on that database and implement the changes to your actual database.

    so your live database not get affected by your testing.

    here, I am not sure why you want to transfer your test data to live database which can lead to any issue in future.

    Reference:

    Create a Full Database Backup (SQL Server)

    let me know , if I misunderstand something in your above description.

    I will try to correct myself and try to provide you further suggestions.

    Regards

    Deepak

    Friday, November 10, 2017 3:01 AM
  • User2090306728 posted

    Hi Deepak,

    I haven't purchased any web hosting (or the domain for that matter), as I assume this project will take quite a while to complete due to having to constantly modify the tables according to the information I'm adding. 

    The data I will be adding, I want as the actual data, so when I eventually go live, I'll have quite a substantial amount of info already inputted.

    Any more suggestions? Apart from quite basic querying and updating, my SQL is quite limited.

    Cheers,

    Pete.

    Monday, November 13, 2017 1:23 PM
  • User347430248 posted

    Hi  pizzaboy,

    you had mentioned that,"I assume this project will take quite a while to complete due to having to constantly modify the tables according to the information I'm adding."

    this is not a good approach to changes the table design many times.

    I suggest you to first gather all the requirements and then try to design a database.

    build a solid design so that you not need to modify it frequently.

    try to follow the database design rules to design database will help you to design robust database.

    Reference:


    11 important database designing rules which I follow

    Regards

    Deepak

    Tuesday, November 14, 2017 8:41 AM
  • User2090306728 posted

    Thanks for the advice Deepak,

    I have developed software systems before, and I'm fully aware that "typically" having the structure of the tables prior to building the project is the best (and probably the only) recommended approach.

    Unfortunately, this project is very complicated. Although the major keys and fields can be clearly identified, a lot of the database querying is defined by the settings which multiple users submit (mostly, people I won't know, and then in some cases an in-between admin). If I could, I would definitely use normalization design principles, however going through every single possible setting for each scenario is currently not a realistic possibility so I'm having to add data (fully functional data from past records) as I go along.

    I just need to know how I will perform updates from my "test" data to the "live" database when the time comes.

    Thanks.

    Tuesday, November 14, 2017 12:27 PM
  • User753101303 posted

    Hi,

    Do you want a full copy? If yes, just backup and restore your db. It could also depend on what your hosting service will allow (it could be a good idea to make sure they have the same SQL Server version to avoid last minute surprises).

    Not sure why you feel you would have to wait for adding pk and fk to your db ?

    For later changes I then use a SQL script (most often that I create myself directly though EF can also do changes or generate a script for you if you prefer).

    Tuesday, November 14, 2017 12:41 PM
  • User2090306728 posted

    PatriceSc

    Not sure why you feel you would have to wait for adding pk and fk to your db

    Was worried that I would have to delete some records and therefore have gaps in between the primary key numbers (some of the tables use incremental ids)

    PatriceSc

    Do you want a full copy? If yes, just backup and restore your db.

    Will look into this. Not really sure how I would restore to a different db - that was my initial concern.

    Previously when I checked hosting options, they had a web interface so not 100% certain of how much control I have with regards to backups...

    If I was to write a script, how would I reference tables from 2 different databases?

    Thanks for your input PatriceSc

    Wednesday, November 15, 2017 9:30 AM