change file table file stream data folder faces error The container can not be dropped

Question

I want to change file database file stream folder from Drive D to Drive F. I successfully transferred data to new folder but when I want to Remove old file stream folder I face this error: "The container can not be dropped because changes exist that require a log backup. Take a log backup and then retry the ALTER DATABASE operation." When I want to get transaction log, It encounter this error: you should enable filestream on this instance, while filestream is enabled. This my code:

ALTER DATABASE MyDatabase
REMOVE FILE MyDatabase_Files;  
GO 

Thanks for any helps.

Answer 1

As the message suggests, you need to backup the transaction log in order to move on to the next step. You can do this inside SSMS with a couple of clicks or, if you prefer writing SQL, you can do this:

BACKUP LOG MyDatabase  
   TO MyDatabase_TXN_log1;  
GO

Once this is completed the requisite ALTER DATABASE statements can be run before restarting the SQL Server instance 👍🏻