Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 11:28 AM
Points: 553, Visits: 1,618
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 @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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, September 16, 2014 12:10 PM
Points: 80, Visits: 259
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:34 PM
Points: 1,276, Visits: 2,803
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:34 PM
Points: 1,276, Visits: 2,803
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