Restore DB automatically

I created script that would restore db automatically, but i have problem
running ...
i get error
Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
HOW do i modify this script to kill all users who use EAGLE database before
restoring database ?
USE EAGLE
GO
RESTORE DATABASE EAGLE
FROM DISK = 'E:\DBbackup\dailybuild.bck'
WITH
MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
,REPLACE
GOMikus
Lookup SINGLE USER MODE in the BOL.
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
>I created script that would restore db automatically, but i have problem
> running ...
> i get error
> Server: Msg 3101, Level 16, State 1, Line 2
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
> HOW do i modify this script to kill all users who use EAGLE database
> before
> restoring database ?
> USE EAGLE
> GO
> RESTORE DATABASE EAGLE
> FROM DISK = 'E:\DBbackup\dailybuild.bck'
> WITH
> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> ,REPLACE
> GO|||> USE EAGLE
> GO
> RESTORE DATABASE EAGLE
Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
having the ROLLBACK option to get rid of other users.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
>I created script that would restore db automatically, but i have problem
> running ...
> i get error
> Server: Msg 3101, Level 16, State 1, Line 2
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
> HOW do i modify this script to kill all users who use EAGLE database before
> restoring database ?
> USE EAGLE
> GO
> RESTORE DATABASE EAGLE
> FROM DISK = 'E:\DBbackup\dailybuild.bck'
> WITH
> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> ,REPLACE
> GO|||WHERE do i put ALTER DATABASE clause ?
this way ?
USE MASTER
GO
ALTER DATABASE
RESTORE DATABASE EAGLE
FROM DISK = 'E:\DBbackup\dailybuild.bck'
WITH
MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
,REPLACE
GO
"Tibor Karaszi" wrote:
> > USE EAGLE
> > GO
> >
> > RESTORE DATABASE EAGLE
> Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
> master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
> having the ROLLBACK option to get rid of other users.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
> >I created script that would restore db automatically, but i have problem
> > running ...
> > i get error
> >
> > Server: Msg 3101, Level 16, State 1, Line 2
> > Exclusive access could not be obtained because the database is in use.
> > Server: Msg 3013, Level 16, State 1, Line 2
> > RESTORE DATABASE is terminating abnormally.
> >
> > HOW do i modify this script to kill all users who use EAGLE database before
> > restoring database ?
> >
> > USE EAGLE
> > GO
> >
> > RESTORE DATABASE EAGLE
> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> > WITH
> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> > ,REPLACE
> > GO
>
>|||You need to read Books Online to see how to execute ALTER DATABASE (SINGLE USER, ROLBACK etc). But,
yes, you put it before your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
> WHERE do i put ALTER DATABASE clause ?
> this way ?
> USE MASTER
> GO
> ALTER DATABASE
> RESTORE DATABASE EAGLE
> FROM DISK = 'E:\DBbackup\dailybuild.bck'
> WITH
> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> ,REPLACE
> GO
> "Tibor Karaszi" wrote:
>> > USE EAGLE
>> > GO
>> >
>> > RESTORE DATABASE EAGLE
>> Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
>> master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
>> having the ROLLBACK option to get rid of other users.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
>> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
>> >I created script that would restore db automatically, but i have problem
>> > running ...
>> > i get error
>> >
>> > Server: Msg 3101, Level 16, State 1, Line 2
>> > Exclusive access could not be obtained because the database is in use.
>> > Server: Msg 3013, Level 16, State 1, Line 2
>> > RESTORE DATABASE is terminating abnormally.
>> >
>> > HOW do i modify this script to kill all users who use EAGLE database before
>> > restoring database ?
>> >
>> > USE EAGLE
>> > GO
>> >
>> > RESTORE DATABASE EAGLE
>> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
>> > WITH
>> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
>> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
>> > ,REPLACE
>> > GO
>>|||To automate the restore, I usually kill connected users then run the restore.
I have a stored procedure KillConn in master database which I always call
whenever I need to automate database restore. KillConn accepts a parameter
which is database name.
EXEC Master.dbo.KillConn 'Eagle'
go
RESTORE DATABASE EAGLE
FROM DISK = 'E:\DBbackup\dailybuild.bck'
...................................
Here is the extract of KillConn stored procedure:
CREATE PROCEDURE KillConn (@.db varchar(30))
AS
DECLARE @.ps VARCHAR(255)
DECLARE @.id smallint
DECLARE c CURSOR FOR
SELECT
DISTINCT
spid
FROM
master..sysprocesses
WHERE
dbid
IN
(SELECT
dbid
FROM
master..sysdatabases
WHERE
name
IN
(@.db))
OPEN c
FETCH NEXT FROM c INTO @.id
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT @.ps = 'KILL ' + CONVERT(VARCHAR(8), @.id)
EXEC(@.ps)
FETCH NEXT FROM c INTO @.id
END
CLOSE c
DEALLOCATE c
GO
"Tibor Karaszi" wrote:
> You need to read Books Online to see how to execute ALTER DATABASE (SINGLE USER, ROLBACK etc). But,
> yes, you put it before your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
> > WHERE do i put ALTER DATABASE clause ?
> >
> > this way ?
> >
> > USE MASTER
> > GO
> >
> > ALTER DATABASE
> > RESTORE DATABASE EAGLE
> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> > WITH
> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> > ,REPLACE
> > GO
> >
> > "Tibor Karaszi" wrote:
> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >>
> >> Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
> >> master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
> >> having the ROLLBACK option to get rid of other users.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> >> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
> >> >I created script that would restore db automatically, but i have problem
> >> > running ...
> >> > i get error
> >> >
> >> > Server: Msg 3101, Level 16, State 1, Line 2
> >> > Exclusive access could not be obtained because the database is in use.
> >> > Server: Msg 3013, Level 16, State 1, Line 2
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> > HOW do i modify this script to kill all users who use EAGLE database before
> >> > restoring database ?
> >> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> >> > WITH
> >> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> >> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> >> > ,REPLACE
> >> > GO
> >>
> >>
> >>
>
>|||This was not working ...
USE MASTER
GO
ALTER DATABASE EAGLE
RESTORE DATABASE EAGLE
FROM DISK = 'E:\DBbackup\dailybuild.bck'
WITH
MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
,REPLACE
GO
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'RESTORE'.
i couldn't really find any info in server books online regarding SINGLE USER
MODE ?
"Tibor Karaszi" wrote:
> You need to read Books Online to see how to execute ALTER DATABASE (SINGLE USER, ROLBACK etc). But,
> yes, you put it before your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
> > WHERE do i put ALTER DATABASE clause ?
> >
> > this way ?
> >
> > USE MASTER
> > GO
> >
> > ALTER DATABASE
> > RESTORE DATABASE EAGLE
> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> > WITH
> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> > ,REPLACE
> > GO
> >
> > "Tibor Karaszi" wrote:
> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >>
> >> Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
> >> master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
> >> having the ROLLBACK option to get rid of other users.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> >> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
> >> >I created script that would restore db automatically, but i have problem
> >> > running ...
> >> > i get error
> >> >
> >> > Server: Msg 3101, Level 16, State 1, Line 2
> >> > Exclusive access could not be obtained because the database is in use.
> >> > Server: Msg 3013, Level 16, State 1, Line 2
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> > HOW do i modify this script to kill all users who use EAGLE database before
> >> > restoring database ?
> >> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> >> > WITH
> >> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> >> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> >> > ,REPLACE
> >> > GO
> >>
> >>
> >>
>
>|||Hi,
The exact command is:-
ALTER DATABASE EAGLE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Now issue the RESTORE database command. After the restore issue the below
command to make database multi user.
go
ALTER DATABASE EAGLE SET MULTI_USER
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:er0BvN6sFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You need to read Books Online to see how to execute ALTER DATABASE (SINGLE
> USER, ROLBACK etc). But, yes, you put it before your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
>> WHERE do i put ALTER DATABASE clause ?
>> this way ?
>> USE MASTER
>> GO
>> ALTER DATABASE
>> RESTORE DATABASE EAGLE
>> FROM DISK = 'E:\DBbackup\dailybuild.bck'
>> WITH
>> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
>> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
>> ,REPLACE
>> GO
>> "Tibor Karaszi" wrote:
>> > USE EAGLE
>> > GO
>> >
>> > RESTORE DATABASE EAGLE
>> Above will *not* work. You enter the eagle database yourself, and block
>> the restore yourself. USE
>> master and then restore instead. Also, use ALTER DATABASE to se the
>> database in single user mode,
>> having the ROLLBACK option to get rid of other users.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
>> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
>> >I created script that would restore db automatically, but i have
>> >problem
>> > running ...
>> > i get error
>> >
>> > Server: Msg 3101, Level 16, State 1, Line 2
>> > Exclusive access could not be obtained because the database is in use.
>> > Server: Msg 3013, Level 16, State 1, Line 2
>> > RESTORE DATABASE is terminating abnormally.
>> >
>> > HOW do i modify this script to kill all users who use EAGLE database
>> > before
>> > restoring database ?
>> >
>> > USE EAGLE
>> > GO
>> >
>> > RESTORE DATABASE EAGLE
>> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
>> > WITH
>> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
>> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
>> > ,REPLACE
>> > GO
>>
>|||Thanks a lot HAri
"Hari Prasad" wrote:
> Hi,
> The exact command is:-
> ALTER DATABASE EAGLE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> Now issue the RESTORE database command. After the restore issue the below
> command to make database multi user.
> go
> ALTER DATABASE EAGLE SET MULTI_USER
> Thanks
> Hari
> SQL Server MVP
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:er0BvN6sFHA.2008@.TK2MSFTNGP10.phx.gbl...
> > You need to read Books Online to see how to execute ALTER DATABASE (SINGLE
> > USER, ROLBACK etc). But, yes, you put it before your RESTORE command.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> > news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
> >> WHERE do i put ALTER DATABASE clause ?
> >>
> >> this way ?
> >>
> >> USE MASTER
> >> GO
> >>
> >> ALTER DATABASE
> >> RESTORE DATABASE EAGLE
> >> FROM DISK = 'E:\DBbackup\dailybuild.bck'
> >> WITH
> >> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> >> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> >> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> >> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> >> ,REPLACE
> >> GO
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >>
> >> Above will *not* work. You enter the eagle database yourself, and block
> >> the restore yourself. USE
> >> master and then restore instead. Also, use ALTER DATABASE to se the
> >> database in single user mode,
> >> having the ROLLBACK option to get rid of other users.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> >> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
> >> >I created script that would restore db automatically, but i have
> >> >problem
> >> > running ...
> >> > i get error
> >> >
> >> > Server: Msg 3101, Level 16, State 1, Line 2
> >> > Exclusive access could not be obtained because the database is in use.
> >> > Server: Msg 3013, Level 16, State 1, Line 2
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> > HOW do i modify this script to kill all users who use EAGLE database
> >> > before
> >> > restoring database ?
> >> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> >> > WITH
> >> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> >> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> >> > ,REPLACE
> >> > GO
> >>
> >>
> >>
> >
> >
>
>