force process termination on restore

Sorry if this is a dumb question, but we have several production databases
that get automatically backed up, Rar'd, FTP'd internally and then restored.
Problem is, if someone has a process open against the restored database (IE:
a report is being run or something) the restore fails.
There must be a way of making the restore wait until a process is finsihed
or to force a termination of any process (altho probably not a good practice)
that is running.
Basically, I'm just looking for a better backup/restore process. Any ideas?
Hi,
There are 2 approaches...
1. If you are restoring in non production then you could disconnect all the
users using below command
ALTER database <dbname> set single_user with rollback immediate
go
Restore database
go
ALTER database <dbname> set multi_user
2. Write a loop sort to check the sysprocesses table for this particular
database for any running process . If there
is any running processes
start1:
if (select status from master..sysprocesses where status='runnable' and
db_name(dbid)='pubs')>1
waitfor delay(......) -- set 1 minutedelay
goto start1
else
kill all the users connected
restore the database
I recommend the first method for development and test server databases.
Thanks
Hari
MCDBA
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?
|||Jason,
I had the same problem and wrote the following code to kill the users
connected to the database when I wanted to do to restore. Would this help
you?
***************************************
use master
go
select p.spid, d.name
into #temp_who
from sysprocesses p
join sysdatabases d on
p.dbid = d.dbid
where d.name = 'PUBS'
DECLARE @.objName int, @.sql NVARCHAR(50)
SET @.objName = 0
WHILE @.objName IS NOT NULL
BEGIN
SELECT @.objName = MIN(spid)
FROM #temp_who
WHERE spid > @.objName
IF @.objName IS NOT NULL
BEGIN
--Insert code to do stuff here.
set @.sql = 'kill ' + cast(@.objName as nvarchar)
exec sp_executesql @.sql
END
END
Good luck.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?
sql

force process termination on restore

Sorry if this is a dumb question, but we have several production databases
that get automatically backed up, Rar'd, FTP'd internally and then restored.
Problem is, if someone has a process open against the restored database (IE:
a report is being run or something) the restore fails.
There must be a way of making the restore wait until a process is finsihed
or to force a termination of any process (altho probably not a good practice
)
that is running.
Basically, I'm just looking for a better backup/restore process. Any ideas
?Hi,
There are 2 approaches...
1. If you are restoring in non production then you could disconnect all the
users using below command
ALTER database <dbname> set single_user with rollback immediate
go
Restore database
go
ALTER database <dbname> set multi_user
2. Write a loop sort to check the sysprocesses table for this particular
database for any running process . If there
is any running processes
start1:
if (select status from master..sysprocesses where status='runnable' and
db_name(dbid)='pubs')>1
waitfor delay(......) -- set 1 minutedelay
goto start1
else
kill all the users connected
restore the database
I recommend the first method for development and test server databases.
Thanks
Hari
MCDBA
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?|||Jason,
I had the same problem and wrote the following code to kill the users
connected to the database when I wanted to do to restore. Would this help
you?
***************************************
use master
go
select p.spid, d.name
into #temp_who
from sysprocesses p
join sysdatabases d on
p.dbid = d.dbid
where d.name = 'PUBS'
DECLARE @.objName int, @.sql NVARCHAR(50)
SET @.objName = 0
WHILE @.objName IS NOT NULL
BEGIN
SELECT @.objName = MIN(spid)
FROM #temp_who
WHERE spid > @.objName
IF @.objName IS NOT NULL
BEGIN
--Insert code to do stuff here.
set @.sql = 'kill ' + cast(@.objName as nvarchar)
exec sp_executesql @.sql
END
END
Good luck.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?

For a newbie in SQL Server 2005: How to activate a mirroring database and Mirroring is it

Hello,
First, excuse me for my poor english.
I have a principal SQL Server 2005 in production and a
SQL server 2005 which will be used as a backup server.
I have a database in activity on the server of production and I made a
setting out of mirror of this one on the backup-server. For that, I
have to make a backup of the principal database and make a restoration of
that database in Mode
"WITH NO RECOVERY" on the backu-serverp. Then I made the setting
out of mirror which functioned. My problem is that I do not manage to
reach the database mirror because this one is not-operational (obligatory
for a setting out of mirror). I would like to know if it is
possible to reach the base to know if I find all my data up to date
. The setting out of mirror has for me a goal of backup. In the
event of crash landing of my principal SQL Server, how to make my mirror
database operational and activates so that my users can working normally? Is
what the
setting out of mirror is appropriate for my use knowing that I would
like to have to as have an access to the mirror database at the same time
as the principal database or would be necessary ' it as I rather use "the
sending of newspapers of transactions" (function that I really don't know)
or anything else ?
Thank you by advance for your invaluable assistance.
BrunoOnly one database is accessible at a certain point in time (the principal). The other (mirror) is in
a constant restoring state. If the principal server "crashes", you have basically two options to
make the mirror the principal:
Automatic, by having a 3:rd SQL Server having the witness role.
Manual, by executing the appropriate ALTER DATABASE SET PARTUNER command
There is plenty of planning information on mirroring in Books Online. Just make sure you have
updated your Books Online to the most recent version.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bruno" <lodoss007@.yahoo.fr> wrote in message news:OKBvTYPmGHA.1912@.TK2MSFTNGP02.phx.gbl...
> Hello,
> First, excuse me for my poor english.
> I have a principal SQL Server 2005 in production and a
> SQL server 2005 which will be used as a backup server.
> I have a database in activity on the server of production and I made a
> setting out of mirror of this one on the backup-server. For that, I
> have to make a backup of the principal database and make a restoration of that database in Mode
> "WITH NO RECOVERY" on the backu-serverp. Then I made the setting
> out of mirror which functioned. My problem is that I do not manage to
> reach the database mirror because this one is not-operational (obligatory for a setting out of
> mirror). I would like to know if it is
> possible to reach the base to know if I find all my data up to date
> . The setting out of mirror has for me a goal of backup. In the
> event of crash landing of my principal SQL Server, how to make my mirror
> database operational and activates so that my users can working normally? Is what the
> setting out of mirror is appropriate for my use knowing that I would
> like to have to as have an access to the mirror database at the same time
> as the principal database or would be necessary ' it as I rather use "the sending of newspapers of
> transactions" (function that I really don't know) or anything else ?
> Thank you by advance for your invaluable assistance.
> Bruno
>|||Thanks a lot for your advice
Bruno
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a écrit
dans le message de news: %23iS84lSmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Only one database is accessible at a certain point in time (the
> principal). The other (mirror) is in a constant restoring state. If the
> principal server "crashes", you have basically two options to make the
> mirror the principal:
> Automatic, by having a 3:rd SQL Server having the witness role.
> Manual, by executing the appropriate ALTER DATABASE SET PARTUNER command
> There is plenty of planning information on mirroring in Books Online. Just
> make sure you have updated your Books Online to the most recent version.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bruno" <lodoss007@.yahoo.fr> wrote in message
> news:OKBvTYPmGHA.1912@.TK2MSFTNGP02.phx.gbl...
>> Hello,
>> First, excuse me for my poor english.
>> I have a principal SQL Server 2005 in production and a
>> SQL server 2005 which will be used as a backup server.
>> I have a database in activity on the server of production and I made a
>> setting out of mirror of this one on the backup-server. For that, I
>> have to make a backup of the principal database and make a restoration of
>> that database in Mode
>> "WITH NO RECOVERY" on the backu-serverp. Then I made the setting
>> out of mirror which functioned. My problem is that I do not manage to
>> reach the database mirror because this one is not-operational (obligatory
>> for a setting out of mirror). I would like to know if it is
>> possible to reach the base to know if I find all my data up to date
>> . The setting out of mirror has for me a goal of backup. In the
>> event of crash landing of my principal SQL Server, how to make my mirror
>> database operational and activates so that my users can working normally?
>> Is what the
>> setting out of mirror is appropriate for my use knowing that I would
>> like to have to as have an access to the mirror database at the same time
>> as the principal database or would be necessary ' it as I rather use "the
>> sending of newspapers of transactions" (function that I really don't
>> know) or anything else ?
>> Thank you by advance for your invaluable assistance.
>> Bruno
>>
>