Filegroups in SQL Server

pts.
Tags:
SQL
SQL Server
I have created many filegroups for each of my databases in SQL Server. Now I am moving to a different server.I have backed up all the databases of this existing server of mine. If I delete these filegroups then will I be able to recover these backed up databases? Or do I need to backup these files and filegroups as well? Pls. help as these filegroups are taking a lot of spaces. I have shrinked them as far as possible.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Make sure you have truly shrink the files with
DBCC SHRINKFILE (gm_data, 25600)

When you recover a SQL database you’ll find it will always create the same files, that’s not to say you can’t change the file paths.

The best way to eliminate unwanted files and filegroups, is to:
– create a new database with the desired files and filegroups.
– create the tables without any Primary Keys, indexs, or other constraints.
– Then insert the data in each table by writting a select statements.
– then add back the remaining database objects.

You can use SQL’s scripting ability to generate scripts, but this will require some editing on your part. I’ve frequently edited scripts to control where tables and indexes will be creates. Note all stored procedures, triggers, and functions will be stored on the default filegroup. I will typically edit scripts to seperate them by database object, i.e. tables only, then primary keys, indexes, other constraints like foreign keys and check constraints, stored procedures, triggers, functions, database roles, grants, etc. I know this is a lot of work, but the final result will be you know where every object is stored in your database and you won’t run into a table being stored across three different files on one or more logical drives.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following