#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