DB stuck in restoring state

Question

DB stuck in restoring state

LearningDBA

SSCertifiable

Points: 6609
More actions
May 28, 2024 at 6:05 pm

#4424493
A developer tried restoring a test DB 3 times and each time it was getting stuck at restoring. And when I tried restoring the DB "Restore database DBName from disk '' with recovery I ran into the same issue and I saw DB, stuck in restoring state.
Sys_dm_requests doesn't show any records because I could see the progress before... I even tried running Restore database DBName with recovery but then I see this error "the database cannot be recovered because the log was not restored". I don't have any logs I need to restore.. I only take full and diff. I have never seen this issue before so not sure what I can do... ANy help or feedback is highly appreciated.
- This topic was modified 7 months ago by LearningDBA.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Viewing 9 posts - 1 through 8 (of 8 total)

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

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

If you only take full and diff backups, I assume your databases are in simple recovery model ( or your db log file(s) will keep on growing after the first full backup )

Regarding your question:

Did you use eXtended Events to monitor the Db Restore(s) ?

ref: Capture Detailed SQL Server Restore Steps with Extended Events

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

LearningDBA SSCertifiable Points: 6609 More actions · Answer 2

Even though the database is in simple recovery mode, I see "offline rollforward begins" in extended events when I restore a database.

Date transfer is complete

backup set is restored

offline rollforward begins

Processing 783 VLFs

Processing VLF header is complete

First LSN *********, Last LSN ******

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

Please obfuscate the database name and then post the exact command(s) you're using to do the restore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LearningDBA SSCertifiable Points: 6609 More actions · Answer 4

ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\MyDB.bak' WITH REPLACE, STATS = 10;

Restore command

This reply was modified 6 months, 4 weeks ago by LearningDBA.
This reply was modified 6 months, 4 weeks ago by LearningDBA.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

LearningDBA wrote:

ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\MyDB.bak' WITH REPLACE, STATS = 10;

Restore command

Interesting. I've never using the SET OFFLINE option before. Here's what I normally do an have never had the problem with the Restoring State...

    USE master;
  ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  ALTER DATABASE MyDB SET MULTI_USER;
RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\MyDB.bak' WITH REPLACE, STATS = 10;

The immediate MyDB SET MULTI_USER makes it so that if I lose the connection for any reason, the system isn't stuck with a single user having control whether it's an app, job, or whatever.

Again, I don't know if what I posted above will solve the problem but I've never done it the way you're doing it and I've not seen anyone else do it that way, either. I just know that I've never had the problem with the code I've posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

Your script seems to be what it needs to be.

Which version number is your target sqlserver instance ? ( did you apply the (near) latest cumulatieve updates

ref: SQL Server Builds

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ken McKelvey SSCoach Points: 18856 More actions · Answer 7

The first thing I would check is the amount of disk space available.

Also, I would rarely use RESTORE...WITH REPLACE. I would normally restore to something like MyDb202405301201 and then use ALTER DATABASE ... MODIFY NAME = ....

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 8

Ken McKelvey wrote:

The first thing I would check is the amount of disk space available.
Also, I would rarely use RESTORE...WITH REPLACE. I would normally restore to something like MyDb202405301201 and then use ALTER DATABASE ... MODIFY NAME = ....

Why?

We frequently "refresh" data from production to qa or dev to have more recent data, as assembled and used in production.

That's why we want to have the same db name ( and file structure / naming conventions ).

Unless of course, it is to handle a staging db to recover parts of distructed data and will drop that db after a couple of days.

Any way, that doens't handle the issue OP is trying to solve

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me