We currently have 3 Windows Server 2008 R2 VM identically build for Sage x3 v6.5, one for production/formation/budget database, a second one for QA (qualification) and a third one for dev & tests. This makes a total of 6 databases, Microsoft SQL Server 2008 R2 with standard licences. We would like to improve our process in order to always have fresh production data in dev, test and qualification (and potentially review, at the same time, the budget and training databases locations, which are also copies of the production scheme).
As I'm not a database specialist (currently an infrastructure engineer apprentice), let alone Microsoft SQL Server... I come to ask you for advice and best practices. Our infrastructure being rather modern, robust and spacious, we don't have any storage capacity or performance problems but, in the interest of optimization, I would like to avoid, as much as possible, having to duplicate 6 times the production VM (about 800 Go) or the production database as it is more or less the case actually...
So is there a way to use the production database from another environment but to redirect the writings of those environments to another location, specific to each environment (another shema or somewhere else...) to keep production database clean as it should be, exactly as a VM snapshot does physically (redirect on write) ? If not, I think I will have to copy the database 6 times (don't hesitate to tell me if I'm wrong)... How could I proceed in SQL 2008 R2 because it's hard to find how to copy tables (and their data) from a specific schema to another database schema on another server and to automate it. And, afterwards, is it possible to do differential copy (each night for example) to reduce the process duration.
Thanks a lot for all your answers :)
Sincerely yours, Tigerblue77