I've inherited a web application that talks to an MS SQL Server instance. It uses multiple databases and database users.
I'm setting up a new staging environment for this application. It must match the existing database schema, but use "dummy" data. I want the process to be repeatable, so I'm scripting it. The scripts will be kept in version control alongside the app, and therefore cannot contain things like hardcoded passwords.
So far I have something like this:
- In the existing environment, use the "generate scripts" wizard to script all database objects in all databases except table data. Concatenate the results and start with that.
- Script the creation of any necessary service logins, their permissions, and their passwords. Since I can't include passwords in version control, presumably I'll need to use a storedproc that asks for them.
- Create dummy table data in .tsv files, and import them with a BULK INSERT loop or a batch file that calls
bcp.exe
I haven't done anything like this before and want to be sure I'm not missing something important.
- Am I doing this in the right order? I suspect logins may need to be created before schema initialization. But before schema initialization, any stored procs are unavailable.
- Is there anything I need to leave out of the "generate scripts" step, to avoid dumping passwords or instance-specific settings that should not be cloned in this way?
- Is there anything I need to include that I'm missing, for the new SQL instance to have everything it needs? (I am thinking of things like the system databases; I don't think I should carry those over, but I'm not sure, and I'm worried about the users-vs-logins distinction).
Best Answer
Look into dbatools (which uses PowerShell to do everything imaginable)
simplifying disaster recovery with dbatools – dbatools
getting started – dbatools
command index – dbatools
As for dummy data - do you need to only copy schema/objects, or data inside as well and scrub/randomize?