Re: Restore leaves DB in Loading state
From: Serena (Serena_at_discussions.microsoft.com)
Date: 10/04/04
- Next message: Scott Morris: "Re: Error 1603 installing MS SQL Server 2005 Beta 2 Setup Support"
- Previous message: Dooza: "Re: Duplicate Entries Help"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 4 Oct 2004 05:43:01 -0700
Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
"Geoff N. Hiten" wrote:
> Sorry for underestimating your technical skill. In this forum, it is
> usually better that way. :)
>
> I think your problem may have something to do with permissions. Perhaps the
> SQL Agent account controlling the job or the context the job runs under does
> not have permissions to create/modify/update the standby file. That would
> leave SQL restore hanging until it could write to that file. SInce it works
> under other security contexts, that may be a good place to check.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> I support the Professional Association for SQL Server
> www.sqlpass.org
>
> "Terry Wight" <dontsendmecrud@hotmail.com> wrote in message
> news:uogJYISaEHA.3352@TK2MSFTNGP12.phx.gbl...
> > I think you are mistaken. What I "told it to do" is restore the database
> > in standby with no recovery or rollback. This lets me apply transaction
> > logs throughout the day. If I use recovery, I lose incomplete
> > transactions and cannot apply transaction logs.
> >
> > It should show up in SQL EM as "(Read Only)" like the 12 other databases
> > I do this with every day.
> >
> > I know quite a lot about every method of backing up and restoring SQL
> > Server databases and have been doing it for years. What I don't know,
> > and perhaps you can help me with this, is how to diagnose the problem
> > here.
> >
> > When the job on SERVER-A completes and I view the job history. The job
> > reports successfull completion.
> >
> > When I look at the DB on SERVER-B the database is left in the
> > "(Loading)" state instead of the expected "(Read Only)" state and I see
> > no errors in the SQL Server Log.
> >
> > I've tried adding STATS=5 to the remote procedure, but the output does
> > not come back across to the linked server executing the job on SERVER-A,
> > so I can't see what's happening.
> >
> > If I execute the exact same statements in the remote proc using SQL
> > Query Analyzer on SERVER-B the procedure completes normally and the
> > database is left in "(Read Only)" state as expected.
> >
> > The problem only occurrs when running remotely from a job.
> >
> > I don't think it's a permissions issue, because the procedure executes
> > and the job comes back successfull.
> >
> > I just need help diagnosing the problem so I can automate my backups to
> > the warm standby for this particular DB.
> >
> > Like I say, it works for 12 other databases at my company.
> >
> > Thanks for your help.
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
>
>
- Next message: Scott Morris: "Re: Error 1603 installing MS SQL Server 2005 Beta 2 Setup Support"
- Previous message: Dooza: "Re: Duplicate Entries Help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
