Restoring two databases to precisely the same time

Question

In SQL Server 2008, I have my parent table in one database, and the child table in another database, with FK relationship maintained by triggers. I cannot change it, cannot move both tables into one DB and have a regular FK constraint. When I restored both databases from full backups, I had orphans in my child table, because the full backups were not taken at the same time. I also have transaction logs. In case of disaster recovery, can I restore both databases to precisely the same moment, so that the two databases are consistent?

Answer 1

I cannot see the full solution for your problem, but you can use full backups with backups of transaction log. first, you restore full backups on poth bases WITH NORECOVERY option, and then resore transaction-log backups WITH STOPAT='xxxxxxxx' on both bases. So you can get both databases restored on same point of time.

Answer 2

Restoring at the same moment in time is possible as long as the databases are in full recovery mode and regular log backups are taken. See How to: Restore to a Point in Time (Transact-SQL).

However point in time recovery will not ensure cross-db transactional consistency on their own, you also need to had been used transactions on all operations that logically spanned the database boundary. Triggers have probably ensured this for deletes and updates because they run in the context of the parent operation, thus implicitly wrapping the cross db boundary operation in a transaction, but for inserts your application usually has to wrap the insert into parent and insert into child into a single transaction.

Consistency of recovery operations is the biggest hurdle with application split between different databases.

Answer 3

The best way to do this is to fix it at the point you're doing the backup. Most multi-database apps do this:

Prior to backup, execute a command to write a marked transaction in the transaction log of each database involved. (BEGIN TRANSACTION WITH MARK) Then do the backups.

That way, you can later do a RESTORE WITH STOPAT MARK to get them all to the same point in time. It's not perfect but much closer than other methods.