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

Keeping 2 DBs in synch

Hi all, here are my goals: Have the same DB on two different stand-alone computers, and keep them up-to-date from each other.

Basically a user would input to a DB for a week. Then every week or two, update the other stand alone DB with the new input. The DB would be exactly the same.

What are my options for this? I'd like it as easy as possible! Are there any software packages that deal with this type of transfer, etc.? Thank you!Replication but this can get complicated depending on your scenario.

Is there any data entry going on with the second server or is it just standby or back up?

If the data entry is only happening on one server you can set up a simple snapshot replication scheme or you can push the data over using data transformation services and there is a somewhat flexible wizard for this.

Again, you are only doing data entry at one end you can always run a backup from one DB and restore to another.

If however you are doing data entry on both ends, merge replication is the way to go.

And finally in the SQL Server 2005 beta there is something called database mirroring which keeps two SQL Servers in sync by taking a snapshot and applying the trans log from one to the other and the copy monitors the production server and if it goes down your mirrored server picks right up where the production server left off.

many options. much to ponder.|||Thank you for your insight.

Yes, only one DB would be used for entry, the other for 'backup' and viewing. Maybe what would be best, is what would be easiest for a advanced end user to operate? (As in, running/backing and transferring the data atleast to us)

... or if I could automate some of these tasks in vb.net 03 ?...|||Replication you can make run automatically and forever by setting the subscription to never expire. No one would have to do anything until something changed.

Data Tansformation Services you can also schedule as a job to run automatically and this may be easier to set up and trouble shoot than replication.

Don't do the backup and restore. Requires certain rights an end user should'nt have and I would let an end user do any of this stuff.

If you do not want to make it automatic I have some VB6 code that fires off DTS without using the command shell. Sorry I do not have the .Net for it. STill stuck in the stone ages.|||An automated backup and restore is probably easiest to implement and manage, considering your limited requirements.|||Thanks again for the information.

Are there some good examples, guides, etc. to get me started on an automated backup and restore method? I'm not sure where to begin!!!