SQL Server Performance Forum – Threads Archive

How to backup and restore related databases?

If an application will update tables in different databases in a transaction, how to backup the databases? For example, if the transaction is something like this:
Begin Transaction
update tables in 1st database
update tables in 2nd database
Commit Transaction Since backup can be done anytime, what happen if the backup for the 1st database occurs before the commit transaction and the backup for the 2nd database occurs after the commit transaction? Thanks.

The 1st database will not write the updated transactions until the next execution of backup. Just take care of schedule of transaction log backup every 15 mins or 10 mins to avoid such
doubts. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya, If the server dies right after the backup of the 2nd database is done, restoring the backup of the 2 databases in anothe server will cause data integrity issue since the backup of the 1st database does not contain the changes of the transaction which is in the backup of the 2nd database. Does SQL Server have anyway to prevent this? If not, is this a design problem of the application? I just wonder how application handle such scenario (updating of multiple databases in a transaction). Updates: I guess there may be a way to handle this. There is a topic in BOL: Backup and Recovery of Related Databases.
Thanks.

]]>