If you want to remove any data or any log file from a database then you can use the EMPTYFILE option with DBCC SHRINKFILE to first empty it and then remove it.
1) Make sure that there is at least one additional file in that filegroup, from where you are trying to remove a file. Also make sure that the remaining files in the filegroup have enough space for you to move the contents to them.
Use the following command to empty the file
DBCC SHRINKFILE (N'AdventureWorks_data2' , EMPTYFILE)
Then remove the particular file using the following command
ALTER DATABASE [AdventureWorks_new] REMOVE FILE [AdventureWorks_data2]
2) You can also use the GUI to do this. Right click on the database Go to Tasks > Shrink > Files. Then choose the filegroup and then the file and below pick the option to 'Empty file by migrating the data to other files in the same filegroup'. Then click OK. Later open the database properties pick the file that you want to delete and REMOVE it.
1) Make sure that there is at least one additional file in that filegroup, from where you are trying to remove a file. Also make sure that the remaining files in the filegroup have enough space for you to move the contents to them.
Use the following command to empty the file
DBCC SHRINKFILE (N'AdventureWorks_data2' , EMPTYFILE)
Then remove the particular file using the following command
ALTER DATABASE [AdventureWorks_new] REMOVE FILE [AdventureWorks_data2]
2) You can also use the GUI to do this. Right click on the database Go to Tasks > Shrink > Files. Then choose the filegroup and then the file and below pick the option to 'Empty file by migrating the data to other files in the same filegroup'. Then click OK. Later open the database properties pick the file that you want to delete and REMOVE it.
And you must take T log backup otherwise you will see the file (offline) when you query Sys.database_files.
ReplyDelete