From: tolcis on
Hi,
I need to be able to establish an exclusive connection to the database
and do a restore to it. Nobody, can be connected to that database
prior to the restore. Also, I need to be able to schedule it to run
on a daily basis.
I tried putting that db in a single user mode and then do the restored
in a transaction but I got the error that the restore command is not
allowed in the begin tran commit tran.
How can I accomplish that?
From: Erland Sommarskog on
tolcis (nytollydba(a)gmail.com) writes:
> I need to be able to establish an exclusive connection to the database
> and do a restore to it. Nobody, can be connected to that database
> prior to the restore. Also, I need to be able to schedule it to run
> on a daily basis.
> I tried putting that db in a single user mode and then do the restored
> in a transaction but I got the error that the restore command is not
> allowed in the begin tran commit tran.
> How can I accomplish that?

Why would you need a transaction?

This is the commands you are looking for:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
RESTORE DATABASE db FROM ...
go
ALTER DATABASE db SET MULTI_USER


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx