I am trying to create partitions on a table of one of my test databases called WingTipToys2012.
Following the recomendations of many sources, I created the three file groups called FgOne, FgTwo and FgThree, as follows:
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgOne;
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgTwo;
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgThree;
Then, I created one additional file for each file group, as follows:
ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file1',
filename='C:\...\file1.ndf', size=5MB) TO FILEGROUP FgOne;
ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file2',
filename='C:\...\file2.ndf', size=5MB) TO FILEGROUP FgTwo;
ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file3',
filename='C:\...\file3.ndf', size=5MB) TO FILEGROUP FgThree;
Then I created the partition function, as follows:
CREATE PARTITION FUNCTION WTPartFunction (int)
AS RANGE LEFT FOR VALUES(30, 60);
The problem comes when trying to create the partition scheme. To do so, I execute the following command:
CREATE PARTITION SCHEME WTPartScheme
AS PARTITION WTPartFunction TO (FgOne, FgTwo, FgThree);
But after executing the instructions above, I get the following error:
Msg 208, Level 16, State 58, Line 1
Invalid object name 'FgOne'.
It looks like the file group FgOne is not recognized, but I know it's there. Funny enough, SQL server also knows it is there. If I try to create another file group with the same name:
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgOne;
I get the following error:
Msg 5035, Level 16, State 1, Line 1
Filegroup 'FgOne' already exists in this database. Specify a different name or remove the conflicting filegroup if it is empty.
I am not sure what I am doing wrong. Can anyone help?
