backup strategy

[Vojislav Depalov]

Hi to all,
we have two databases, where is second database historical database and
data from first database are inserted in second database on every change
in primary database using triggers.
How sholud I make backup strategy in order to maintain data consistency
between those two databases?
I.e. I can make backup of 1 database, but as soon as I make backup of
second database, some data will be in second database dump and won't be
in first database dump. When I restore those two dumps, I won't get
consistent data!
I've been experimenting with quiesce and I can suspend updates and
inserts, but can't make backup during quiescence time.
Also making copy of database devices during quiesce is ok, but it
requires that database is droped before we want to mount database from
devices. So, solution droping database which is required to be restored
is not good enough
So my question is: what should be backup strategy in this case?
Thank you all,
Vojislav Depalov

[A.M.]

Post by Vojislav Depalov
we have two databases, where is second database historical database and
data from first database are inserted in second database on every change
in primary database using triggers.
How sholud I make backup strategy in order to maintain data consistency
between those two databases?
I.e. I can make backup of 1 database, but as soon as I make backup of
second database, some data will be in second database dump and won't be
in first database dump. When I restore those two dumps, I won't get
consistent data!
I've been experimenting with quiesce and I can suspend updates and
inserts, but can't make backup during quiescence time.
Also making copy of database devices during quiesce is ok, but it
requires that database is droped before we want to mount database from
devices. So, solution droping database which is required to be restored
is not good enough
So my question is: what should be backup strategy in this case?

That's part of the problem with trying to maintain two or
more databases in sync with each other.

Since you can quiesce your databases, it means you can lock
users out for a period of time. So the best thing to do in
this case is to set the databases to dbo use only. This
assumes your users aren't aliased to the dbo, of course.
You could also try read only mode. Dump both databases
together, if you can, to minimise the impact of the lock
out and then reset the dboptions when done.

-am © MMVI

[unknown]

What you need to do is used defined transactions, so that
you insure that nothing is committed in the first database
unless it is also committed in the second database.

Quiesce or putting the database into dbo only mode will only
work if you can guarantee that there was not a transaction
in process at the time that you did the quiesce or mode
change. My guess is that you can't guarantee that.

[Vojislav Depalov]

Thank You for the answer. My presume is that transaction will rollback
if trigger which inserts data into second database fail. Am i right?
Vojislav Depalov

Post by unknown
What you need to do is used defined transactions, so that
you insure that nothing is committed in the first database
unless it is also committed in the second database.
Quiesce or putting the database into dbo only mode will only
work if you can guarantee that there was not a transaction
in process at the time that you did the quiesce or mode
change. My guess is that you can't guarantee that.

[unknown]

Post by Vojislav Depalov
My presume is that transaction
will rollback if trigger which inserts data into second
database fail.

Not automatically. You have to explicitly define your
transactions.