keeping dev and live databases in sync

  • Does anyone know of any ways to keep a dev database up-to-date with changes on a live database without doubling up on the work?

    Thanks in advance

  • Can you just restore the latest live backup into your dev database when it requires refreshing ?

    However that would also copy data as well as stored procs, database permissions etc from live, which might not be what you want.

    _______________________________________________________________

    Website : www.sqlmatters.com

  • erics44 (7/31/2012)


    Does anyone know of any ways to keep a dev database up-to-date with changes on a live database without doubling up on the work?

    Thanks in advance

    Log Shipping or a manual approach to Log Shipping could work

  • thanks for the reply

    yeah im doing that sort of thing at the minute

    there is currently a lot of movement on the live (we are nearing a deadline) and its very beneficial to keep the dev updated so theres a lot of work also on the dev

    i was hoping that there was some tool or method that replicates the changes on one server to the other without doing these "manual" tasks

    thanks again

  • SQLSACT (7/31/2012)


    erics44 (7/31/2012)


    Does anyone know of any ways to keep a dev database up-to-date with changes on a live database without doubling up on the work?

    Thanks in advance

    Log Shipping or a manual approach to Log Shipping could work

    thanks ill look into it

    my other reply was to SQLEnthusiast 🙂

  • Yes, if you wanted changes to appear on the dev database in real time or near real time then there are several high availability features that could be used such as replication, mirroring or log shipping but these do look like overkill in my opinion. Log shipping also requires the target database to be read-only, which is usually unsuitable for a dev database.

    _______________________________________________________________

    Website : www.sqlmatters.com

  • Just a question, changes are getting made to the production server directly and then you need those brought down to dev and we're not talking data?

    I'd say the process is backwards. You should always be deploying upwards. If you put your database into source control and manage it the same way you do app code, you should be able to rebuild a dev environment at any given moment (minus the data). At least, that's how I helped manage about 15 different development teams.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (7/31/2012)


    Just a question, changes are getting made to the production server directly and then you need those brought down to dev and we're not talking data?

    I'd say the process is backwards. You should always be deploying upwards. If you put your database into source control and manage it the same way you do app code, you should be able to rebuild a dev environment at any given moment (minus the data). At least, that's how I helped manage about 15 different development teams.

    🙂 99.99999 % of the time yes, at the minute we are just going live with a big project that is massively overdue and its like final tweaks, although the final tweaks have been dragging a bit, (in fact even in this instamce it should really be that way, its just come to boiling point where its got to be done yesterday and all resource is on it)

    the process will change to a dev, staging, live situations as soon as the system is live

  • erics44 (7/31/2012)


    Grant Fritchey (7/31/2012)


    Just a question, changes are getting made to the production server directly and then you need those brought down to dev and we're not talking data?

    I'd say the process is backwards. You should always be deploying upwards. If you put your database into source control and manage it the same way you do app code, you should be able to rebuild a dev environment at any given moment (minus the data). At least, that's how I helped manage about 15 different development teams.

    🙂 99.99999 % of the time yes, at the minute we are just going live with a big project that is massively overdue and its like final tweaks, although the final tweaks have been dragging a bit, (in fact even in this instamce it should really be that way, its just come to boiling point where its got to be done yesterday and all resource is on it)

    the process will change to a dev, staging, live situations as soon as the system is live

    Oh, a death march. Those always turn out well...

    I've been in this situation before. I've won the fight and I've lost the fight. The fight is, despite the fact we're in a death march, still do things properly. The places where I won the fight, we knew what we were deploying. The places where I've lost the fight, we spent almost as much time troubleshooting why new stuff kept breaking as we did fixing the stuff that was already broken.

    If you're stuck in that situation, I'm sorry. If you don't have a comparison tool to be able to see what has changed between two different databases, I'd suggest getting one. The better examples of this type of tool can be easily automated so that you capture all the changes on the fly. My personal favorite, SQL Compare [/url]from Red Gate (my employer), but there are others out there (almost as good) that will get the job done. That's what I'd suggest to try to keep this in some type of control.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks

    The comparrison tools are the kind of thing i was looking for

    i was considering scripting the procs and stuff from the sys tables and comparing that way, building a kind of comparrison tool of my own 🙂

  • erics44 (7/31/2012)


    Thanks

    The comparrison tools are the kind of thing i was looking for

    i was considering scripting the procs and stuff from the sys tables and comparing that way, building a kind of comparrison tool of my own 🙂

    That would probably work, but you might hit issues depending the order in which stuff got scripted out. You'd need to control that process pretty closely.

    SQL Compare has a command line that will let you completely automate the process. You can even compare to a backup of the database or to code in source control directly. I've worked for Red Gate for about 18 months, but I've been using this tool for the last 12 years.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I've used MS Access as a quick compare tool across servers (we aren't allowed to use linked servers, or this would be possibl ein SQL Server itself). Use ODBC to point tables in Access to the desired locations in SQL server, and use queries to join the Access tables to show differences. I've done this with the INFORMATION_SCHEMA views to compare definitions. Using various combinations of joins and value comparisons will reveal the differences.

  • Are you looking for this kind of stuff???

    http://mssqltipsandtricks.blogspot.in/2012/08/how-to-schedule-auto.html

    This is good and easy to understand.

    Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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