title: Automating SQL Server Dev database refresh from Prod
body: <p>Seeking some advice before I dig in. Let me explain the ask and my current process.</p>
  
  <p>We currently have Dev teams who require refreshed data from the Prod databases placed into there DEV databases. There requirements change, sometimes they just need tables and other times they need different subsets of the below</p>
  
  <pre><code>Tables
  Views
  Stored Procedures
  Users
  Schemas
  </code></pre>
  
  <p>Currently the process is completely manual and is as outlined below</p>
  
  <ol>
  <li>Disable Job responsible for replication of Prod DB (actually standby)</li>
  <li>Highlight Prod DB and "Generate Scripts" </li>
  <li>Select the Options that is required (see above e.g. tables views etc..)</li>
  <li>Backup Dev DB (just in case)</li>
  <li>sp_msforeachtable and drop each table from dev db</li>
  <li>Execute the script that was generated from step 2 on dev db</li>
  <li>Then use the import wizard to pull the data from the prod source</li>
  <li>An additional sql script is often required to run post import on new Dev DB (scrubber)</li>
  <li>Enable Job on prod for repl</li>
  </ol>
  
  <p>The SQL Server instance hosts can change as can the databases so variables will need to be passed. The SQL Servers are 2008 on Windows 2008. The box that I host the script/instance on can be any version of windows and any version of SQL Server.</p>
  
  <p>I'm hoping to automate this process, at first just for the SA teams (so could be ps or cli). Eventually (hopefully sooner than later) however present this in some type of ui to the dev teams so they can manage themselves.</p>
  
  <p>I'd prefer if this all runs from a management box running SQL Server and not the SQL Server instances that host the databases. I'm not sure what options are available but I suspect SSIS could be used or PowerShell and SMO and I'm sure there are other crude ways.</p>
  
  <p>I'd like this to be somewhat elegant so it's easily presentable to management. I'm comfortable with PowerShell and SQL but have no experience with SSIS.</p>
  
  <p>Anyway looking for some suggestions.</p>
  
  <p>EDIT:</p>
  
  <p>So my Requirements have actually changed. I now need to scrub the data then backup then post to a share for dev. I'm nearing completion of my script, which is powershell using SMO. I'll give a brief description below and when I'm complete I'll post more details. Prod is over wan, as are the backups. We have have log shipping enabled to my site which is the data I have to work with. Steps are probably going to make some gringe but its necessary because by db is standby. </p>
  
  <ol>
  <li>Create new database by looping through source DB using smo for files/file settings</li>
  <li>Backup the newly create database with standby / readonly</li>
  <li>Stop Job for log shipping to source db</li>
  <li>Take source db offline</li>
  <li>Take newly create db offline</li>
  <li>Replace newly created DB files with source db files</li>
  <li>Bring both DB's back on line</li>
  <li>Start Job for log shipping to source db</li>
  <li>Restore new created / newly copied file DB with recovery</li>
  <li>Execute .sql to scrub new DB </li>
  <li>Backup new DB</li>
  <li>Copy to share for dev</li>
  </ol>
  
  <p>So thats it, all sql related work is done through SMO. I'm pretty much done, I've built out functions for each step which all work, I just have to pull it all together. </p>
  
  <p>Not pretty but does the job...damn that wan!</p>
  
  <p>EDIT 2:</p>
  
  <p>I ended up backing up, copying local, scrubbing, backing up again with compression than copying across that WAN overnight. I did this all through task scheduler / ps / SMO.</p>
  
  <p>Thanks to all that offered advise</p>