Overview of Files and Filegroups

Article
06/26/2007

By defining and using additional filegroups, you can improve database performance and better manage how your database objects are backed up and restored. For example, you can define filegroups on different physical disks to reduce access time. If you associate all database objects in a schema with a particular filegroup, you can then back up and restore those objects together. For more information about filegroups, see Physical Database Files and Filegroups.

Behavior

Before you define files and filegroups in your database project, you should consider the following behaviors so that you can distinguish between expected behavior and unexpected issues.

Upgrading to Service Release 1

If you defined filegroups in Team Edition for Database Professionals before the release of Service Release 1 (SR1), you defined them as schema objects. You also defined the associated files in the storage.sql file that is part of the post-deployment script. After you install SR1, you define filegroups and files as properties of the database project. If you use SR1 to open a database project that contains files or filegroups that were created before SR1, the file and filegroup definitions are converted from schema objects to project properties. In addition, the corresponding sections in the storage.sql file are converted to comments. However, the conversion does not affect other statements in the storage.sql file or filegroup and file definitions that are in other scripts.

Importing schemas

When you import a schema from an existing database, you also import the database's filegroup and file definitions. If the database from which you imported the schema had additional filegroups defined, a warning such as the following appears:

Warning: ADD FILEGROUP statements have been generated, which include the creation of a file for each filegroup. You should check the file name and path of the files for consistency with the other files on the target server.

Comparing schemas

By comparing schemas, you can determine whether identically named objects were created on filegroups that have different names. You can also determine whether a filegroup was added to or removed from the source but not the target. However, you cannot determine whether filegroups contain the same files because the filegroups will always appear as identical. For more information about how to compare schemas, see How to: Compare the Schemas of Two Databases.

Building and deploying database projects

You cannot make files and filegroups themselves specific to particular build configurations. However, you can define files in terms of MSBuild variables, which can be specific to a configuration. By using this approach, you can, for example, use paths and file names in your isolated development environment that differ from those on your staging server.

Deleting Filegroups, Files, and Log Files

If you delete a filegroup from a database project, you can deploy that change to a new database, but the change is ignored if you deploy to an existing database. When you delete a filegroup from a database project, you also delete any files that were associated with that filegroup. Any objects in the database project that were on the filegroup that you deleted appear in an error state. To resolve the errors, you must either re-create the filegroup or modify those object definitions to associate them with another filegroup. Under these circumstances, you cannot deploy your database project until you resolve the errors.

You cannot delete the primary filegroup. You must always have a default filegroup specified. If you remove the only file in a filegroup, a warning appears in the Error List window to indicate that the filegroup definition is incomplete. In addition, you cannot remove the only log file in a database project. A database project must always have at least one log file defined on it.

Limitations

You might encounter the following limitations when you deploy changes to filegroups, rename filegroups, and define database objects on read-only filegroups.

Deploying changes

If you add a filegroup in a database project, you can deploy that change to a new or existing database. If you delete a filegroup or modify its files or properties, you can deploy those changes to a new database. However, deletions and modifications of filegroups are ignored if you deploy the project to an existing database. If the name of a filegroup in the database project matches the name of a filegroup on the target, the filegroups are assumed to be the same.

If you rename a filegroup and then deploy it to an existing database, a filegroup that has the new name is created, and the existing filegroup does not change.

Renaming filegroups in object definitions

You cannot use refactoring to update names of filegroups in definitions of database objects. You must update those names manually or by using the search feature. For more information, see Find and Replace Window.

Defining objects on read-only filegroups

Even if you select the Read-only check box for a filegroup, you can still define objects on that filegroup in the database project. If you try to deploy those changes to an existing database, the operation will fail with an error. When you deploy those changes to a new database, the objects are created as if the Read-only check box were cleared. After the objects are created, the filegroup is set as read-only on the database.

Overview of Files and Filegroups

Behavior

Limitations

See Also

Tasks

Concepts

Other Resources

Additional resources