Sql-server – Database Migration – Create New Filegroup and Move Objects

filegroupsmigrationsql server

We have built a new SQL Server Database server instance. I am migrating our databases from existing instance to the new server instance using Backup and Restore.

One existing database has a single FILEGROUP. Is it possible to create a new FILEGROUP and move the data to this?

I cannot move the existing data file to the new FILEGROUP. So is my only option to manually script all Database Objects to the new FILEGROUP? Can I then delete the now empty FILE after moving the objects?

Restore Script:

;RESTORE DATABASE [TestDB] FROM DISK = 'I:\testDB.bak' 
    WITH REPLACE, FILE = 1, NORECOVERY, STATS=10, 
    MOVE 'TestData' TO 'H:\Test.mdf', 
    MOVE 'Test_log' TO 'E:\Test_log.ldf'
;RESTORE DATABASE [TestDB] WITH RECOVERY

Best Answer

This can be done if you have clustered indexes (either as the primary key constraint or on their own) on the tables that you wish to move.

So restore the database, add the new filegroup and then...

For primary keys:-

 ALTER TABLE [SCHEMA].[TABLE NAME] DROP CONSTRAINT [CONSTRAINT NAME]

 ALTER TABLE [SCHEMA].[TABLE NAME] ADD PRIMARY KEY CLUSTERED 
 (
   [Column]  ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEW FILEGROUP]
 GO

For clustered indexes:-

CREATE CLUSTERED INDEX [CONSTRAINT NAME] ON [SCHEMA].[TABLE NAME]
(
    [Column] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEW FILEGROUP]
GO

Please be warned that this may take a while to do on a VLDB!

Related Question