Restore dev database on a weekly basis, will this break SSC?

dbdmoradbdmora Posts: 50
edited March 15, 2012 4:55AM in SQL Source Control Previous Versions
Hello, we have dev databases that need to be updated with the latest data once a week. What we plan to do is tell developers to commit their code every night into SSC, then on the weekend we would restore the dev database from production so they have the latest data come Monday morning. Then we would do a 1 time get latest to ensure the dev DB has the same code as SSC because not all code will be deployed to production.

If we do this, will the link to SSC break for developers who have it linked? Is there a way to automatically get the latest by running a batch file? Thanks for any imput.
· Share on Twitter

Comments

  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Yes, this can be automated using the sqlcompare.exe command line.

    What is driving the weekend restore? Have you got a build automation tool?

    Which source control system are you using?

    David Atkinson
    Red Gate
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • dbdmoradbdmora Posts: 50
    Hello David, we are using SVN as our Source control system. the weekend restore is being driven by developers requesting ad-hoc refreshes to the database data. If developers know that the datbase data will be refreshed once a week, they wouldn't need to ask for this request.

    SSC will not care if we restore a database that it is linked to?
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Yes, it will remain linked as it's the name of the database that matters.

    What automation mechanism will be used to refresh the database?

    Do you have a shared database or dedicated dev databases?
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • dbdmoradbdmora Posts: 50
    We will be using t-sql into a job that will run this process to restore the DB. All of our Link DBs are in sharing mode.

    Do you have an article I can read that will help me setup sql compare to do a get latest from SSC to our restored Database? I am sure we would have to put this in a batch file of some sort, so we can automate it.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    If you type svn -? in a command prompt, you'll get the list of commands. You can then do svn checkout -? to get help for a specific command.

    This might work (replace the values in square brackets with yours):
    svn checkout "[svn://yourrepo/DBScriptsFolder]"  [DBScriptsFolder] --username [YourUsername] --Password --[YourPassword]

    Once you've got the DB scripts in a folder, you can use the SQL Compare command line as follows:
    sqlcompare.exe /scripts1:[DBScriptsFolder] /db2:[YourRestoredDevDB] /s2:[YourSQLServer\Instance] /sync

    If you're using SQL Server authentication, you'll need /UserName2: and /Password2:

    More information about the SQL Compare command line can be found here:

    http://www.red-gate.com/supportcenter/C ... _in_the_cl
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.