How to: Add Files and Filegroups

Article
02/04/2013

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.

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.

Note

If you are working with a Data-tier Application (DAC) project, you cannot define files or filegroups. You must use a database project if you want to control the files and filegroups in your database.

To add a filegroup

In Solution Explorer, expand the database project to which you want to add a filegroup.
Expand the Schema Objects node, expand the Database Level Objects node, and expand the Storage node.
Right-click the Filegroups folder, point to Add, and click New Item.
In the Add New Item dialog box, in Install Templates, click Storage.
In the details pane, click Filegroup.
In Name, type the name that you want to give the new filegroup.
Click Add.

The filegroup is added to the database project, and a new entry appears in the list of filegroups. The Transact-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

In Solution Explorer, expand the database project to which you want to add a filegroup.
Expand the Schema Objects node, expand the Database Level Objects node, and expand the Storage node.
Right-click the Files folder, point to Add, and click New Item.
In the Add New Item dialog box, in Installed Templates, click Storage.
In the details pane, click Filegroup File or Log File.
In Name, type the name that you want to give the new file.
Click Add.

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

In the Transact-SQL editor, modify the file definition to associate the file with the logical filegroup, as shown in the following example:

/*
Do not change the database name.
It will be properly coded for build and deployment
This is using sqlcmd variable substitution
*/
ALTER DATABASE [$(DatabaseName)]
    ADD FILE 
    (
    NAME = [MySqlFile], 
    FILENAME = '$(DefaultDataPath)$(DatabaseName)_MySqlFile.ndf', 
        SIZE = 3072 KB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [MyFilegroupName]

(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.

Note

You can use a SQLCMD 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 a SQLCMD 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 SQLCMD variables for each build configuration.
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.

Share via

How to: Add Files and Filegroups

To add a filegroup

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

See Also

Tasks

Concepts

Additional resources