March 17, 2005 at 3:49 am
Hi
On a SQL instance with many databases, is it possible to prevent 1 database starting (e.g. stay offline), but have all the others start when the SQL instance is restarted?
e.g. Instance has DB1, DB2, DB3 and DB4. When the instance restarts, have DB1,2 and 3 start, but leave DB4 down until we're ready to manually start it?
Thanks in advance
Andy
March 17, 2005 at 4:44 am
Use set sp_dboption @dbname ='db',@optname ='offline',@optvalue =true
To manually start right click database in enterprise manager and bring it back to online or use sp_dboption
You may also use ALTER DATABASE command.
But remember no users must be connected
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
March 17, 2005 at 10:09 am
But if the server crashed when the database was up, wouldn't the database just come back up when the server was restarted?
March 17, 2005 at 9:21 pm
I don't think so. Because the status field in the sysdatabase field will be set when we specify this option. BOL says
| status | Bit mask that shows whether a database is read-only, off line, designated for use by a single user only, and so on. Some of the bits can be set by a database owner using the ALTER DATABASE command; others are set internally. (The SQL Server documentation shows most of the possible bit-mask values.) |
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
March 18, 2005 at 2:25 am
Thanks Helen, I'll give it a go
March 18, 2005 at 2:47 am
Helen
I took the db offline (using sp_dboption), then brought it online using EM and restarted sql, the db came up online, which is not what I want unfortunately. I want offline to be its default state.
I also tested with single-user mode, but the same thing happens. It looks like the db will always come back up in the state that it was in before it went down.
Thanks for your suggestion.
Andy
March 18, 2005 at 9:14 am
You may be able to execute a procedure at startup to do what you want. From Books Online (CREATE PROCEDURE):
Automatically Executing Stored Procedures
One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.
There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one connection.
Execution of the stored procedures starts when the last database is recovered at startup. To skip launching these stored procedures, specify trace flag 4022 as a startup parameter. If you start SQL Server with minimal configuration (using the -f flag), the startup stored procedures are not executed. For more information, see Trace Flags.
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.
View a list of all procedures that execute at SQL Server startup.
March 18, 2005 at 9:21 am
Andy,
I don't think that's possible. But below are couple of options.
1) You can move your data files to a different location when your server crashes. Hence the database will come back in suspect mode.
2) You can run a startup procedure to take your database offline.
can you please explain why you would like the database to be offline and probably someone might have better ideas ?
April 20, 2005 at 2:38 am
Thankyou for your replies. I'll try the startup stored procedure option
Andy
April 20, 2005 at 1:07 pm
If it's a question of just wanting the DB to remain static, why not just use the Read-Only option and remove any user access? The Read-Only attribute will persist after a reboot.
Hope this helps.
My hovercraft is full of eels.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply