Post reply

Reflecting changes in production Db onto staging DB

  • July 21, 2004 at 11:45 am

    #108509

    I have a database db1 on server1 and server2.The Db On server1 is a production db and the Db on server2 is a staging Db.All the new data will be coming into production Db.And i wanted to update the data and database structures on staging Db from production Db on weekly basis.So how can I reflect the data and datastructures on my staging Db from my production Db.

    Thanks.

  • July 21, 2004 at 12:13 pm

    #515518

    Try http://www.red-gate.com/sql/summary.htm

  • July 21, 2004 at 9:02 pm

    #515567

    While the software from Red-Gate will work I think an easier way would be to simply use a backup/restore scenario. Another way to do this would be to use Replication with the Production database as the Publication and the Staging database as the Subscription database. Using Replication you would then be able to schedule the snapshot agent to run on a weekly basis.

    Ultimately the best solution will depend on your business needs/knowledge.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • July 22, 2004 at 7:19 am

    #515669

    Gary,

    In the case of replication, how would you replicate new tables added.

    How can you replicate new tables, roles and users ?

    Thanks

  • July 26, 2004 at 6:02 pm

    #516179

    Sylvain,

    That is the reason I stated that it ultimately depended on their business needs. It may be easiest to simply restore a database on the remote machine. Without knowing their business model I could not answer that.

    As for how you can replicate new tables, roles, and users... That would certainly take a lot of discipline and some very careful planning. Ultimately you can add the new tables to the publication and then do a snapshot that contains these new data structures.

    I in fact have a situation where I use Snapshot Replication to make a copy of a database on a different server that I have to do builds from. This Snapshot scenario requires me to first drop/create the target database and then apply the snapshot and assign permissions appropriately by running some stored procedures after the replication has completed. I have this all automated by several SQL Jobs that are triggered by a custom application that my users can control. It is a pretty neat system that requires little maintenance. However when I leave I defy the person who replaces me to figure out what I did!




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 5 posts - 1 through 4 (of 4 total)

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