-
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.
-
Run Kill process procedure before loading the database.
http://forums.databasejournal.com/sh...highlight=Kill
-
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'.
-
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"
-
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?
-
--works fine for me
--DUMP database PUBS to disk ="d:\pubs.dmp"
EXEC USP_KILLPROCESS "PUBS"
LOAD DATABASE from disk ="d:\pubs.dmp"
-
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
-
Forum Rules
|
|