Restore Database

Hi everyone,
I have a SQL Server JOB that restores a database every day at 2 AM.
Here's the script:
USE master
RESTORE DATABASE SomeDatabase
FROM DISK = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
WITH RECOVERY,
MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
GO
However, if I had a SQL Query window open, the job will fail and I get
an error stating that the database is in use. Is there any way that I
can still restore even if the database is in use?
*** Sent via Developersdex http://www.codecomments.com ***
Audrey,
You need to chase any users out of the database first. One way is to add to
your script.
ALTER DATABASE SomeDatabase SET RESTRICTED_USER
ALTER DATABASE SomeDatabase SET SINGLE_USER
This will first limit future connections to db_owner, db_creator and
sysadmin, then switching to single user will kick other connections out.
Once you have restored, remember to do:
ALTER DATABASE SomeDatabase SET MULTI_USER
Make sure that the SET MULTI_USER works even if the restore cannot run for
the reason that some other sysadmin got in during the moment between the
ALTER and the RESTORE.
RLF
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:%23r%23NwEJbHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> I have a SQL Server JOB that restores a database every day at 2 AM.
> Here's the script:
> USE master
> RESTORE DATABASE SomeDatabase
> FROM DISK = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
> WITH RECOVERY,
> MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
> MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
> GO
> However, if I had a SQL Query window open, the job will fail and I get
> an error stating that the database is in use. Is there any way that I
> can still restore even if the database is in use?
> *** Sent via Developersdex http://www.codecomments.com ***
|||Ok great! I will try that! Thanks!
*** Sent via Developersdex http://www.codecomments.com ***
|||I disagree RLF. Restricted_user only limits connections to db_owner, not
the number of them. And Single User will only work if you use this specific
syntax:
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Note however that that WILL yank the run out from under currently inprocess
transactions.
Another method is to loop through all currently open spids connected to that
database and issue the KILL command, thusly:
WHILE 1 = 1
BEGIN
SELECT @.activespid = spid
FROM master.dbo.sysprocesses (NOLOCK)
WHERE db_name(dbid) = @.dbname
AND SPID > 50
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
SET @.sql = 'KILL ' + cast(@.activespid as varchar(10))
EXEC (@.sql) --can't seem to directly EXECute a kill !
--give server time to finalize kill before looping
WAITFOR DELAY '00:00:00.1'
END
SET @.i = @.i + 1
IF @.i > 100 --need to exit IF hung
BEGIN
BREAK
END
END
IF @.i > 100 --couldn't kill all spids
BEGIN --raise error, then move on
RAISERROR(60000,10,1,@.dbname) WITH LOG
GOTO NextFile
END
The looping code has legacy stuff from sql7/2000 where I experienced
problems with KILL working properly on a very infrequent basis. I do not
know if SQL2K5 exhibits the same behavior.
TheSQLGuru
President
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eWY3WMJbHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Audrey,
> You need to chase any users out of the database first. One way is to add
> to your script.
> ALTER DATABASE SomeDatabase SET RESTRICTED_USER
> ALTER DATABASE SomeDatabase SET SINGLE_USER
> This will first limit future connections to db_owner, db_creator and
> sysadmin, then switching to single user will kick other connections out.
> Once you have restored, remember to do:
> ALTER DATABASE SomeDatabase SET MULTI_USER
> Make sure that the SET MULTI_USER works even if the restore cannot run for
> the reason that some other sysadmin got in during the moment between the
> ALTER and the RESTORE.
> RLF
> "Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
> news:%23r%23NwEJbHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
|||Of course, you are right to add the clause on the SET SINGLE_USER, or ...
ALTER DATABASE mydatabase SET SINGLE_USER ROLLBACK AFTER integer SECONDS
However, the switching to RESTRICTED is useful since it limits who else can
connect. I have had switching to SINGLE_USER mode fail to give me the
needed results if I did not do RESTRICTED first. In one case another
process connected to the database in the instant between going to
SINGLE_USER mode and doing my next scripted command. That was a pain in the
neck to resolve.
Of course, KILL also clobbers currently inprocess transactions. I began
using the ALTER approach (using WITH ROLLBACK IMMEDIATE, of course) because
some transactions would not kill reliably in 2000, as you noted.
I don't know either about SS2005 overall, but I have experienced one
unkillable connection on 2005 that was holding some locks. I finally had to
stop and restart SQL Server.
FWIW,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OqNNg6ZbHHA.4888@.TK2MSFTNGP06.phx.gbl...
>I disagree RLF. Restricted_user only limits connections to db_owner, not
>the number of them. And Single User will only work if you use this
>specific syntax:
> ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> Note however that that WILL yank the run out from under currently
> inprocess transactions.
> Another method is to loop through all currently open spids connected to
> that database and issue the KILL command, thusly:
> WHILE 1 = 1
> BEGIN
> SELECT @.activespid = spid
> FROM master.dbo.sysprocesses (NOLOCK)
> WHERE db_name(dbid) = @.dbname
> AND SPID > 50
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> SET @.sql = 'KILL ' + cast(@.activespid as varchar(10))
> EXEC (@.sql) --can't seem to directly EXECute a kill !
> --give server time to finalize kill before looping
> WAITFOR DELAY '00:00:00.1'
> END
> SET @.i = @.i + 1
> IF @.i > 100 --need to exit IF hung
> BEGIN
> BREAK
> END
> END
>
> IF @.i > 100 --couldn't kill all spids
> BEGIN --raise error, then move on
> RAISERROR(60000,10,1,@.dbname) WITH LOG
> GOTO NextFile
> END
>
> The looping code has legacy stuff from sql7/2000 where I experienced
> problems with KILL working properly on a very infrequent basis. I do not
> know if SQL2K5 exhibits the same behavior.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eWY3WMJbHHA.1240@.TK2MSFTNGP04.phx.gbl...
>