Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a log shipping setup where we replicate from our primary database to a secondary. Now that database is, by the way of log shipping, in standby and thus read-only. We set it up so we could run reports from it, but it seems the reporting software used needs to go through the application server which needs to log onto the server - in this case, Microsoft Dynamics AX and Microsoft Dynamics ERP.

Our plan is to have a teritary database - this would also get log backups via log shipping, but in a different way - we would like to roll the database back to what it was at the last transaction log restore, then restore the latest log file(s). Is this possible, and how would we go about it?

EDIT 2013/07/21:

Allow me to rephrase my question. If I have a SQL database with full transaction logging, can I tell it to roll back all changes to a certain point, and if so, how may I accomplish this?

Thanks

share|improve this question
    
I'm not familiar with MS Dynamics but this article seems promising: community.dynamics.com/ax/b/brandongeorge/archive/2011/12/30/… Why do you need the tertiary database? I'm confused by "roll the database back to what it was at the last transation log restore, then restore the latest log files(s)". –  Tanner Corbin Jul 5 '13 at 15:36
    
@TannerCorbin Please don't post comments as answers, even if you don't have the reputation to post a comment yet. –  JNK Jul 5 '13 at 18:19
add comment

1 Answer

up vote 1 down vote accepted

Seems like you are misunderstanding what you have in-place and what you want to get out of it.

We have a log shipping setup where we replicate from our primary database to a secondary. Now that database is, by the way of log shipping, in standby and thus read-only. We set it up so we could run reports from it, but it seems the reporting software used needs to go through the application server which needs to log onto the server - in this case, Microsoft Dynamics AX and Microsoft Dynamics ERP.

Log-shipping is not meant for this purpose. Its a warm / stand-by database that can be brought online by restoring the log-backups and the tail backup of the primary server (if possible) with minimum data loss.

When you want to run reports against a database, I would suggest you to go for Transactional Replication (if reports are needed for near to real-time data) or database snapshot (if data can be a bit stale and you are using enterprise edition).

Replication latency will depend on the amount of data you are replicating, the n/w bandwidth, number of articles and the amount of activity/transactions on the publisher database as well.

Our plan is to have a teritary database - this would also get log backups via log shipping, but in a different way - we would like to roll the database back to what it was at the last transaction log restore, then restore the latest log file(s). Is this possible, and how would we go about it?

In simple terms, when you set up log-shipping - what ever happens on Primary database server, gets backed up, copied and restored to secondary database server depending on the log-backup, copy and restore job settings (minimum is 1 min). This is how log-shipping works.

for e.g.

8:00 A.M. ==> Back up database to create a full database backup.
Noon      ==> Back up transaction log.
4:00 P.M. ==> Back up transaction log.
6:00 P.M. ==> Back up database to create a full database backup.
8:00 P.M. ==> Back up transaction log.
9:45 P.M. ==> Failure occurs.

Recovery can be done by 2 methods :

method 1 : Restore the database by using the most recent full database backup

  1. Create a tail-log backup of the currently active transaction log as of the point of failure.
  2. Do not restore the 8:00 A.M. full database backup. Instead, restore the more recent 6:00 P.M. full database backup, and then apply the 8:00 P.M. log backup and the tail-log backup.

Method 2 : Restore the database by using an earlier full database backup

  1. Create a tail-log backup of the currently active transaction log as of the point of failure.
  2. Restore the 8:00 A.M. full database backup, and then restore all four transaction log backups in sequence. This rolls forward all completed transactions up to 9:45 P.M.

Alternatively, you can use the STOPAT to restore to a point in time.

Allow me to rephrase my question. If I have a SQL database with full transaction logging, can I tell it to roll back all changes to a certain point, and if so, how may I accomplish this?

So, there is no way to ROLL BACK a transaction using T-log natively (unless you use third party software like ApexSQL Log and log rescue - only supported sql 2000 ). As mentioned above, use STOPAT to restore to a point in time.

share|improve this answer
    
Thanks, much appreciated. We decided in the end to just update the secondary database when the need arose. We actually tried replication but it almost broke the production AX instance, which makes changes to the DDL periodically. –  Aaron Mason Aug 1 '13 at 23:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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