Backup Coupled Databases Consistently

richie_erichie_e Posts: 9
edited January 15, 2008 5:34AM in SQL Backup Previous Versions
Hi all

Our production system consists of two SQL 2005 databases A and B on different servers. Server A is running SQL Backup 4.
Server B is running SQL Backup 5.

Server A has a set of Tables which are replicated to B with Transactional Replication.

Server B has a set of Tables, Server A Has Views of the same name which look at these tables. All other tables on B are subscriptions to A's Publication.

What I want to ensure is that the Full backup of these two databases is taken at precisely the same point in time. This will make restoring the db to another environment easier than doing the restores and then doing a WITH STOPAT Log Restore and trying to match them up based on DateTime.

Anyone know a good solution to this ?
(Would prefer not to put the dbs in SINGLE USER MODE)


Many thanks
Rich
· Share on Twitter

Comments

  • Eddie DEddie D Posts: 1,807 Rose Gold 5
    Thank you for your post into the forum.

    I am little confused by your post.

    Can this be achieved by simply having the system clocks synchronised on each machine to the same time source.

    Next create a scheduled backup job on both machines to trigger or start at exactly the same time.

    Or I am missing something that needs further explanation.

    Many Thanks
    Eddie

    Eddie Davis
    Technical Support Engineer
    Red Gate Software Ltd
    E-Mail: support@red-gate.com
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    When you create a backup, the backup file contains the state of the database as at the end of the backup, not at the time the backup was started. Thus, depending on database size, disk I/O speed, CPU activity etc, two databases that are backed up at the same time may still contain 'inconsistent' data in your scenario. And that will be due to transactions that are committed during the backup. The aim then is to prevent transactions from being committed during this time.

    Setting both databases to single user mode during the backup is one option, which you obviously want to avoid.

    If stopping user activity is not feasible, restoring transaction logs to a specified point in time is another option. Transaction log restores in SQL Backup aren't as tedious as normal SQL Server restores.

    E.g. you have 10 transaction log backups that you want to restore, and the 10th backup contains the point in time which you want to restore to. You can just place these files in a unique folder and run the RESTORE command e.g.
    EXEC master..sqlbackup '-sql "RESTORE LOG mydb FROM DISK = [G:\Staging\Logs\*.sqb] WITH STOPAT = [15-Jan-2008 10:10], RECOVERY" '

    Note that you did not need to specify each file individually, and neither did you need to order the files yourself. SQL Backup handles all those details for you.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • richie_erichie_e Posts: 9
    Pete,

    You answer is just what I was after. The backups are finishing at different times as one of the databases is significantly larger than the other.

    I will bear this in mind when doing my restores.

    Many Thanks

    Richard
    · Share on Twitter
Sign In or Register to comment.