Database Stuck in Restoring!!

Question

Database Stuck in Restoring!!

MostInterestingMan

SSCertifiable

Points: 6291
More actions
November 11, 2010 at 6:11 am

#143540

Hi,
I have a database stuck in 'restoring' but there is no restore progress in the sql logs. Should I just let it continue?
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 15 posts - 1 through 14 (of 14 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

was the instance restarted? if yes it may take a while to roll forward transactions which depends on certain configuration settings.

Pradeep Singh

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

MostInterestingMan (11/11/2010)
I have a database stuck in 'restoring' but there is no restore progress in the sql logs. Should I just let it continue?

Was this DB restored from backup? If so, what's the exact restore database command that was run?

Query sys.databases, what's the exact value for state_desc for this database?

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 3

GilaMonster (11/11/2010)
MostInterestingMan (11/11/2010)
I have a database stuck in 'restoring' but there is no restore progress in the sql logs. Should I just let it continue?
Was this DB restored from backup? If so, what's the exact restore database command that was run?
Query sys.databases, what's the exact value for state_desc for this database?

Yes the DB is being restored from a full backup. I am working on getting the EXACT restore command as the restore is triggered thru an app. From sys.databases, the value for STATE_DESC is RESTORING.

...No, the instance was not restarted

Thanks

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

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

ps. (11/11/2010)
was the instance restarted? if yes it may take a while to roll forward transactions which depends on certain configuration settings.

That's recovering, not restoring.

The DB will go into the recovering state after a restart. It will go into restoring if a restore (or backup) is done WITH NORECOVERY.

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

MostInterestingMan (11/11/2010)
GilaMonster (11/11/2010)
MostInterestingMan (11/11/2010)
I have a database stuck in 'restoring' but there is no restore progress in the sql logs. Should I just let it continue?
Was this DB restored from backup? If so, what's the exact restore database command that was run?
Query sys.databases, what's the exact value for state_desc for this database?
Yes the DB is being restored from a full backup. I am working on getting the EXACT restore command as the restore is triggered thru an app. From sys.databases, the value for STATE_DESC is RESTORING.
...No, the instance was not restarted
Thanks

restore is triggered thru an app.

Which application are you try to restore ?

How you restored the DB from ssms ?

The commands get successful.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 6

GilaMonster (11/11/2010)
ps. (11/11/2010)
was the instance restarted? if yes it may take a while to roll forward transactions which depends on certain configuration settings.
That's recovering, not restoring.
The DB will go into the recovering state after a restart. It will go into restoring if a restore (or backup) is done WITH NORECOVERY.

USE master

ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE mydatabase

FROM DISK = @Backup

WITH REPLACE

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

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

Huh? That's not even valid syntax.

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

ps. One Orange Chip Points: 29422 More actions · Answer 8

ps.

One Orange Chip

Points: 29422

November 11, 2010 at 7:26 am

#1249497

Thanks for correcting me Gail. 🙂

Pradeep Singh

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 9

GilaMonster (11/11/2010)
Huh? That's not even valid syntax.

Sorry Gail, I edited the syntax.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

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

That should be fine. Check sys.dm_exec_requests, see what the wait type is.

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 11

GilaMonster (11/11/2010)
That should be fine. Check sys.dm_exec_requests, see what the wait type is.

The wait_type is NULL.

At this point is it okay to just drop it and attempt to re-restore using SSMS?

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

homebrew01 SSC Guru Points: 55548 More actions · Answer 12

Not sure if this applies in this case, but I found this script somewhere ... (probably SSC) and have used it to see how far along a backup or restore is:

SELECT command,

s.text,

start_time,

percent_complete,

CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '

+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '

+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 13

Thanks for all of your inputs...

RESTORE DATABASE mydatabase

FROM DISK = @Backup

WITH REPLACE, RECOVERY

fixed the issue. Again thanks.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 14

MostInterestingMan (11/11/2010)
Thanks for all of your inputs...
RESTORE DATABASE mydatabase
FROM DISK = @Backup
WITH REPLACE, RECOVERY
fixed the issue. Again thanks.

Thats already suggested by gail.

Huh? That's not even valid syntax.
--------------------------------------------------------------------------------
Gail Shaw

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/