Auto-restore of database backups

Our DBs are set up to do a full backup once a day (late at night) and then transaction log backups during the day at shorter intervals.

I want to setup a dev database on the same server. I want this database to be an automatically restored copy of the live database. So every night, after the full backup of the live DB, I want to restore the live DB to this dev DB.

Can this be automated? Can the restore automatically stop the dev database in case some open connections exist?

Is there a better way to do this?Ok, I got this far:

declare @.PhysicalDeviceName nvarchar(260)
select top 1
 @.PhysicalDeviceName = M.physical_device_name
 from backupmediafamily M
 join backupset S
 on M.media_set_id = S.media_set_id
 and S.database_name = 'northwind'
 and S.type = 'D'
 order by backup_finish_date desc
restore database NorthwindDev
 from disk = @.PhysicalDeviceName
 with move 'northwind' to 'D:\DB\Microsoft SQL Server\MSSQL\Test\Test.mdf',
 move 'northwind_log' to 'D:\DB\Microsoft SQL Server\MSSQL\Test\Test.ldf'

And this works fine. I'll schedule this in SQL Server Agent.

Now my only issue is that I need get exclusive access to the NorthwindDev database before the restore will work. How do I organise that? Do I have to kill all processes connected to NorthwindDev (horrors!)? Or is there a better way?|||Ok, it's working now. What I do is I kill all processes connected to NorthwindDev using a script, and then I run the restore above. I don't like the senseless killing, but it works... Is there a more elegant way?

declare @.PhysicalDeviceName nvarchar(260),
 @.spid int,
 @.Kill varchar(50)
create table #Locks
 (spid int,
 dbid int,
 ObjId int,
 IndId int,
 Type varchar(5),
 Resource varchar(50),
 Mode varchar(10),
 Status varchar(10))
insert into #Locks
exec sp_lock
declare NextFrag cursor local for
 select spid
 from #Locks L
 join master.dbo.sysdatabases D
 on L.dbid = D.dbid
 and D.[name] = 'NorthwindDev'
open NextFrag
while (1=1)
begin
 fetch next from NextFrag
 into @.spid
 if @.@.fetch_status = -1 break
 set @.Kill = 'kill ' + cast(@.spid as varchar)
 exec(@.Kill)
end
close NextFrag
deallocate NextFrag
drop table #Locks
select top 1
 @.PhysicalDeviceName = M.physical_device_name
 from backupmediafamily M
 join backupset S
 on M.media_set_id = S.media_set_id
 and S.database_name = 'Northwind'
 and S.type = 'D'
 order by backup_finish_date desc
restore database NorthwindDev
 from disk = @.PhysicalDeviceName
 with move 'northwind' to 'D:\DB\Microsoft SQL Server\MSSQL\Test\NorthwindDev.mdf',
 move 'northwind_log' to 'D:\DB\Microsoft SQL Server\MSSQL\Test\NorthwindDev.ldf'

|||I like talking to myself sometimes|||Why are there connections to the NorthwindDev database in the middle of the night? Do you have insomniac developers?

Reading BOL, it says that these processes should not be killed:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER

And be aware that it might take a while to kill a SPID, so you might want some sort of loop to check to make sure all relevant processes are truly killed before attempting the restore. (See the KILL topic in BOL for more info.)

And also, I came across this simple series of commands to kill all processes and perform the restore. This might be a better method:

alter database yourdatabasename set single_user with rollback immediate
alter database yourdatabasename set multi_user with rollback immediate
RESTORE DATABASE WebReqs FROM disk = 'c:\inetpub\wwwroot\administration\database\WebReqs.bak'

Terri
PS "Talking to yourself is a sign of impending mental collapse" -- Zork ;-)|||In my shop developers often leave Query Analyser or Enterprise Manager connected to the databases overnight. So even though nothing is executing, the processes are still connected and thus own shared DB locks.

Since it's only a dev DB that's getting overwritten anyway, I'm not going hold off on killing certain processes.

I'll give the single_user thing a go, though. Looks much better than my KILL hack.