Preventing automatic startup of database
-
How do I prevent a database from starting up automatically when the SQL server instance is started? I want to manually be able to attach/detach the database from the SQL server instance, and hence I do not want it to be started when my PC boots.
Thanks!
-
Change database option 'AUTOCLOSE' to true.
For syntax, check BOL for alter database
-
The BOL says that this flag when set to true closes the database when the last user disconnects. What I want is however different. I do not want the database to be closed automatically. I want the database NOT to be started automatically when the SQL server instance to which it was attached is started, like on PC boot. I want to explicitly attach the database to the instance when I feel the need to do so. SQL Server must be saving this information somewhere as to what all databases it needs to start on startup. Maybe I can modify that to prevent my database from automatically starting up.
-
User database will be closed when last user disconnect. It applies to start up as well, if no connection, database stay closed until otherwise.
If you do not want the sql instance to start when pc reboot, then set mssqlserver service to manual in Startup Type.
-
Maybe use this to put them offline when sqlserver is started
:(you must be sysadmin to install this)
use master
create procedure sp_MyOwn_SetUserDbOffline
as
set nocount on
declare @SqlCmd varchar(1000)
declare c1 cursor for
select ' alter database ' + name + ' set offline with NO_WAIT '
from master..sysdatabases
where name not in ('master','msdb','tempdb','model','whatever_db_you_want_to_exclude')
and convert(binary(2),status ) & 512 <> 512
order by name
for read only
open c1
FETCH NEXT FROM c1
INTO @SqlCmd
WHILE @@FETCH_STATUS = 0
BEGIN
execute (@SqlCmd)
FETCH NEXT FROM c1
INTO @SqlCmd
END
CLOSE c1
DEALLOCATE c1
Go
--To create a startup stored procedure, you must be logged in as a member of the sysadmin fixed server role
-- and create the stored procedure in the master database.
-- Use sp_procoption to:
-- Designate an existing stored procedure as a startup procedure.
-- Stop a procedure from executing at SQL Server startup.
exec sp_procoption @ProcName = 'sp_MyOwn_SetUserDbOffline'
, @OptionName = 'startup'
, @OptionValue = 'on' -- or 'off'
-- Activate startup procedures
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'scan for startup procs', 1
GO
RECONFIGURE
GO
Print ' Now stop/start sqlserver '
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply