Copy data and structure of database

  • Krasavita

    SSC-Insane

    Points: 23136

    Hello, Is anyone know any tools that you can copy one server to another. We have 3 servers: Production, Staging and development. I would like to copy their data from Production to Staging and from Production to Development. I have a store procedure that runs everyday and deletes data from tables and inserts data from Production to Staging and development. I am trying to find a better way to do it.

  • Luke L

    SSC-Dedicated

    Points: 35463

    Since you're interested in both data and structure, why don't you just restore the previous night's backup from the production server to test and dev?

    Alternatively you could check out tools such as RedGate's SQL Data Compare to help keep them in synch.

    To help us help you read this[/url]

    For better help with performance problems please read this[/url]

  • steveb.

    SSC-Forever

    Points: 46732

    You can restore a production backup to your dev server, this would be the easiest and quickest.

    Or else there are thrid party tools out there, such as Redgate SQL compare which gives you a bit more control over what excatly is copied.

  • Krasavita

    SSC-Insane

    Points: 23136

    Thank you

  • Jerry Hung

    SSChampion

    Points: 12858

    We download our production data in house, then auto-restore them to Staging/Report Server

    That's the easiest way

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005
  • anupam.jha

    SSC Veteran

    Points: 217

    Similar to this situation, we need to replicate only the database structure from one SQL Server to another SQL Server (v2005).

    How could this be achieved across 2 sql servers having direct connectivity between them.

    The reason to have such a arrangement is that the production db structure keeps changing and we need to replicate everything as part of one of our deliverables.

    So, we are hoping to carry on this db structure activity on staging server and timely replicating the db structure on another server (maybe a staging server) to keep it in sync.

    Please Help.

    Thanks.

  • BrainDonor

    SSCoach

    Points: 19181

    To initially get your database structures back into sync I would use Redgate Compare. After that, whatever process you use to change the structure of your main database must also include the process to update the staging database.

    Here, we have environments for testing and QA that replicate the structure of Live, and any changes to databases required for Live (including staging, data warehouses and so on) are scripted and tested in these environments prior to Live deployment.

    Steve Hall
    Linkedin
    Blog Site

Viewing 7 posts - 1 through 7 (of 7 total)

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