Export (0) Print
Expand All

How to: Add Files and Filegroups

Every database has a default filegroup with at least one file defined on it, but you can define additional filegroups and files for a database project. By defining and using additional filegroups, you can improve database performance and better manage how your database objects are backed up and restored. For more information, see Overview of Files and Filegroups.

NoteNote:

If you delete a filegroup from a database project, you can deploy that change to a new database. However, the change is ignored if you deploy the same change to an existing database.

To add a filegroup

  1. In Solution Explorer, expand the database project to which you want to add a filegroup.

  2. Expand the Schema Objects node, expand the Database Level Objects node, and expand the Storage node.

  3. Right-click the Filegroups folder, point to Add, and click New Item.

  4. In the Add New Item dialog box, in Categories, click Storage.

  5. In Templates, click Filegroup.

  6. In Name, type the name that you want to give the new filegroup.

  7. Click Add.

    The filegroup is added to the database project, and a new entry appears in the list of filegroups. The Transact-SQL (T-SQL) code editor opens and displays the definition for the new filegroup.

    When you build and deploy the database project, any new files and filegroups are created in the database that you are updating.

To add a file to a filegroup or to add a log file

  1. In Solution Explorer, expand the database project to which you want to add a filegroup.

  2. Expand the Schema Objects node, expand the Database Level Objects node, and expand the Storage node.

  3. Right-click the Files folder, point to Add, and click New Item.

  4. In the Add New Item dialog box, in Categories, click Storage.

  5. In Templates, click Filegroup File or Log File.

  6. In Name, type the name that you want to give the new file.

  7. Click Add.

    The file is added to the database project, and the T-SQL editor opens and displays the definition of that file.

  8. (optional) Modify any property of the file.

    For example, you can specify the filegroup in which the file will be created when you deploy the project.

    NoteNote:

    You can use an MSBuild variable for the path if you want to deploy the project to a location that requires a specific configuration. For example, you could specify $(DefaultDataPath)$(TargetDatabase).mdf, which is the default value for the file in the PRIMARY filegroup. You can then override the value of an MSBuild variable at a command prompt. For an example of how you can override such a value, see the "Command-line Syntax" section of An Overview of Database Build and Deployment. You can also update the .dbproj file to add the values of the MSBuild variables for each build configuration. For more information, see MSBuild Properties.

  9. On the File menu, click Save All.

    When you build and deploy the database project, any new log files and filegroups are created in the database that you are updating.

Community Additions

ADD
Show:
© 2015 Microsoft