Partial Restore how to cleanup medatadata of defunct objects

Question

Database uses the Recovery Model Simple. Made a partial Backup by setting some of the Filegroups Read_Only. "Backup Database mydb READ_WRITE_FILEGROUPS "
Restored the Database with partial, recovery.

Worked as expected, some of the Filegroups are online, some are "Recovery_Pending".

I want to remove / drop all objects / metadata of the "not restored" object.

I found only the possibility to "alter database mydb Remove Filegroup". The filegroup has the state defunct, but all Metadata (Schema, Tables, DatabaseFiles and so on) are still shown when you query sys.objects and so on.

This Objects will never be restored, how can i clean up the metadata of the SQL Server Database?

My main goal is to split 1 Database into multiple Databases.

Thank you for advice and help

Answer 1

Boring answer: Restore the entire database. Drop everything in the filegroups you want to get rid of. Once you have done that, remove the file groups with ALTER DATABASE.

This is the only supported way to achieve what you want.

Answer 2

Hi,

For splitting 1 database into multiple I may suggest below:

1 - You may create new database e.g. with name NewDB2 & copy tables manually tables from old to new issuing below select query:

select * into NewDB2.dbo.table1 from oldb.dbo.table1

2 - You may generate scripts for the objects you want to shift in new database.

In the instance right click on database->Tasks->Generate scripts.

You may select here objects to copy. For tables you may need to select one option in advance tab "Types of data to script" as shown in below image:

To cleanup defunct objects you take below steps:

Cleaning up metadata of defunct SQL Server objects is an important task to maintain the health and efficiency of your database. Here’s a general approach to clean up metadata for defunct SQL Server objects:

1. Identify Defunct Objects: Use SQL Server Management Studio (SSMS) or scripts to find unused or obsolete objects such as tables, stored procedures, functions, etc.
2. Backup Database: Before making any changes, ensure you have a full backup of your database.
3. Remove Unused Objects: For each identified object, use the DROP statement to remove it. For example:
4. Clean Filegroups: If you have defunct filegroups, you can remove them using SSMS or Transact-SQL. 
5. Update Statistics: After removing objects, update the statistics to help SQL Server’s query optimization:
6. Refresh Views: If any views reference the removed objects, refresh them:

7.     Check Dependencies: Ensure that no other objects depend on the ones you’ve removed. You can use the sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities dynamic management functions to check for dependencies.

8.     Monitor Performance: After cleanup, monitor your database performance to ensure that the removals have not negatively impacted your system.

 

Following are commands that may helpful in this regards:

DROP TABLE [YourTableName];

DROP PROCEDURE [YourProcedureName];

DROP FUNCTION [YourFunctionName];

USE master;

GO

ALTER DATABASE [YourDatabaseName]

REMOVE FILE [YourFileName];

ALTER DATABASE [YourDatabaseName]

REMOVE FILEGROUP [YourFileGroupName];

GO

EXEC sp_updatestats;

You may go through below links before taking action:

https://www.bing.com/ck/a?!&&p=85801754d2c86ebbJmltdHM9MTcyMjU1NjgwMCZpZ3VpZD0yMThmZmUxYi02ZmU3LTY2MzUtMjMzZS1lYWI5NmVlZTY3MWImaW5zaWQ9NTQ2NA&ptn=3&ver=2&hsh=3&fclid=218ffe1b-6fe7-6635-233e-eab96eee671b&psq=cleanup+defunct+objects+in+sql+server+2019&u=a1aHR0cHM6Ly9zb2x1dGlvbmNlbnRlci5hcGV4c3FsLmNvbS9ob3ctdG8tY2xlYW4tdXAtdW51c2VkLW9iamVjdHMtZnJvbS1hLXNxbC1zZXJ2ZXItZGF0YWJhc2UvIzp-OnRleHQ9VXNlJTIwdGhlJTIwT2JqZWN0JTIwZmlsdGVyJTIwaW4lMjB0aGUlMjBsZWZ0JTIwcGFuZSxzZWxlY3RpbmclMjB0aGUlMjBTUUwlMjBvYmplY3RzJTIwdG8lMjBiZSUyMGNsZWFuZWQlMjB1cC4&ntb=1

https://www.bing.com/ck/a?!&&p=627f89b525565654JmltdHM9MTcyMjU1NjgwMCZpZ3VpZD0yMThmZmUxYi02ZmU3LTY2MzUtMjMzZS1lYWI5NmVlZTY3MWImaW5zaWQ9NTIxOQ&ptn=3&ver=2&hsh=3&fclid=218ffe1b-6fe7-6635-233e-eab96eee671b&psq=cleanup+defunct+objects+in+sql+server+2019&u=a1aHR0cHM6Ly93d3cuc3Fsc2hhY2suY29tL2NsZWFuaW5nLXVwLW9sZC1jb2RlLWFuZC11bnVzZWQtb2JqZWN0cy1pbi1zcWwtc2VydmVyLw&ntb=1

Answer 3

Hi @Paolo Taverna ,

Thanks for your reaching out and welcome to Microsoft Q&A!

In addition to Erland and Zahid's answers, here is an article describing the reasons of SQL Server RECOVERY PENDING Mode, hope this can help you well!

Please refer to this official document and try to Remove Defunct Filegroups in SQL Server.

If you think the answers are helpful, please don't forget to accept it! Thanks for your understanding!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 4

Thank you very much for your answers. So far i understand there is no way to cleanup Metadata of Defunct Objects.

At this point im going the standard way. Im going to create new Databases and transfer the needed Objects from 1 Database to another.

Best Regard

Paolo