4

We have a BIG production database. We also have STG and UAT servers, where we restore the PROD database. One of the biggest tables (+250 GBs) in this PROD database is in the SECONDARY filegroup. We need the table in UAT an STG, but we don't need the data we have in PROD. My solution was to restore only the PRIMARY filegroup; that way, the restore process is faster, and also we need a lot less space since the secondary filegroup is big. We don't want to restore the secondary filegroups in the future, so I defunct the Secondary filegroup.

The big table is the only table in the secondary filegroup and it remains inaccessible. If I try to query the table, I get:

The query processor is unable to produce a plan for the table or view 'tablename' because the table resides in a filegroup that is not online.

I would like to create the table accessible again, an empty table, and even if it's on the PRIMARY filegroup. I tried to DROP the table so I can recreate it in PRIMARY filegroup, but since the SECONDARY filegroup is offline, I get an error. Also, If I try to recreate the table in PRIMARY filegroup, it says the table already exists.

As I said, we need the table for the app to work in UAT/STG, but we don't need the data, and we don't have the space to restore the secondary filegroup.

5
  • Is there anything on that defunct filegroup other than that table that you'd like to keep? cause the doc says you should discard the files on the defunct filegroup. Otherwise you could remove the files and generate scripts from the original DB with only the structure of tables and execute it on the restored database.
    – Ronaldo
    Apr 16, 2020 at 2:20
  • I had followed the instructions on that page you mentioned. But that will only work to change the Filegroup state from Recovery Pending to Defunct. What that does, allows the database to be backed up; but the filegroup never dissappears from the metadata. So, I cannot drop the table and recreate it empty from script, which is what I would like to do. Yes, that big table is the only table in that filegroup. Also, in the page you mentioned there's a link at the bottom, with a user kind of experiencing the same issue. Link here: github.com/MicrosoftDocs/sql-docs/issues/1906 Apr 16, 2020 at 4:01
  • I have re-read the Microsoft document of your link carefully, and it mentioned that that process only applies for "This topic is relevant for SQL Server databases that contain multiple files or filegroups; and, under the simple model, only for read-only filegroups." My secondary filegroup was read/write; maybe that's why my filegroup is still there and cannot be removed. Hopefully, someone from the Microsoft SQL Server team could confirm this information. Apr 16, 2020 at 4:11
  • 4
    Rename objects from the secondary filegroup and then create new ones in the primary filegroup with correct names. You can't drop them whereas renaming won't be a problem. Apr 16, 2020 at 5:44
  • 2
    Denis, this is great, I have tested and it worked, that was exactly what I was looking for. The old tables will remain there but by renaming them I will be able to recreate them in the PRIMARY filegroup and the app will have the tables accessible. I'm really thankful for your help. Thanks!!! If you post it as an answer I will mark it as the correct one. Apr 16, 2020 at 16:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.