May 28, 2024 at 6:05 pm
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.
"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]
May 29, 2024 at 6:39 am
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
May 29, 2024 at 7:37 pm
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]
May 29, 2024 at 11:54 pm
Please obfuscate the database name and then post the exact command(s) you're using to do the restore.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2024 at 1:00 am
ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\MyDB.bak' WITH REPLACE, STATS = 10;
Restore command
"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]
May 30, 2024 at 3:02 am
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
Change is inevitable... Change for the better is not.
May 30, 2024 at 5:25 am
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
May 30, 2024 at 10:03 am
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 = ....
May 30, 2024 at 11:21 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy