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