Results 1 to 7 of 7

Thread: SQL 6.5 - restore as scheduled task?

  1. #1
    Join Date
    Oct 2003
    Posts
    14

    Question SQL 6.5 - restore as scheduled task?

    Our company's database is maintained offsite, and every night a backup is posted to an FTP server. I download the backup file and restore it to our local server each morning. I'd like to be able to set up the restore as a
    scheduled task. I've tried doing it with a LOAD DATABASE command, but when I try to run the task and check the
    history, I see that it has failed after one or two seconds. When I check the failure details I see the following message:

    Database in use. System Administrator must have exclusive use of database to run load. (Message 3101)

    I've tried stopping and restarting the MSSQL Server service on the server, but it doesn't fix the problem. I can also confirm that nobody is in the database at the time this is happening.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Run Kill process procedure before loading the database.

    http://forums.databasejournal.com/sh...highlight=Kill

  3. #3
    Join Date
    Oct 2003
    Posts
    14
    Thanks, I just tried creating the SP and got the errors below:

    Server: Msg 131, Level 15, State 1, Procedure USP_KILLPROCESS, Line 5
    The size (320) given to the parameter '@QUERY' exceeds the maximum. The largest size allowed is 255.
    Server: Msg 170, Level 15, State 1, Procedure USP_KILLPROCESS, Line 18
    Line 18: Incorrect syntax near '@QUERY'.
    Server: Msg 137, Level 15, State 1, Procedure USP_KILLPROCESS, Line 20
    Must declare variable '@QUERY'.

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    oops. My bad. That code was written for 7/2000.

    here it is for 6.5


    use master
    go
    set quoted_identifier off
    go
    --drop procedure USP_KILLPROCESS
    Create PROCEDURE USP_KILLPROCESS @dbname varchar(128) AS
    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER OFF
    DECLARE @KILL_ID smallint
    DECLARE @QUERY VARCHAR(255)

    DECLARE KILLPROCESS_CURSOR CURSOR FOR
    SELECT A.SPID FROM SYSPROCESSES A JOIN
    SYSDATABASES B ON A.DBID=B.DBID WHERE B.NAME=@DBNAME


    OPEN KILLPROCESS_CURSOR
    FETCH NEXT FROM KILLPROCESS_CURSOR INTO @KILL_ID

    WHILE(@@FETCH_STATUS =0)
    BEGIN

    SELECT @QUERY = "KILL "+ CONVERT(VARCHAR(128),@KILL_ID)
    --print @query
    EXEC (@QUERY)
    FETCH NEXT FROM KILLPROCESS_CURSOR INTO @KILL_ID
    END

    CLOSE KILLPROCESS_CURSOR
    DEALLOCATE KILLPROCESS_CURSOR

    --USAGE
    --EXEC USP_KILLPROCESS "PUBS"

  5. #5
    Join Date
    Oct 2003
    Posts
    14
    Good news and bad news:

    The good news: I was able to create the stored procedure.

    The bad news: I still get the same failure message when I try to run the scheduled task. The syntax of my task's command is:

    EXEC USP_KILLPROCESS mydatabase
    LOAD DATABASE mydatabase FROM mybackup

    Syntax problem maybe?

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --works fine for me
    --DUMP database PUBS to disk ="d:\pubs.dmp"

    EXEC USP_KILLPROCESS "PUBS"
    LOAD DATABASE from disk ="d:\pubs.dmp"

  7. #7
    Join Date
    Oct 2003
    Posts
    14
    I think I nailed the problem. In the "Edit Task" window, I had the database I was trying to restore to selected from the "Database" drop-down. I changed that to the master database and the scheduled task ran successfully.

    I will keep your KILLPROCESS SP running just as an extra precaution for now. Thanks again for your help.

Posting Permissions

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