Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Database restoring,,,,, since 20 hrs Expand / Collapse
Author
Message
Posted Monday, December 2, 2013 4:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 44,610, Visits: 42,355
KcV (12/2/2013)
yes, i think (99%) didn't checked this option.


I'm fairly sure you did.

RESTORE DATABASE <database name> WITH RECOVERY

If that brings your database online then you did select the option in the restore dialog to leave the database in the restoring state to allow for further backups to be restored.



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

Post #1518847
Posted Tuesday, December 3, 2013 12:22 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, May 12, 2016 11:41 AM
Points: 612, Visits: 1,899
Question: If you run this command

RESTORE DATABASE databasename WITH RECOVERY

after a RESTORE WITH NORECOVERY as shown in GilaMonster's post, the database will change to a normal state and you're ready to go.

What happens if you run this command while a database is actually in the process of restoring? Is this a safe thing for the OP to try?
Post #1519362
Posted Tuesday, December 3, 2013 2:18 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 44,610, Visits: 42,355
dan-572483 (12/3/2013)
What happens if you run this command while a database is actually in the process of restoring?


Nothing, because the running restore will have an exclusive lock on the database.

Is this a safe thing for the OP to try?


Wouldn't have suggested it if it wasn't.



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

Post #1519412
Posted Thursday, January 9, 2014 3:00 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 3, 2016 6:36 PM
Points: 80, Visits: 281
I've witnessed restore operations take FOREVER due to extremely high VLF counts

Michelle Ufford has a good script for checking the count http://sqlfool.com/2010/06/check-vlf-counts/


Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Post #1529589
Posted Friday, January 10, 2014 8:54 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 23, 2016 6:22 AM
Points: 1,498, Visits: 3,517
Do a SP_WHO2 and see what SPID your restore is running then do a SP_WHO2 99 with 99 being the thread number... and keep hitting enter to see if any of the numbers are increasing....

or run this

Select r.command
, s.text
, r.start_time
, r.percent_complete
, cast(((datediff(second, r.start_time, getdate())) / 3600) As varchar) + ' hour(s), '
+ cast((datediff(second, r.start_time, getdate()) % 3600) / 60 As varchar) + 'min, '
+ cast((datediff(second, r.start_time, getdate()) % 60) As varchar) + ' sec' As running_time
, cast((r.estimated_completion_time / 3600000) As varchar) + ' hour(s), '
+ cast((r.estimated_completion_time % 3600000) / 60000 As varchar) + 'min, '
+ cast((r.estimated_completion_time % 60000) / 1000 As varchar) + ' sec' As est_time_to_go
, dateadd(second, r.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 Like 'DBCC%'
Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');




Post #1529853
Posted Friday, January 10, 2014 8:55 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 23, 2016 6:22 AM
Points: 1,498, Visits: 3,517
Markus (1/10/2014)
Do a SP_WHO2 and see what SPID your restore is running then do a SP_WHO2 99 with 99 being the thread number... and keep hitting enter to see if any of the numbers are increasing....

or run this

Select r.command
, s.text
, r.start_time
, r.percent_complete
, cast(((datediff(second, r.start_time, getdate())) / 3600) As varchar) + ' hour(s), '
+ cast((datediff(second, r.start_time, getdate()) % 3600) / 60 As varchar) + 'min, '
+ cast((datediff(second, r.start_time, getdate()) % 60) As varchar) + ' sec' As running_time
, cast((r.estimated_completion_time / 3600000) As varchar) + ' hour(s), '
+ cast((r.estimated_completion_time % 3600000) / 60000 As varchar) + 'min, '
+ cast((r.estimated_completion_time % 60000) / 1000 As varchar) + ' sec' As est_time_to_go
, dateadd(second, r.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 Like 'DBCC%'
Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');



disrgard this... I reread your post and the restore is done but the db still shows as restoring. This won't help... sorry.



Post #1529854
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse