Keeping SQL 2000 databases in synch after restore.

We have 4 different databases, running on sql server 2000. I scheduled
the database to be backed up at 5:00, and every hour for the trans
log.
If we have a hardware failure and have to restore from backup. how do
I make sure that the database are all starting at the same time. (if
database common takes 2 min to back up and database customer takes 30
min to back up) how do I make sure these 2 database are at the same
point in time.
Is there anything I can do in my sql backup job to have the backup's
end at the same time?
Currently all of the backup are different jobs.
Nicholas.GadaczSo you're saying there are transactional integrity reasons why the DB's all
need to be restored to the exact same point in time?
RESTORE LOG allows you to specify STOPAT for point in time recovery, but
RESTORE DATABASE doesn't.
I can't think of a clean way to do that for a full database without using
tran log restores...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"First Tracks Research" <info@.ftresearch.com> wrote in message
news:4d02a84e.0401061017.1db0b24d@.posting.google.com...
> We have 4 different databases, running on sql server 2000. I scheduled
> the database to be backed up at 5:00, and every hour for the trans
> log.
> If we have a hardware failure and have to restore from backup. how do
> I make sure that the database are all starting at the same time. (if
> database common takes 2 min to back up and database customer takes 30
> min to back up) how do I make sure these 2 database are at the same
> point in time.
> Is there anything I can do in my sql backup job to have the backup's
> end at the same time?
> Currently all of the backup are different jobs.
> Nicholas.Gadacz|||I really don't mean to be a smart ass here. I realize it may not be the
answer you are looking for. But this is kind of a design issue. If there
are transactional integrity issues, the easiest and cleanest way is to put
those objects in the same database. You may have name conflicts, but those
will be much easier to resolve now than the issues you have started to run
into now. What if tomorrow you want to send a snapshot replication of five
related tables? You will have the same issue.
If you do take my advice and put your databases into one, a good way to
avoid naming conflicts in your code, is to script one of the databases in
its entirety, do a search and replace and change all occurences of
conflicting names at the object definition as well as the references in code
simultaneously. Then you can put the two together without conflict. Again,
if you decide to do that, I can be made available for assistance remotely.
Pls remove NO_SPAM(s) from my email if you need to reach me.
--
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"First Tracks Research" <info@.ftresearch.com> wrote in message
news:4d02a84e.0401061017.1db0b24d@.posting.google.com...
> We have 4 different databases, running on sql server 2000. I scheduled
> the database to be backed up at 5:00, and every hour for the trans
> log.
> If we have a hardware failure and have to restore from backup. how do
> I make sure that the database are all starting at the same time. (if
> database common takes 2 min to back up and database customer takes 30
> min to back up) how do I make sure these 2 database are at the same
> point in time.
> Is there anything I can do in my sql backup job to have the backup's
> end at the same time?
> Currently all of the backup are different jobs.
> Nicholas.Gadacz

KB892923....questions.

We do weekly fulls, daily differentials, and hourly trans logs on all user
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/defaul...kb;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.

KB892923....questions.

We do weekly fulls, daily differentials, and hourly trans logs on all user
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/default.aspx?scid=kb;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.

KB892923....questions.

We do weekly fulls, daily differentials, and hourly trans logs on all user
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/default...b;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?
> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.