Restore database

Question

Post reply

Restore database

Krasavita

SSC-Insane

Points: 23138
More actions
September 8, 2011 at 9:56 am

#256819

I am trying to restore database 120GB and 1 hour later still 0 progress,any idea why? SQL agent is not showing that I am restoring databases
Thank you

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

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

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

how are u measuring progress? hv you used stats clause in restore? or are you using dmvs?

Pradeep Singh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

That will show you the progress :

SELECT r.[session_id]

, c.[client_net_address]

, s.[host_name]

, c.[connect_time]

, [request_start_time] = s.[last_request_start_time]

, [current_time] = CURRENT_TIMESTAMP

, r.[percent_complete]

, [estimated_finish_time] = DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP)

, [estimated_seconds_left] = DATEDIFF(s, CURRENT_TIMESTAMP, DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP))

, current_command = SUBSTRING ( t.[text], r.[statement_start_offset]/2, COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647) )

, module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>')

FROM sys.dm_exec_requests AS r

INNER JOIN sys.dm_exec_connections AS c

ON r.[session_id] = c.[session_id]

INNER JOIN sys.dm_exec_sessions AS s

ON r.[session_id] = s.[session_id]

CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t

WHERE r.[percent_complete] <> 0;

Lowell SSC Guru Points: 323518 More actions · Answer 3

are you are restoring a backup of the database as a new database?

I believe it can take time for the operating system to find 120 gig of as-contigous-as-possible space in order to put the backup in it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Lowell (9/8/2011)
are you are restoring a backup of the database as a new database?
I believe it can take time for the operating system to find 120 gig of as-contigous-as-possible space in order to put the backup in it.

Agreed, but on a decent san this takes only seconds. Maybe minutes on a slower array.

Do you have instant file initialization on?

How big is the log file in the restored DB? That file needs to be 0 initialized and that takes time.

http://sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx

Lowell SSC Guru Points: 323518 More actions · Answer 5

Ninja's_RGR'us (9/8/2011)
How big is the log file in the restored DB? That file needs to be 0 initialized and that takes time.
http://sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx

ahh, yeah zeroing out the log file , that makes sense too. one more thing i knew once and forgot about.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Lowell (9/8/2011)
Ninja's_RGR'us (9/8/2011)
How big is the log file in the restored DB? That file needs to be 0 initialized and that takes time.
http://sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx
ahh, yeah zeroing out the log file , that makes sense too. one more thing i knew once and forgot about.

Don't feel bad. You forgot more than most people will ever know. Sad, but nice to know :-P.

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

Restoring over an existing database that is in use? If so, the restore can't even start until exclusive access is obtained on the DB.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

GilaMonster (9/8/2011)
Restoring over an existing database that is in use? If so, the restore can't even start until exclusive access is obtained on the DB.

Ya but doesn't that error out WAY before 60 minutes?

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

GilaMonster (9/8/2011)
Restoring over an existing database that is in use? If so, the restore can't even start until exclusive access is obtained on the DB.

SQL throws error message for the same. I hv not seen SQL server wait for an hour to throw this msg. May be the restore is in progress...

Pradeep Singh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

ps. (9/8/2011)
GilaMonster (9/8/2011)
Restoring over an existing database that is in use? If so, the restore can't even start until exclusive access is obtained on the DB.
SQL throws error message for the same. I hv not seen SQL server wait for an hour to throw this msg. May be the restore is in progress...

The script I posted will answer that, but the op is nowhere to be found at this point!

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

ps. (9/8/2011)
GilaMonster (9/8/2011)
Restoring over an existing database that is in use? If so, the restore can't even start until exclusive access is obtained on the DB.
SQL throws error message for the same. I hv not seen SQL server wait for an hour to throw this msg. May be the restore is in progress...

SQL doesn't throw errors in this case, because there's nothing actually wrong.

The Management Studio GUI restore dialog will throw errors (timeout errors), but if the restore is being done from a query window it will wait forever as the query windows by default have a query timeout of 0 (forever)

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 12

Thanks for the script Remi. I had written one but this is better. Copied and saved for future use 😉

Pradeep Singh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

ps. (9/8/2011)
Thanks for the script Remi. I had written one but this is better. Copied and saved for future use 😉

Don't forget to give references.

I did and now I don't know where I got it from :-D.

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

GilaMonster (9/8/2011)
ps. (9/8/2011)
GilaMonster (9/8/2011)
Restoring over an existing database that is in use? If so, the restore can't even start until exclusive access is obtained on the DB.
SQL throws error message for the same. I hv not seen SQL server wait for an hour to throw this msg. May be the restore is in progress...
SQL doesn't throw errors in this case, because there's nothing actually wrong.
The Management Studio GUI restore dialog will throw errors (timeout errors), but if the restore is being done from a query window it will wait forever as the query windows by default have a query timeout of 0 (forever)

Gail, I've seen exclusive access cannot be obtained error in query window while restoring databases many times. And SSMS has default settings...

Pradeep Singh