Working on a legacy project that has a SQL Server database with 50+ tables and 100+ stored procedures. Once every quarter, we manually replace the development and test databases with a database from production. The only problem is that the developers and testers do not have an account on production, hence their accounts need to be manually recreated once the dev / test database has been replaced.

Manually refreshing / replacing a database is tedious work, thus I’m trying to automate the process. This automated process will contain the following steps:

1. Create a temporary database where accounts for developers and testers can be stored

2. Copy accounts from dev / test to temporary database

3. Replace the dev / test database with the production database

4. Recreate the accounts for the developers and testers.

I’ve thought about creating a permanent database on the dev and test environments that will contain the relevant tables, or a SQL script that will create such a temporary database with only the relevant tables, but the relevant tables are sometimes altered. Therefore I want to instead generate the temp database and tables during every execution of the pipeline.

So far I’ve tried sqlpackage.exe, but I run into some issues:

• sqlpackage /a:extract: I added the option /p:tabledata=... to specify the ~10 tables that I need, but sqlpackage fails with several

  Error SQL71564: “Foreign key ….” messages that refer to tables and stored procedures that need not be backed up.

• sqlpackage /a:export: Our database makes use of Windows authentication, and I get an error

  Error SQL71627 The element … has property AuthenticationType set to a value that is not supported”. Removing these users is not an options.

What would you recommend for creating a new database with a subset of the tables based on a source table?