Wednesday, May 4, 2011

Read-only tables in MS SQL

here is description how to make readonly table[s] in MS SQL using filegroups:
#1 Create new filegroup;
#2 Add file into the filegroup;
#3 Create new of move existing table[s] into the filegroup;
#4 Change DB mode to SINGLE_USER;
#5 Modify filegroup to be readonly;
#6 Change DB mode to MULTI_USER.

notes:
Table can't be moved into other filegroup using "Alter table".
but you can use "CREATE CLUSTERED INDEX" instead, table will be automatically moved into the new filegroup.

examples:
ALTER DATABASE db_name ADD FILEGROUP FG_READONLY;

    ALTER DATABASE db_name ADD FILE( 
        NAME = 'logic_name_here', 
        FILENAME = 'os_file_name_here', 
        SIZE = 10, 
        FILEGROWTH = 10%,
        MAXSIZE = UNLIMITED ) TO FILEGROUP FG_READONLY;
    GO

    CREATE CLUSTERED INDEX IX_Table_Temp ON dbo.Table(ID) ON FG_READONLY
    GO 

    DROP INDEX IX_Table_Temp ON dbo.Table
    GO

    ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO
    
    ALTER DATABASE db_name MODIFY FILEGROUP FG_READONLY READONLY;
    GO
    
    ALTER DATABASE db_name SET MULTI_USER;
    GO

links:
Filegroups in SQL Server 2005
How to make a table Read Only in SQL Server

No comments:

Post a Comment