November 17, 2021 at 6:05 am
hi
somebody please help with removing filestream filegroup.
i am getting below error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for DataFile 'NTAFSDB_NTAFLOWMEMORYSTORAGE'. (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46041.41+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+DataFile&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot drop the last memory-optimized container 'NTAFSDB_NTAFLOWMEMORYSTORAGE'. (Microsoft SQL Server, Error: 41802)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2000&EvtSrc=MSSQLServer&EvtID=41802&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Below are the steps of it
USE [master]
GO
CREATE DATABASE [NTAFSDB_NEW] ON
( FILENAME = N'\\amazontesteceg5mbm.core.cvent.org\sqlnta-001.Data\MSSQL15.SQLNTA\MSSQL\DATA\NTAFSDB.mdf' ),
( FILENAME = N'\\amazontestmoaz6oef.core.cvent.org\sqlnta-001.Log\MSSQL15.SQLNTA\MSSQL\Log\NTAFSDB_log.ldf' ),
( FILENAME = N'\\amazontesteceg5mbm.core.cvent.org\sqlnta-001.Data\MSSQL15.SQLNTA\MSSQL\DATA\NTAFSDB_NTAFLOWSTORAGE.mdf' ),
( FILENAME = N'\\amazontestmoaz6oef.core.cvent.org\sqlnta-001.Log\MSSQL15.SQLNTA\MSSQL\Log\NTAFSDB_log2.ldf' )
FOR ATTACH
GO
alter database [NTAFSDB_NEW] modify file (name='NTAFSDB' , filename='\\amazontesteceg5mbm.core.cvent.org\sqlnta-001.Data\MSSQL15.SQLNTA\MSSQL\DATA\NTAFSDB.mdf')
alter database [NTAFSDB_NEW] modify file (name='NTAFSDB_log' , filename='\\amazontestmoaz6oef.core.cvent.org\sqlnta-001.Log\MSSQL15.SQLNTA\MSSQL\Log\NTAFSDB_log.ldf')
alter database [NTAFSDB_NEW] modify file (name='NTAFSDB_NTAFLOWSTORAGE' , filename='\\amazontesteceg5mbm.core.cvent.org\sqlnta-001.Data\MSSQL15.SQLNTA\MSSQL\DATA\NTAFSDB_NTAFLOWSTORAGE.mdf')
alter database [NTAFSDB_NEW] modify file (name='NTAFSDB_log2' , filename='\\amazontestmoaz6oef.core.cvent.org\sqlnta-001.Log\MSSQL15.SQLNTA\MSSQL\Log\NTAFSDB_log2.ldf')
alter database [NTAFSDB_NEW] set emergency
alter database [NTAFSDB_NEW] set single_user with ROLLBACK IMMEDIATE;
dbcc checkdb(NTAFSDB_NEW,repair_allow_data_loss)
alter database [NTAFSDB_NEW] set multi_user
alter database [NTAFSDB_NEW] set online
ALTER Database [NTAFSDB_NEW] REMOVE FILEGROUP FGNTAFLOWMEMORYSTORAGE
GO
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 17, 2021 at 11:05 am
Hi Bhuvesh,
In the scripts provided I can see only data & log files , can you share more details on what exactly you trying to achieve?
Are you moving a DB with Filestream from one local to another and then trying to remove the Filestream filegroup and respective folder location?
November 17, 2021 at 2:13 pm
Correct , my goal is to move database (on-premises) size 700 GB having 2 gb filestream to EC2 (data and log files will be sitting on ASWS FSX files) and also we need to remove filestream on EC2.
i can see NO table or column is from filestream still not sure how/where this 2 gb data exists on filestream.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy