Competition: What’s your favorite Redgate tool? Enter now.

Error on Restore

mfailemfaile Posts: 18 Bronze 2
edited December 19, 2006 12:27PM in SQL Backup Previous Versions
Have been using ext stored proc for a while with no issue. This evening I was implemeting a variable into the mix and got the error:

VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had
elapsed.)
SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3101: Exclusive access could not be obtained because the database is in use.

I am in QA as SA....the path includes the Sql Bin.
I went back and tried the ext stored proc without the variable and still got the error.

Forgot but using v4.6.0.815

Any suggestions?
thx
matt

Comments

  • mfailemfaile Posts: 18 Bronze 2
    My issue was not an issue. The DB that I was trying to restore has just seen it's load increase and I still had users in it thus the exclusive access message. The clear connections in Detach DB via Ent Manager didn't work. I had to take the DB offline and then bring it back online and then run the restore.

    In my script I was putting the DB in single user mode but apparently that was not doing the trick. Is there a way via script that I can take the db offline & back online right before I run the restore command?

    thx
    matt
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Matt,

    You could try killing off any SPIDs connected to the database before restoring to it. Here is a bit of a sample script:
    DECLARE @ProcessId varchar(4) 
    
         DECLARE CurrentProcesses SCROLL CURSOR FOR 
         select spid from master..sysprocesses where dbid=(SELECT dbid FROM sysdatabases WHERE NAME='<name of database>') order by spid 
         FOR READ ONLY 
    OPEN CurrentProcesses 
    FETCH NEXT FROM CurrentProcesses INTO @ProcessId 
    WHILE @@FETCH_STATUS <> -1 
    BEGIN 
         Exec ('KILL ' +  @ProcessId) 
         FETCH NEXT FROM CurrentProcesses INTO @ProcessId 
    END 
    CLOSE CurrentProcesses 
    DeAllocate CurrentProcesses
    
Sign In or Register to comment.