Set filegroup to read only sql 2005 2

[mflancour]

How is this done?

 

[SQLDenis]

ALTER DATABASE DataBaseName
MODIFY FILEGROUP FileGroupName ReadOnly;


Example:

USE master
go

--Create New DB For Testing
CREATE DATABASE testFilegroup
go


--Create FileGroup
ALTER DATABASE testFilegroup
ADD FILEGROUP Test1FG1;

--Add file to fileGroup
ALTER DATABASE testFilegroup
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'c:\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
    
)
TO FILEGROUP Test1FG1

--Set FileGroup ReadOnly
ALTER DATABASE testFilegroup
MODIFY FILEGROUP Test1FG1 ReadOnly;

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

You can also change it from SSMS, right click on the database and select properties then filegroups...

However, I prefer to do it like in Denis's example. It is always better to make changes with the TSQL command. That way you know what is happening to the database.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

and you can check with this

SELECT is_read_only
FROM sys.filegroups
WHERE name = 'Test1FG1'


of course your file group name would be different

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mflancour]

Thanks, nice and easy. Do you know about compacting historical filegroups as well?

 

[SQLDenis]

Just move them to a compressed folder..read..

http://msdn2.microsoft.com/en-us/library/ms190257.aspx

BTW....
The keyword READONLY will be removed in a future version of Microsoft SQL Server. Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. Use [red]READ_ONLY[/red] instead.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mflancour]

I'm missing something. I read through the link and I'm afraid I don't really know anything about windows compression.
"
To compress a file, or verify the compression status of a file, use Windows COMPACT command or Windows Explorer. For more information, see the Windows documentation.
"
What is it telling me to do here?

 

[mflancour]

ah, nevermind found it I beleive..porperties-advanced-compress