restoring status

Question

restoring status

sqlfriend

SSC Guru

Points: 52769
More actions
April 12, 2013 at 3:56 pm

#276449

I have restored a database from a backup, and the restore is OK.
I went into it checking security is Ok, tables are OK,
Then after a few hours I logged in again, and see the database is at restoring status.
Why is that?
I have no jobs schedules to do that, and I am not aware of anyone else logging to this new server.
It happened once, I thought I missed something, I restored again, it is fine, I can open tables, edit, and then after a few hours, I login and see it is at restoring status again...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sqlfriends (4/12/2013)
Then after a few hours I logged in again, and see the database is at restoring status.
Why is that?

Someone, some job or some application either ran a restore or ran a backup log .. with norecovery. SQL doesn't switch databases into the restoring state by itself.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

By your hints, I do have some clue now.

we do have a full, diff, and a transaction log backup scheduled. Full happens only at weekend, transaction and diff will happen each day.

Since this is a dev server I just set up, I have not run a full backup yet, but the transaction backup has already started,

is that possible because there is not a full backup yet, the transaction log backup will put the database in restoring status.

This didn't happend on SQL server standard edition, it will just fail and tell you cannnot do a transaction log backup without a full backup.

This is the first enterprise edition we set up, so is it behaving differently?

Thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sqlfriends (4/12/2013)
is that possible because there is not a full backup yet, the transaction log backup will put the database in restoring status.

No.

With no full backup yet, log backups will fail

This is the first enterprise edition we set up, so is it behaving differently?

Nope.

For a log backup to switch the database into recovering, the log backup must be run WITH NORECOVERY, ie a tail log backup. That's not something you'd put into a maintenance plan, it's something you do in a disaster.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

Thanks, that is very strange then,

In maintenance plan, we just use regular backup. we don't do with norecovery.

And I did check with one developer, he didn't do anything either.

Wierd...

dan-572483 SSCoach Points: 15870 More actions · Answer 5

Is it possible you're logging into an existing session where SQL Management Studio was already running and the app simply hasn't refreshed the object restore pane since the restore was running. Once you can open tables, etc, there's simply no way for a DB to revert to restoring status unless someone else is running a restore or if there's an automated restore job running.