"Peter" <Pe***@discussions.microsoft.com> wrote in message
news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
> 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.

"Peter" <Pe***@discussions.microsoft.com> wrote in message
news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
> 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.

"Anthony Thomas" wrote:

> Moreover, since we are talking about a distributed transaction here, each
> respective transaction log, and there associated transaction log backups,
> will also have the dual commits, or not, thus, rolling forward or rolling
> back as necessary.
>
> Sincerely,
>
>
> Anthony Thomas
>
>
> --
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:uLjkeeOMFHA.3228@TK2MSFTNGP12.phx.gbl...
> > 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?
>
> If you restore from these backups, you would find that uncommitted changes
> do not exist in the 1st database.  The 2nd database will contain the
> committed changes.
>
> I assume the reason for your question is that you would like to have the
> transaction either committed or rolled back in the event of a restore.  This
> a bit tricky because databases are independently backed up and restored.  If
> data are closely related, these should probably be in the same physical
> database.  If you must use multiple databases for some reason, it's probably
> best to schedule backups during a maintenance window so that you won't get
> out-of-sync in the event of a restore.  Of course, the databases must always
> be restored together.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Peter" <Pe***@discussions.microsoft.com> wrote in message
> news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
> > 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.
>
>
>

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:uLjkeeOMFHA.3228@TK2MSFTNGP12.phx.gbl...
>> 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?
>
> If you restore from these backups, you would find that uncommitted changes do not exist in the 1st
> database.  The 2nd database will contain the committed changes.
>
> I assume the reason for your question is that you would like to have the transaction either
> committed or rolled back in the event of a restore.  This a bit tricky because databases are
> independently backed up and restored.  If data are closely related, these should probably be in
> the same physical database.  If you must use multiple databases for some reason, it's probably
> best to schedule backups during a maintenance window so that you won't get out-of-sync in the
> event of a restore.  Of course, the databases must always be restored together.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Peter" <Pe***@discussions.microsoft.com> wrote in message
> news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
>> 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.
>
>

"Tibor Karaszi" wrote:

> On option is to set a log marker for the backup. BEGIN TRAN ... WITH MARK. There are some sections
> about this in Books Online. But I agree with the others that if the data is related, it belongs in
> the same database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:uLjkeeOMFHA.3228@TK2MSFTNGP12.phx.gbl...
> >> 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?
> >
> > If you restore from these backups, you would find that uncommitted changes do not exist in the 1st
> > database.  The 2nd database will contain the committed changes.
> >
> > I assume the reason for your question is that you would like to have the transaction either
> > committed or rolled back in the event of a restore.  This a bit tricky because databases are
> > independently backed up and restored.  If data are closely related, these should probably be in
> > the same physical database.  If you must use multiple databases for some reason, it's probably
> > best to schedule backups during a maintenance window so that you won't get out-of-sync in the
> > event of a restore.  Of course, the databases must always be restored together.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Peter" <Pe***@discussions.microsoft.com> wrote in message
> > news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
> >> 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.
> >
> >
>
>
>

"Peter" <Pe***@discussions.microsoft.com> wrote in message
news:25D506C0-A593-4020-B015-748686AB0817@microsoft.com...
> Hi Tibor,
>
> Thanks for the information.  I believe you're referring to the section
> Backup and Recovery of Related Databases.
>
> I'm thinking about using one connection for each database so I don't need to
> switch database or qualify table with database name.   My understanding is
> that if I have different connection, a Begin Transaction and Commit
> Transaction will only work on the current connection.  To workaround this
> limitation, I'm thinking about using bound connection.  Will this work?
>
> Thanks.
>
>
> Peter
>
> "Tibor Karaszi" wrote:
>
>> On option is to set a log marker for the backup. BEGIN TRAN ... WITH MARK. There are some
>> sections
>> about this in Books Online. But I agree with the others that if the data is related, it belongs
>> in
>> the same database.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>>
>> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
>> news:uLjkeeOMFHA.3228@TK2MSFTNGP12.phx.gbl...
>> >> 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?
>> >
>> > If you restore from these backups, you would find that uncommitted changes do not exist in the
>> > 1st
>> > database.  The 2nd database will contain the committed changes.
>> >
>> > I assume the reason for your question is that you would like to have the transaction either
>> > committed or rolled back in the event of a restore.  This a bit tricky because databases are
>> > independently backed up and restored.  If data are closely related, these should probably be in
>> > the same physical database.  If you must use multiple databases for some reason, it's probably
>> > best to schedule backups during a maintenance window so that you won't get out-of-sync in the
>> > event of a restore.  Of course, the databases must always be restored together.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "Peter" <Pe***@discussions.microsoft.com> wrote in message
>> > news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
>> >> 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.
>> >
>> >
>>
>>
>>

"Tibor Karaszi" wrote:

