DETACH/ATTACH FILEGROUP | Microsoft Connect

DETACH/ATTACH FILEGROUP by Mattias Lind, Sweden

Closed

as Won't Fix Help for as Won't Fix

Type:	Suggestion
ID:	770846
Opened:	11/12/2012 1:35:43 PM
Access Restriction:	Public

Workaround(s)

view

Detaching a filegroup with tables and it's indexes from one database to attach them to another database would be a great feature of SQL Server.

Details (expand)

English

SQL Engine

CREATE TABLE foo
(CIX_Key int NOT NULL, colxxx datatype...)
ON RemovableFilegroup

INSERT INTO foo
SELECT * FROM table

CREATE CLUSTERED INDEX cix_foo ON foo(CIX_KEY)

CREATE NONCLUSTERED INDEX nix_foo_1 ON foo(some_col)

ALTER DATABASE whatever
MODIFY FILEGROUP RemovableFilegroup DETACH

ALTER DATABASE anotherdb
MODIFY FILEGROUP RevovableFilegroup ATTACH
FILES(filedef),...

Improved Performance

With this a loading of a table as rebuilding of its indexes could be done outside of the warehouse, and then attached to the warehouse, and with partition switching "added" to a table without the need of rebuiling any indexes.

0 attachments

This is an idea that we've looked at, and while it seems straightforward on the surface, in reality it would be a very large undertaking. Consistency in the database is governed by the log sequence numbers in the transaction log as well as references to LSNs throughout the data files.
If you take a filegroup and drop it into another database, the chance that the new filegroup's LSNs will make any sense at all to the database it has been added to are essentially nil.
In order to accomplish this and still guarantee consistency in all cases, we would need to have independent log streams for filegroups, which has its own problems when considering database-wide consistency. not to mention that it is a massive change to the architecture.