Sample Header Ad - 728x90

What is the quickest and most efficient way to remove an in-memory filegroup from a large SQL Server 2019 database?

0 votes
2 answers
736 views
I have a database with an in-memory filegroup that I want to remove (I won't go into many details why, but I want to move the database to another place without this filegroup). I'm trying to move the database to Azure SQL Managed Instance (General Purpose), and this PaaS solution doesn't support in-memory filegroups. Notes: - This is running SQL Server 2019 - This database is quite big: more than 500GB - There is no data in that filegroup at this time - It have tables (of course), views, SP, Synonyms... From the documentation "Once you use a memory-optimized filegroup, you can only remove it by dropping the database.". So, I need to drop the database, and create it again without the file group. But doesn't detail the best way to do this. What is the best option to do this? I've tried the Copy Database Wizard but it copies the filegroup that I want to delete. I've tried the SQL Server Import and Export Wizard but it takes too long. I've tried to export to another instance, but due to the size of the database it fails after several hours (network error or session lost). I've tried to use the option "Generate Scripts" and do this: 1. Export schema only 2. Export data only 3. Edit the file to remove the filegroup 4. Drop the database 5. Recreate the database with the edited file 6. Import the data But this is taking too long. There is any other way (best and quick way) to do this? 1. Extract the schema from DatabaseA (from Generate Scripts: Types of data to script: Schema only) 2. Put the DatabaseA in single user mode 3. Create the DatabaseB in the same instance Create all the objects in DatabaseB from the extract without that filegroup 4. Use the SQL Server Import and Export Wizard and copy all the data from DatabaseA to DatabaseB 5. Rename the database DatabaseA to DatabaseA_old 6. Rename the database DatabaseB to DatabaseA
Asked by dcop7 (29 rep)
Jun 4, 2023, 08:08 PM
Last activity: Jun 5, 2023, 02:46 PM