> I haven't tried bound connections for this. Give it a spin and see. I'd still advice against having
> several databases when data is related.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
>
> "Peter" <Pe***@discussions.microsoft.com> wrote in message
> news:25D506C0-A593-4020-B015-748686AB0817@microsoft.com...
> > Hi Tibor,
> >
> > Thanks for the information.  I believe you're referring to the section
> > Backup and Recovery of Related Databases.
> >
> > I'm thinking about using one connection for each database so I don't need to
> > switch database or qualify table with database name.   My understanding is
> > that if I have different connection, a Begin Transaction and Commit
> > Transaction will only work on the current connection.  To workaround this
> > limitation, I'm thinking about using bound connection.  Will this work?
> >
> > Thanks.
> >
> >
> > Peter
> >
> > "Tibor Karaszi" wrote:
> >
> >> On option is to set a log marker for the backup. BEGIN TRAN ... WITH MARK. There are some
> >> sections
> >> about this in Books Online. But I agree with the others that if the data is related, it belongs
> >> in
> >> the same database.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> >> news:uLjkeeOMFHA.3228@TK2MSFTNGP12.phx.gbl...
> >> >> 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?
> >> >
> >> > If you restore from these backups, you would find that uncommitted changes do not exist in the
> >> > 1st
> >> > database.  The 2nd database will contain the committed changes.
> >> >
> >> > I assume the reason for your question is that you would like to have the transaction either
> >> > committed or rolled back in the event of a restore.  This a bit tricky because databases are
> >> > independently backed up and restored.  If data are closely related, these should probably be in
> >> > the same physical database.  If you must use multiple databases for some reason, it's probably
> >> > best to schedule backups during a maintenance window so that you won't get out-of-sync in the
> >> > event of a restore.  Of course, the databases must always be restored together.
> >> >
> >> > --
> >> > Hope this helps.
> >> >
> >> > Dan Guzman
> >> > SQL Server MVP
> >> >
> >> > "Peter" <Pe***@discussions.microsoft.com> wrote in message
> >> > news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
> >> >> 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.
> >> >
> >> >
> >>
> >>
> >>
>
>
>

"Dan Guzman" wrote:

> > 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?
>
> If you restore from these backups, you would find that uncommitted changes
> do not exist in the 1st database.  The 2nd database will contain the
> committed changes.
>
> I assume the reason for your question is that you would like to have the
> transaction either committed or rolled back in the event of a restore.  This
> a bit tricky because databases are independently backed up and restored.  If
> data are closely related, these should probably be in the same physical
> database.  If you must use multiple databases for some reason, it's probably
> best to schedule backups during a maintenance window so that you won't get
> out-of-sync in the event of a restore.  Of course, the databases must always
> be restored together.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Peter" <Pe***@discussions.microsoft.com> wrote in message
> news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
> > 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.
>
>
>

"Peter" <Pe***@discussions.microsoft.com> wrote in message
news:A811CB79-CC68-4570-8CE5-548C6CA77B7C@microsoft.com...
> Hi Dan,
>
> I'm currently evalutating whether to split a database into 2 databases and
> has connection for each one.  I'm trying to think about what issues may
> rise
> due to the split.  So far, I can only think of 2 issues:
>
> 1.  how to commit transaction which update both database
> 2.  how to backup and restore both database
>
> I wonder whether there is any performance issue.
>
> Thanks,
>
> Peter
>
>
>
> "Dan Guzman" wrote:
>
>> > 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?
>>
>> If you restore from these backups, you would find that uncommitted
>> changes
>> do not exist in the 1st database.  The 2nd database will contain the
>> committed changes.
>>
>> I assume the reason for your question is that you would like to have the
>> transaction either committed or rolled back in the event of a restore.
>> This
>> a bit tricky because databases are independently backed up and restored.
>> If
>> data are closely related, these should probably be in the same physical
>> database.  If you must use multiple databases for some reason, it's
>> probably
>> best to schedule backups during a maintenance window so that you won't
>> get
>> out-of-sync in the event of a restore.  Of course, the databases must
>> always
>> be restored together.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Peter" <Pe***@discussions.microsoft.com> wrote in message
>> news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
>> > 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.
>>
>>
>>

"Dan Guzman" wrote:

> If you use separate connections, each transaction is independent so you
> could run into data integrity problems if one update succeeds and the other
> fails.  As Tibor mentioned, you'll need to use bound connections so that the
> connections can share the same transaction context.  It doesn't seem to me
> that this will provide any value other than allowing you to omit the
> database qualifier in your queries.
>
> The main issue with the 2-database approach is backup/recovery.  If a
> restore us required, you'll also need to restore both databases and apply
> transaction log backups with the same STOPAT or STOPATMARK specification.
>
> If you currently have only one database, why are you considering a second
> database?
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Peter" <Pe***@discussions.microsoft.com> wrote in message
> news:A811CB79-CC68-4570-8CE5-548C6CA77B7C@microsoft.com...
> > Hi Dan,
> >
> > I'm currently evalutating whether to split a database into 2 databases and
> > has connection for each one.  I'm trying to think about what issues may
> > rise
> > due to the split.  So far, I can only think of 2 issues:
> >
> > 1.  how to commit transaction which update both database
> > 2.  how to backup and restore both database
> >
> > I wonder whether there is any performance issue.
> >
> > Thanks,
> >
> > Peter
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > 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?
> >>
> >> If you restore from these backups, you would find that uncommitted
> >> changes
> >> do not exist in the 1st database.  The 2nd database will contain the
> >> committed changes.
> >>
> >> I assume the reason for your question is that you would like to have the
> >> transaction either committed or rolled back in the event of a restore.
> >> This
> >> a bit tricky because databases are independently backed up and restored.
> >> If
> >> data are closely related, these should probably be in the same physical
> >> database.  If you must use multiple databases for some reason, it's
> >> probably
> >> best to schedule backups during a maintenance window so that you won't
> >> get
> >> out-of-sync in the event of a restore.  Of course, the databases must
> >> always
> >> be restored together.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Peter" <Pe***@discussions.microsoft.com> wrote in message
> >> news:F4434522-EC6B-4976-9B1A-2F1D4F26F292@microsoft.com...
> >> > 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.
> >>
> >>
> >>
>
>
>