SQL Server - cloning a subset of a database for a test environment
1
vote
0
answers
146
views
I'm trying to set up an automated script which I can run on a schedule in SQL Server 2016 (probably nightly) to copy the structure of our production database and then transfer a subset of data into each table in the new database from the old one so that we can use it for a test environment. Copying the data is fairly trivial. I can use DBCC CLONEDATABASE for copying the structure, but that requires sysadmin rights, which I don't want to have to use because I don't want a sysadmin user's authentication details stored in a connection string. I'm open to the idea of approaching the whole problem differently. How do most people solve this?
Just to clarify, the production database is 10s of GBs and ideally I'd like it to be practical to regenerate the test one in a matter of a few minutes if we need to do it manually, so I'd rather avoid a full backup and restore. My script that uses CloneDB currently runs in about 2-3min and produces a test environment with plenty of data for us to use and is considerably faster than a backup and restore, plus it doesn't use up resources as much.
Asked by wizzardmr42
(460 rep)
May 12, 2022, 12:39 PM