Can I set up a job in SQL Management Studio that uses a SQL Compare script with special parameters?

Varwulf

Hello everyone. I have a very specific request from a company I am contracting with that wants a job, or something similar that can be run on a whim, that will take all of the data in our Production environment and copy it out to our QA and Dev environments but there are some special parameters and requirements needed for this process.

Firstly, I need to be able to take any sensitive information (let's say email addresses) and generate some randomized email addresses in their place, not just taking a direct copy. From what I've read, this can be achieved during the copying of one database to another, but a SQL Data Generator project needs to be created and attached to this process in order to generate the "fake data". I am hoping there is a less cumbersome way of performing this task, for this might be a bit more overheard than the company is looking to undertake.

The other issue is though I like the idea of creating a batch or PowerShell script, I believe the requirement calls for a job or something to that affect that can simply be run by a developer from within SQL Management Studio, or worst case scenario, I have to develop a .NET app to handle this process (but if that were the case, I'd need to know if Red Gate offers an API that could easily be utilized for such an endeavor).

I realize my requirements are probably a bit too much to ask for, but I hope that I can at least get a better understanding of what this undertaking is going to require before I really start sinking my teeth into the task at hand. Thank you very much for your time and I look forward to reading your responses.

AlessandroAlpi

Hi @Varwulf,

this one

that wants a job, or something similar that can be run on a whim, that will take all of the data in our Production environment and copy it out to our QA and Dev environments but there are some special parameters and requirements needed for this process.

is one of the most "long standing" problem when managing QA environment. How to bring "real" production data into the QA environments. Redgate has a great tool to do so, which is SQL Clone. I really suggest to give it a try.

About masking (or faking) data, one you've a cloned database, you can do anything you want to it, since it's completely isolated by the source (and the other clones). You can also mask a clone of the source, and then use that clone as the new source image, in order to create many copies of the, let's say, masked-cloned database.
Speaking about repeating this behavior (which is a DevOps approach, actually), you can use SQL Clone with PoSh or even Windows task scheduler.

I think that your requests aren't too much. I believe that we should approach repeatedly, when this is possible. By integrating other tools/services we've already in our companies, improving continuously, decreasing the technical debt, writing useful and pragmatic processes, we will also enhance our solutions. Don't you think?

Hope this helps