Restore Schedule Job error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restore Schedule Job error

Can you please help me to see if my script is wrong, as i am scheduling my
Database RESTORE Job. This Job will run once in every month. My schedule is
fine but i think problem is on my script & getting following error. i am only one logged
in to this server.no user is connected nor any query analyzer is open. This server is on
Latest SP-4. Script:-
alter database CCS_SITE_Monthly
set RESTRICTED_USER (i can also use single_user)
with rollback immediate
restore database CCS_SITE_Monthly
from disk = ‘E:Tempccs_site. bak’
with replace; ERROR:-
Exclusive access could not be obtained because the database is in use.
[SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.
Add
use master
after altering the database. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
We had a similar issue when folks would leave Query Analyzer session open or something at nights. So I resolve the problem with "live" connections still be out there by simply putting the following script in before the restore which will kill all live connections to that database. (you will need to modify the script since I have it hardcoded to use DBID = 8 which is my database.
declare @procid smallint
declare @command char(50)
DECLARE procs_cursor CURSOR FOR
SELECT distinct(spid) from master..syslocks where dbid = 8 OPEN procs_cursor FETCH NEXT FROM procs_cursor INTO @procid WHILE @@FETCH_STATUS = 0
BEGIN
set @command = ‘kill ‘ +convert(char, @procid)
select @command
exec (@command)
— Get the next spid.
FETCH NEXT FROM procs_cursor INTO @procid
END CLOSE procs_cursor
DEALLOCATE procs_cursor Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
]]>