2

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
  • I'm not sure I understand why you would need to create a new filegroup to move the data. What is wrong with the traditional and simple backup on source server, and restore on destination server? – Thomas Stringer Sep 3 '14 at 11:38
  • If you have filegroups you need to specify filegroup clause in restore query which is missing from your query. Check example mentioned in link:technet.microsoft.com/en-us/library/ms178099(v=sql.105).aspx – Shanky Sep 3 '14 at 11:38
  • Was planning to create a new Filegroup to store all the data on. Separate from system data. I have read this is good practice? – K09 Sep 3 '14 at 12:03
1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.