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

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.