Working on a legacy project that has a SQL 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:
- Create a temporary database where accounts for developers and testers can be stored
- Copy accounts from dev / test to temporary database
- Replace the dev / test database with the production database
- 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 message “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?
1