Remove Files From Databases

Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:



1- Drop the table - OK

2- Drop the filegroup - OK

3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.



When I query a catalog view with this query:

Select * From Sys.Database_Files



I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.





This is the code I use:





Use [TESTING ]

Go



-- Add a FileGroup to the Database

Alter Database TESTING Add FileGroup FG01

Go



-- Add a file to a FileGroup

Alter Database TESTING Add File (

NAME = TESTING_DATA01,

FILENAME = 'D:Sql ServerDataTesting_Data01.ndf',

SIZE = 1 MB,

MAXSIZE = 10 MB,

FILEGROWTH = 1 MB

) To FileGroup FG01

Go



-- Create the table using a specific FileGroup

Create Table TABLE1 (

Id Int Not Null,

FirstName VarChar(30) Not Null,

LastName VarChar(30) Not Null,

BirthDate SmallDateTime

)

On FG01



So far everything works ok, but in the next code there is an error:



-- Delete the table

Drop Table TABLE1

Go



-- Remove a FileGroup from the Database

Alter Database TESTING Remove FileGroup FG01

Go



-- Try to remove the file

Alter Database TESTING Remove File TESTING_DATA01



When I try to remove the file there is an error:

"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"