Hello,

[SQL2000 latest sp]

We have a daily database restore that refreshes ServerB with the latest
backup from ServerA. This refresh happens at night so that the next morning
users have the "latest" data on ServerB. Quite often, this restore doesnt
run, like someone accidentaly disabling the refresh job, or serverB being
shuwdown and thus missing the job schedule etc. In those cases, we come to
know about this only when the users login the next day and report data not
being "accurate". Then it would mean 2-3 hour downtime for their app as we
go ahead and start the refresh during "production" hours.

I am thinking of a way to tackle this. My plan is to create and run a
monitor job in ServerB that will query the database and find out the last
date that it was refeshed. Based on the output, it will alert us so that we
can be proactive. Is this a feasible solution? If so, how can I find the
time when the database was last restored?


tia,
AbbA

If your using SQL BACKUP/RESTORE, the logs will be in the msdb database as
well as the SQL Server logs. I'd have to check exactly where, but you can do
that too; besides, I don't have 2000 installed anymore

Still, your suggested solution bothers me and your two reasons for failure
bother me even more.

How on earth does a job get accidentally disabled?
Why isn't the person the person taking a machine down making sure the jobs
are run when their done?

Sounds like you have a bigger people problem, than a programming/admin
problem.


"Abba" <sql_help (AT) aaa (DOT) com> wrote

I am aware that the info would be in msdb. Just couldn't find the exact
table, hence the query.

Regarding the other queries, sorry I do not have an answer(though I wish I
knew). All I can say that these things happen as long as humans are running
the computers . If none of these would have happened as in an ideal world,
then all those companies making Monitoring software would have been out of
business!

"Jay" <spam (AT) nospam (DOT) org> wrote

There aren't that many tables in msdb, just manually check them all. It
should only take about 10 minutes.

Once you find the right table, check the docs in BOL.

"Abba" <sql_help (AT) aaa (DOT) com> wrote

Please ignore. Found it in:
http://www.mssqltips.com/tip.asp?tip=1724

"Abba" <sql_help (AT) aaa (DOT) com> wrote

[msdb].[dbo].[restorehistory]

"Abba" <sql_help (AT) aaa (DOT) com> wrote

I'm glad you found it, but I'm curious why you aren't using replication
or log shipping, neither of which requires the application to be shut
down.

Abba wrote:

--
HTH,
Bob Barrows

Your solution to base something on serverB is useless if serverB is shutdown
or THAT job disabled, right? :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Abba" <sql_help (AT) aaa (DOT) com> wrote

That is why I tried to stress he had a people problem, not a programming
problem.

However, if he does something with the startup script functionality, he can
get around the server being down when the job wads supposed to run.


"TheSQLGuru" <kgboles (AT) earthlink (DOT) net> wrote

The App owners do not want updated data. Only the data from the previous day
..

"Bob Barrows" <reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote