Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Unanswered: Remote Stored Proc Call

    I'm calling this from another sql server....
    I created a linked server... and want to restore database backups on the other box....
    The restore script runs fine when ran locally but fails with the message below when calling it remotely

    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    Server: Msg 3101, Level 16, State 1, Line 1
    Exclusive access could not be obtained because the database is in use.

    CREATE PROCEDURE usp_restore_database_backups AS

    RESTORE DATABASE BesMgmt
    FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
    WITH
    --DBO_ONLY,
    REPLACE,
    --STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
    MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
    MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'

    WAITFOR DELAY '00:00:05'


    EXEC sp_dboption 'BesMgmt', 'single user', true
    GO


    I have set it to read only dbo only .... single user.... still get the same message....
    does anyone have any suggestions....

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    try executing...

    ALTER DATABASE <MyDB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

    or

    ALTER DATABASE <MyDB> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    before the restore statement and drop the sp_dboption.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    thanks Thrasymachus
    I'm still getting the same error:
    Executed as user: NFCU\sqlsvc. RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101). The step failed.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    post the script you are currently using and try running sp_who when it fails to see what connections are in use. Also when you execute this script in the QA are you connected to the database you are trying to restore? The database selected from the dropdown should not be the database you are trying to restore. If everything is OK with your script I suspect this is the case.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The error does not tally with this but just belt and braces.

    If you run
    Code:
    EXEC sp_helpserver

    do you see RPC & RPC out in the status field?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Script & Script Errors Log Shipping Remote Proc Call

    When I set the database manually into single user mode I get a different error when trying to restore the database.... this is the error that comes from when the database is in single user mode.....
    "Executed as user: NFCU\sqlsvc. Cannot open database requested in login 'BESMgmt'. Login fails. [SQLSTATE 42000] (Error 4060). The step failed."


    CREATE PROCEDURE usp_restore_database_backups AS

    /*
    declare @x varchar(255)
    select @x = @x + ' kill ' + convert(varchar(5), spid)
    from master.dbo.sysprocesses
    where dbid = db_id ('BesMgmt')
    exec (@x)
    */

    ALTER DATABASE BesMgmt SET SINGLE_USER WITH ROLLBACK IMMEDIATE


    RESTORE DATABASE BesMgmt
    FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
    WITH
    --DBO_ONLY,
    REPLACE,
    --STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
    MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
    MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'

    WAITFOR DELAY '00:00:05'


    --EXEC sp_dboption 'BesMgmt', 'single user', true
    GO

  7. #7
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    The command is within a job...
    the sql agent service is ran under a domain user acount...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •