Making a database read-only? 2

[katgirl]

Hi,

What are the steps for making a production database, one that has live clients connected to it, read-only?

It's okay if we disconnect clients... but I want to make sure it's done properly.

Thank you

 

[mrdenny]

You need to disconnect all the users from the database and then use the ALTER DATABASE command to change it's status.

use master
declare @spid varchar(10)
declare @Loginame varchar(255)
declare @Database varchar(200)
set @Database = '{YourDatabase}'
declare cur CURSOR for select spid, loginame
from sysprocesses
where dbid = (select dbid from sysdatabases where name = @Database)
open cur
fetch next from cur into @spid, @Loginame
while @@fetch_status = 0
begin
	print convert(varchar(100), 'Killing ' + @spid + ': ' + @Loginame)
	exec ('kill ' + @spid)
	fetch next from cur into @spid, @Loginame
end
close cur 
deallocate cur
go
ALTER DATABASE {YourDatabase}
set READ_ONLY
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

thanks much

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

ok - dumb question - but what is the command to take database OUT of read only?

 

[mutley1]

ALTER DATABASE DBName
set READ_WRITE
GO

Rgds,

M.