Export (0) Print
Expand All

Overview of Files and Filegroups

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 of the 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 this topic on the Microsoft Web site: Physical Database Files and Filegroups.

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

Importing schemas

When you import a schema from an existing database, you also import that database's file and filegroup definitions.

Comparing schemas

By comparing schemas, you can determine whether identically named objects were created in filegroups that have different names. You can also determine whether a filegroup was added to or removed from the source but not the target. For more information about how to compare schemas, see How to: Compare Database Schemas.

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 in 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 in it.

You might encounter the following limitations when you deploy changes to filegroups, rename filegroups, or define database objects in 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 can use database refactoring to update names of filegroups in definitions of database objects. For more information, see Rename All References to a Database Object.

Defining objects in read-only filegroups

Even if you select the Read-only check box for a filegroup, you can still define objects in that filegroup in the database project. However, 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 in the database.

Community Additions

ADD
Show:
© 2015 Microsoft