Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    36

    Unanswered: Restoring the database using stored procedure

    hey guyz...
    i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"

    i have included the stored procedure below. is the stored procedure correct?
    if it is.. how can i sovle this problem?


    CREATE Procedure spRestoreDatabase
    @Path VARCHAR(100)
    AS
    Restore Database Test From Disk = @Path
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A database restore can only be done when you've got exclusive use of the database. No other users can be using the database besides the one doing the restore.

    The procedure can't be in the database, because then the spid trying to restore the datbase would need to be executing code in the database which would prevent the restore.

    An SQL Agent job would be a better choice than a stored procedure. You may need to think about using ALTER DATABASE to force the other users out of the database, but think that through carefully before you try to use it because it can cause other problems.

    -PatP

  3. #3
    Join Date
    Feb 2005
    Posts
    36
    erm... so what can i do now?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Put your stored procedure in a different database and run it from there.

    Gotta ask why you need a stored proc to restore your database in the first place. Surely this isn't going to be an automated task?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ryanlum
    erm... so what can i do now?
    As blindman pointed out, your first step ought to be to rethink what you are doing. Try to determine if there is some better way than an automated restore.

    If you really, truly need to automate the restore, I'd do it as a SQL Agent Job myself. You can actually get pretty tricky with jobs, and do nearly anything you can do with a stored procedure, plus a whole lot more.

    Before you get too busy coding, think hard about what you are doing, why you are doing it, and whether there are any alternatives. You are headed into the area that cartographers of olde used to label "Here be dragons"

    -PatP

Posting Permissions

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