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

  • 4
    "I come to ask you for advice and best practices" Upgrade your OS and version of SQL Server to ones that are supported.
    – kevinskio
    Jan 29, 2020 at 17:46
  • First, upgrade to supported version. Then you will be able to use tools like Red Gate SQL Clone, which is designed for scenarios like this. With it you will create one copy of the database and differential logs for each environment using this copy. Jan 29, 2020 at 19:13
  • Kevinsky, yes I know but no budget... Piotr, OMG that's what I need ! I love you <3 Jan 30, 2020 at 7:38

3 Answers 3


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)


  • 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.

  • Humm okay it's a nice idea but what happen to changes made in test or dev ? :) Jan 30, 2020 at 12:34
  • It really depends on how you set it up and what you change. If you change the definition of a table, you need to remove it from replication before and after that you need to see your change all the way to production and re-include the table in replication so the change will propagate back with fresh data. So if you often change definition of tables maybe another solution would be more appropriate. But if you only work on stored procedures, functions, views etc, you can replicate the tables and nothing but the tables and then you are fine.
    – LadyBug1
    Feb 4, 2020 at 13:45
  • By the way, most people setup Replication as One-Way so that data changes only propagate in one direction from the source database (Publisher) to the destination database (Subscriber). So if this solution works for you, you don't have to be concerned about accidental data changes affecting production.
    – J.D.
    Nov 24, 2020 at 17:31

Consider tools like:

A. Red Gate SQL Clone, which is designed for scenarios like this.
It will use following components:
1. You will restore single backup of the production database within the tool.
2. Tool will create virtual files for databases in all your environments and create a differential logs for each of them.
3. You will create separate database for each environment using virtual files. At the beginning storage will be used only for single backup. As you modify copies, each environment will keep differential log.

B. Use features of your storage array or VM environment.
You can create single volume on the SAN array and then create multiple copies with differential logs. About details you need to talk with your SAN vendor.

  • In the past I've also found SQL Examiner Suite useful for this kind of scenario too: sqlaccessories.com
    – J.D.
    Nov 24, 2020 at 17:33

If data from the previous day or from the early morning would do, then a BACKUP/RESTORE cycle is your best bet. Basically, you back up the Production DBs, then restore those to the locations that you need the updated data in.

Note that doing this to certain environments every day will break your processes. Restoring daily to Dev, for example, will overwrite any new tables / alterations / performance tweaks that you may be making in the DB (NOTE: I do not know if SAGE x3 allows user changes to DB structures, or adding additional tables, views, or indexes for performance / reporting purposes.)

Any data set up for testing would also be overwritten by the restored databases.

Finally, you may have to re-link or re-add some users to the databases after restoring, depending on your configuration on the Dev / QA systems. There are scripts available on the net for this purpose.

While I agree that updating your SQL Server to a later, supported version is important, this may depend on what version of Sage x3 you are running - for example, v6.3 does not seem to be validated to anything later than SQL Server 2012 - and your ability to upgrade may be constrained by actual budgetary considerations. Since this is true, you should fully specify your environment (SAGE x3 Version, Windows version, etc.) and specify if you are in a position to upgrade SAGE, SQL Server, both, or neither.

  • Hey, thanks for your answer and sorry for the lack of details. We actually run Sage x3 v6.5 on a Windows Server 2008 R2 with Microsoft SQL 2008 R2 too (I've updated my original post) We actually are in this position : "we do not upgrade anything because it's validated by Sage and qualified" but But we can't stay in 2008 forever so I'm doing my best to make it move... I'll probably opt for a weekly restore by backuping and restoring the full database, do you have complementary tips or ideas now that I gave you my system details ? :) Jan 30, 2020 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.