Database restoring,,,,, since 20 hrs
-
Hi,
i have 25 GB database backup file. I just restored it with a different database name. after starting restoring process it took hardly 20-30 minutes to complete the process successfully.
but even after 20 hrs, sql server management studio shows :
Database
abc
def (Restoring ...)
why this 'restoring... ' is been showing even after complition of restore process. ?
-
What was the exact restore command/options that you used?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
KcV (11/30/2013)
Hi,i have 25 GB database backup file. I just restored it with a different database name. after starting restoring process it took hardly 20-30 minutes to complete the process successfully.
but even after 20 hrs, sql server management studio shows :
Database
abc
def (Restoring ...)
why this 'restoring... ' is been showing even after complition of restore process. ?
Did you try right clicking on it and selecting "Refresh"? The Explorer window is rather famous for not automatically updating.
--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) -
Did you restore with "NORECOVERY" ?
That would show it "restoring" as it leaves it in a condition for you to restore additional Differentials or transaction logs.
If you do not have additional Diff or T-Logs to restore and want to use the database as it is, then run:
restore database MyDatabase with recovery
-
HI here my restore process.
- I just selected 'Restore Database' from 'database' from MSSMS.
- then i entered name of new database and defined a path to save location.
(Yes, Original database name is different from which i have defined due to i want a copy of same database with different name)
- Selected 'From Device'
- specified a backup file
- start restore. nothing else.
(i checked today morning, its still in same state)
-
Yes, i tried 'Refresh'. nothing happened.
-
Are you absolutely sure that you didn't check the option to allow more log 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi,
If you got disconnected before your database fully recovers from restoring then it stays in Recovering mode forever, this had happens with me once when i was restoring database over the network and network got disconnected.
-
yes, i think (99%) didn't checked this option.
-
Dear PravB4u,
i was not disconnected from database before completing restore process.
As i mentioned in my first post there was a message saying 'process is completed successfully'.
-
KcV (12/2/2013)
yes, i think (99%) didn't checked this option.I'm fairly sure you did.
RESTORE DATABASE <database name> WITH RECOVERYIf 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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?
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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
-
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');
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply