MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another 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 &amp; 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
Consider also replication. You can replicate only your tables so that you don't override any stored procedures or functions that the developers are working on. You can set up a publisher on production and 2 subscribers: one on TST and one on DEV. 

It's almost real time and very suitable for scenarios like yours. I always try to go for replication instead of anything else because in a stable environment you set it up and forget it's there while saving network bandwidth and unnecessary data copy. (with backup and restore you basically [copy prod], [delete tst/dev], [paste prod in dev/tst] - 90% the same data everyday) 

Downsides: 


it's a bit of trouble until you learn to work with it
it will take some network bandwidth during the day to send your
transactions from the publisher (prod in your case) to the subscriber
(or subscribers in your case: dev and tst).
it will eat up a lot of network bandwidth, SQL Server memory and disk I/O when you initially set it up and this will repeat if and every time you need to reinitialize the replication. You can also set up replication from backup and that goes faster when you initially set it up and if you ever reinitialize.


More documentation about replication here.