SQLRestore error
-
Exclusive access can not be obtained because the database is in use.RESTORE DATABASE is terminating abnormally
This is what I get after executing SQLDMO.Restore.SQLRestore procedure. The exact code looks like this.
------------------------------------------------------------------
Set myRestore = New SQLDMO.Restore
myRestore.Files = Text1.Text
myRestore.Database = "MyDB"
myRestore.ReplaceDatabase = True
' This is where the error occurs
myRestore.SQLRestore myServer
-------------------------------------------------------------------
where myRestore is a valid SQLDMO.Restore object
myServer is a valid SQLDMO.SQLServer object
I already closed and destroyed my ADODB.Connection and other ADODB objects (Recordsets, Commands) object just before committing the restoration.
What is happening here?
Thanks in advance.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
Dont see anything immediately wrong - have you verified prior to running your code that no connections to the db still exist? Not just closing everything, checking in Query Analyzer?
Andy
-
my VB is the only app running. I only have one connection. I listed every ADO and SQLDMO object and destroyed them prior to the invocation of the restore.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
I just ran the code, nothing wrong with it. I get the error if I leave a connection open - for testing I just opened the tables node in Enterprise Manager. Regardless of what you think you have open, you have to select against sysprocesses to be sure that nothing is still holding the connection open. Some connections dont close immediately. For production, I'd recommend trapping the error, then killing any connection to the db, then do a resume.
Andy
-
Thanks very much Andy! Your sysprocesses saved the day!
I was able to trace what really was happening in my database thru the sysprocesses system table and found the culprit: a SQLDMO.SQLServer within my class which I destroyed but did not close. I was wrong by assuming that there is an implied invocation of the SQLDMO.SQLServer.Close method once I destroy a SQLDMO.SQLServer object.
However, another problem arose. It has something to do with the logins that once had access to the database. I decided to post it as another topic http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=6053&FORUM_ID=22&CAT_ID=2&Topic_Title=SQLDMO%2ESQLRestore+erases+loginames&Forum_Title=DMO
Edited by - bani on 08/08/2002 1:21:12 PM
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
Glad it helped. For those following along, the server object has a disconnect method that should be called prior to setting the object to nothing. So far I've never had a reason to kill the object and leave the connection open, but there must be a reason to have it that way?
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply