Pausing SQL Server from transact SQL

We have a database that automatically restores at night from another server.
We use a kill all users job and place the database in single user mode.
Unfortunately there is an app on the network that will automatically
reconnect if it's spid is killed.
I'd like to be able to Pause the server from within transact SQL.
The only way I can see to do this is:
exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
Kill the spids >= 50
exec sp_dboption 'Database Name', 'dbo use only', true
exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
Is there a way of pausing without using xp_cmdshell?
PaulThis might work...
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE yourdb
GO
RESTORE ...
That way you should be the single_user before the application can reconnect.
--
HTH
Ryan Waight, MCDBA, MCSE
"Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> We have a database that automatically restores at night from another
server.
> We use a kill all users job and place the database in single user mode.
> Unfortunately there is an app on the network that will automatically
> reconnect if it's spid is killed.
> I'd like to be able to Pause the server from within transact SQL.
> The only way I can see to do this is:
> exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> Kill the spids >= 50
> exec sp_dboption 'Database Name', 'dbo use only', true
> exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> Is there a way of pausing without using xp_cmdshell?
> Paul
>|||Or set the db in restricted user mode, assuming that that nasty app doesn't
use a login that is a member of the db_owner, db_creator or sysadmin roles.
ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
Ryan, your code will not work. _no one_ can be in the database when you want
to restore it, not even the connection that is doing the restore, that
should be connected to a different database. You can do ALTER DATABASE...
and then RESTORE when you are connected to the master database and hope the
application won't be able to log back in between the statements, but that
won't always work.
--
Jacco Schalkwijk
SQL Server MVP
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> This might work...
> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
> That way you should be the single_user before the application can
reconnect.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > We have a database that automatically restores at night from another
> server.
> > We use a kill all users job and place the database in single user mode.
> > Unfortunately there is an app on the network that will automatically
> > reconnect if it's spid is killed.
> >
> > I'd like to be able to Pause the server from within transact SQL.
> > The only way I can see to do this is:
> >
> > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > Kill the spids >= 50
> > exec sp_dboption 'Database Name', 'dbo use only', true
> > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> >
> > Is there a way of pausing without using xp_cmdshell?
> >
> > Paul
> >
> >
>|||> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
>
Since a database can't be restored while it's in use, the database
context needs to be changed to some other database:
USE master
GO
RESTORE ...
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> This might work...
> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
> That way you should be the single_user before the application can
reconnect.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > We have a database that automatically restores at night from another
> server.
> > We use a kill all users job and place the database in single user
mode.
> > Unfortunately there is an app on the network that will automatically
> > reconnect if it's spid is killed.
> >
> > I'd like to be able to Pause the server from within transact SQL.
> > The only way I can see to do this is:
> >
> > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > Kill the spids >= 50
> > exec sp_dboption 'Database Name', 'dbo use only', true
> > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> >
> > Is there a way of pausing without using xp_cmdshell?
> >
> > Paul
> >
> >
>|||Apologies all. USE yourdb will of course use up the SINGLE_USER.
Should have read :-
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
RESTORE ...
--
HTH
Ryan Waight, MCDBA, MCSE
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uamRLnZjDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Or set the db in restricted user mode, assuming that that nasty app
doesn't
> use a login that is a member of the db_owner, db_creator or sysadmin
roles.
> ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> Ryan, your code will not work. _no one_ can be in the database when you
want
> to restore it, not even the connection that is doing the restore, that
> should be connected to a different database. You can do ALTER DATABASE...
> and then RESTORE when you are connected to the master database and hope
the
> application won't be able to log back in between the statements, but that
> won't always work.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> > This might work...
> >
> > ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > GO
> > USE yourdb
> > GO
> > RESTORE ...
> >
> > That way you should be the single_user before the application can
> reconnect.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> > news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > > We have a database that automatically restores at night from another
> > server.
> > > We use a kill all users job and place the database in single user
mode.
> > > Unfortunately there is an app on the network that will automatically
> > > reconnect if it's spid is killed.
> > >
> > > I'd like to be able to Pause the server from within transact SQL.
> > > The only way I can see to do this is:
> > >
> > > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > > Kill the spids >= 50
> > > exec sp_dboption 'Database Name', 'dbo use only', true
> > > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> > >
> > > Is there a way of pausing without using xp_cmdshell?
> > >
> > > Paul
> > >
> > >
> >
> >
>|||Thanks guys. Too busy looking at set dboption to remember alter database.
Paul
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uamRLnZjDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Or set the db in restricted user mode, assuming that that nasty app
doesn't
> use a login that is a member of the db_owner, db_creator or sysadmin
roles.
> ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> Ryan, your code will not work. _no one_ can be in the database when you
want
> to restore it, not even the connection that is doing the restore, that
> should be connected to a different database. You can do ALTER DATABASE...
> and then RESTORE when you are connected to the master database and hope
the
> application won't be able to log back in between the statements, but that
> won't always work.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> > This might work...
> >
> > ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > GO
> > USE yourdb
> > GO
> > RESTORE ...
> >
> > That way you should be the single_user before the application can
> reconnect.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> > news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > > We have a database that automatically restores at night from another
> > server.
> > > We use a kill all users job and place the database in single user
mode.
> > > Unfortunately there is an app on the network that will automatically
> > > reconnect if it's spid is killed.
> > >
> > > I'd like to be able to Pause the server from within transact SQL.
> > > The only way I can see to do this is:
> > >
> > > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > > Kill the spids >= 50
> > > exec sp_dboption 'Database Name', 'dbo use only', true
> > > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> > >
> > > Is there a way of pausing without using xp_cmdshell?
> > >
> > > Paul
> > >
> > >
> >
> >
>