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