Implementing a test system with SQL Server log files

Software testing
SQL Server database
SQL Server logs
We need to implement a test system where the entries created in various tables on a test server need to be automatically re-generated on a live server so that there are no manual errors. We think the only way to do this is to use SQL Server log files. However, we are not sure that the tools available, which are seemingly oriented towards database backup/recovery operations, are what we would need. Are there tools available that would work towards what we would like to do?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can’t take the log files from one server, and run them against another server. SQL Server won’t let you do this.

If you need to make data changes like this between environment you need to script out the changes in your development environment (yes it’s a long process to type out all the INSERT and UPDATE statements), and then run these scripts in Development, QA and then Production so that the change is done the same way in all three environments.


What I understand is that in your test system you are entering some data in various tables that is valid for your live server also, this could be some initial setups/configurations, parameter settings etc. Simply use the complete backup and restore utility for the purpose for your live server.

Actually, you can take a TLog and apply the relevant transactions you care about to another DB assuming the schema is identical. SQL Server does not provide such a tool natively but there are a few 3rd party ISVs that do. Log readers are not new and most major RDBMS vendors already have them. SQL Server probably will, someday.

As for moving data automatically from the test system to production, that actually sounds like a pre-prod system rather than test. I really would not approach it the way the OP described but if you really want to do this, you have a few options:

  1. Replication with a time delay.
  2. Triggers, if load is low/moderate and not many tables
  3. Script the SELECT INTO or some other commands into a SQL Agent job

There are more but these are fairly common and not real difficult to implement. However, I do recommend you review your process and whether you are taking the right approach on this. Having a verification step before committing to a production database is a good thing to do but how you go about it is equally important. Without real details, I’m not convinced running 2 databases is the best approach if it is an on-going, online activity.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    69,005 pointsBadges:
  • Jaideep Khanduja
    I suggest you not making it too complex of trying a new tool for this as this is going to effect your live database. Be careful and go for a well tested solution of backup utility of database enterprise manager rather than trying for a third party tool with less expertise.
    19,345 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: