July 25, 2018 at 6:37 am
I'm trying to figure out a way to update our test databases from the production environment. Backups and restores and transactional replication are not an option in the environment I'm working in. Due to workplace policies I'm not allowed to do a full data dump. However, I can get the last 'N' (1000 - 5000) records from production so we can have some recent data to test out the applications being devleoped. I was thinking about creating a SSIS package to handle the transfer, but several of the databases have hundreds of tables. Not all of them are transactional, but many are. I'm wondering if there is a way to transfer the necessary data without having to create a data flow task for each table. Any ideas would be appreciated. - Thanks
July 25, 2018 at 7:17 am
A backup and a restore followed by data cleansing would be a lot easier.
Otherwise, if you're moving subsets of data, especially since it's going to be relational data, the only way to do it efficiently that I know of, is by table. Now, you could get a little fancy with it, if you know for certain you've got the data flawlessly exported. You can disable/drop all constraints, move the data into the tables in no particular order, then enable/recreate the constraints. As long as the data is flawless, no missing rows causing failures in the constraint checks, you'll have the data in place. However, even here you're going to have to spend a lot of time ensuring that the export is done perfectly, requiring lookups across tables, etc.
Don't forget to be sure to take into account privacy and protection issues as well. Do you have to meet GDPR, HIPAA, SOX, PCI compliance? Any, or all, of these will affect your processing as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 25, 2018 at 7:36 am
Thanks for the suggestion.
July 25, 2018 at 9:05 am
you also need to take in consideration licensing - if your dev boxes are using a SQL Server Developer license (being it a MSDN one or not) they can not access a production environment - only way to get the data is to have a prod server push down the data to the dev server.
And as Grant mentioned you do need to worry about all the other stuff - what many shops are doing now is to do a intermediary step on a prod server or "preparing" the data to be used by non prod environments - mask names, remove sensitive data and so on. And the result of this process is what can be copied down to non prod.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply