Restore database
-
ps. (9/8/2011)
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...
The more I think of it, the more I'm sure I've seen both cases on the same server, in a query window.
I'll keep that in mind to try to pinpoint what could be different in each case.
-
Ninja's_RGR'us (9/8/2011)
I did and now I don't know where I got it from :-D.I'll write your name at the top of the notepad file;-)
I have done this only one time in the past when i copied Michael Valentine Jone's query so i remember.
But havent written names for other 'copied' queries.:-D
Pradeep Singh -
ps. (9/8/2011)
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...
I've definitely seen it run 'forever' before. Maybe a different version of SQL.
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 -
GilaMonster (9/8/2011)
I've definitely seen it run 'forever' before. Maybe a different version of SQL.We definitely believe you on this. Apparently this is a "it depends" answer.
Now only if the OP could come back so that we get back on topic :-).
-
ps. (9/8/2011)
Ninja's_RGR'us (9/8/2011)
I did and now I don't know where I got it from :-D.I'll write your name at the top of the notepad file;-)
I have done this only one time in the past when i copied Michael Valentine Jone's query so i remember.
But havent written names for other 'copied' queries.:-D
I actually feel a little bad about this.
I've started retracing the sources of most of my scripts so that I can give credit when I post them here.
I Never found it for this one :unsure:
-
I am restoring as a new database
-
Krasavita (9/8/2011)
I am restoring as a new databaseWhat does the script I posted returned?
-
In fact i shud write the names too 🙂
Glenn Berry i remember.. but hard to find actual owners for all queries 🙁
Pradeep Singh -
ps. (9/8/2011)
In fact i shud write the names too 🙂Glenn Berry i remember.. but hard to find actual owners for all queries 🙁
That's only my desktop (one of them anyways). I really need to organize this better :-).
-
lol i got two honorable mentions in there!
I gotta know, what is in that Lowell_used_columns file? i'm sure i have the same script saved, but I dunno what yours references!
Thanks Remi!
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! -
Lowell (9/8/2011)
lol i got two honorable mentions in there!I gotta know, what is in that Lowell_used_columns file? i'm sure i have the same script saved, but I dunno what yours references!
Thanks Remi!
I'm sure I got more than that from ya :-D.
It's the script you wrote to extract the columns used in a sp.
I'm modifying it so I can make it work on any cached plan (or profiler xml stats profile). That way I'll be able to run all my reports in one go and document the whole shebang ;-).
-
Here is my results:
I am ran this script on SQL Server,
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')
I am getting this result :
NOUNLOAD, REPLACE, STATS = 10,2011-09-08 13:02:32.313,0,1 hour(s), 50min, 9 sec,0 hour(s), 0min, 0 sec,2011-09-08 14:52:41.570
Message
1 row effected
-
Ping me back if this is not done tomorrow morning.
No more ideas at this point.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply