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

dbdmora

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.

David Atkinson

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

dbdmora

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?

David Atkinson

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?

dbdmora

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.

David Atkinson

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