filegroups

If the name does not match one of the current files or filegroups, an error occurs.

To create a database, you have to determine the name of the database, its owner, its size, and the files and filegroups used to store it.

As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database.

The files in the default filegroup must be large enough to hold any new objects not allocated to other filegroups.

A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups.

A partition scheme is then created that specifies the filegroups to hold each one of the four partitions.

Differential partial backup -- This backup only records data that has changed in the filegroups since the preceding partial backup.

The most recent full backup of all the data in a database or in a subset of the files or filegroups of a database.

A restore of only a part of a database that consists of its primary filegroup and, perhaps, one or more secondary filegroups.

This illustration shows each of the four filegroups being backed up one at a time.

If the GUID from the backup does not match any of the current files or filegroups, an error occurs.

Do not put the transaction log file or files on the same physical disk that has the other files and filegroups.

Page restores can be performed on any database, regardless of the number of filegroups.

Transaction log files are never part of any filegroups.

The Subscriber instance must already contain the filegroups on which the partitioned tables will be placed.

However, read-only secondary filegroups and read-only databases can be placed on a file system using NTFS file compression.

When multiple filegroups are used, the files in a database can be backed up and restored individually.

Attempts to revert a database containing either of these types of filegroups fail.

Database objects and files can be grouped together in filegroups for allocation and administration purposes.

You can add data files and filegroups to a database to improve database performance and enhance your backup and restore strategies.

Only read-only databases and read-only secondary filegroups can be put on an NTFS compressed file system.

Creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups.

Additionally, files and filegroups enable data placement, because a table can be created in a specific filegroup.

Specifies the names of the filegroups to hold the partitions specified by partition_function_name.

Therefore, you should create the files or filegroups on as many different local physical disks as you can.

Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped.

During a file restore, the filegroups that contain the files are automatically offline for the duration of the restore.

Replicates filegroups associated with a partitioned table or index.

During an offline restore, the database catalog is not accessible, including the current list of filegroups.

A database is considered to be online whenever the primary filegroup is online, even if one or more of its secondary filegroups are offline.

The partial restore sequence restores the primary filegroup and, optionally, some of the secondary filegroups.

You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

Partitions created by the partition function are mapped to the filegroups specified in the partition scheme.

The offline filegroups, however, can be restored later by a file restore.

To view the current state of a filegroup, use the sys. Filegroups catalog view.

Specifies the logical names of the files or filegroups to include in a database backup.

Provides recommendations and guidelines for designing multiple files and filegroups.

Then, plan accordingly by adding more disk space, if required, and creating and adding more files or filegroups to the database.

For more information, see "To restore files and filegroups to a new location, " later in this topic.

Under the simple recovery model, file backups are essentially restricted to read-only secondary filegroups.

Restore specific files, filegroups, or pages to a database (a file or page restore).

Under the simple recovery model, file backups are basically restricted to read-only secondary filegroups.

Put different tables used in the same join queries in different filegroups.

In conformance with the syntax, every file in the source database is specified, and filegroups are not specified.

In the Remarks section, added the subsection "Partitioned Tables on Separate Filegroups. "

To create additional unassigned filegroups to hold new partitions, use ALTER PARTITION SCHEME.

Any unrestored read-write filegroups are marked as defunct and cannot subsequently be restored into the resulting database.

Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups.

For more information, see "Database Snapshots with Offline Filegroups" later in this topic.

Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

In SQL Server 2005, databases made up of multiple filegroups can be restored in stages by a process known as piecemeal restore.

For more information, see Understanding Files and Filegroups.

To create a partial backup, use the BACKUP statement and specify the READ_WRITE_FILEGROUPS option.

Example: Piecemeal Restore of Only Some Filegroups Full Recovery Model.

The transaction log backups, if applied, must cover the time when the files and filegroups were backed up.

Provides recommendations for file and filegroups size.

If filegroup_name is not specified, and there are no filegroups marked NEXT USED, ALTER PARTITION SCHEME returns a warning.

Filegroups use a proportional fill strategy across all the files within each filegroup.

Specifies a partial backup, which includes the primary filegroup and any read-write secondary filegroups.

This filegroup contains the primary data file and any secondary files that are not put into other filegroups.

This topic is relevant only for databases that contain multiple filegroups under the full or bulk-load recovery model.

Use filegroups to enable placement of objects on specific physical disks.

The source database contains any read-only or compressed filegroups.

Describes read-only filegroups and how to put read-only filegroups on disks using NTFS compression.

Multiple files or filegroups may be specified.

Under the simple recovery model, differential file backups are enabled only for read-only filegroups.

Put objects that compete heavily for space in different filegroups.

You can specify the database name, schema name, filegroups, log files, default filegroup, and collation.

For more information, see Placing Indexes on Filegroups.

This example assumes the filegroups already exist in the database.

Sparse files are not created for the offline filegroups.

Use to synchronize access to filegroups for ADD and DROP file operations.

A partial restore that omits some secondary filegroups.

Backs up a complete database, or one or more files or filegroups BACKUP DATABASE.

Modifies a database, or the files and filegroups associated with the database.

For more information, see Using Files and Filegroups.

You can also consider partitioning clustered and nonclustered indexes across multiple filegroups.

If a RESTORE statement specifies filegroups, files, or pages, only these items are included in the roll forward set.

However, files and filegroups let you easily add new files to new disks.

Every recovery model lets you back up a whole or partial SQL Server database or individual files or filegroups of the database.

Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups.

Partial backups include all the read write filegroups and, optionally, one or more read-only filegroups.

If the table is spread across multiple filegroups, the entire table is ignored.

Under the Simple Recovery Model, file and filegroup backups are available only for read-only filegroups.

Initially, the other filegroups remain offline, but they can be restored later.

You can specify a list of files or filegroups.

A full backup of all the data in one or more files, or filegroups.

A backup of all the data in one or more files or filegroups.

Explains database concepts and includes descriptions of database types, files, filegroups, and log files available in SQL Server 2005.

A maximum of 32, 767 files and 32, 767 filegroups can be specified for each database.

This topic is relevant only for SQL Server databases that contain multiple filegroups.

The partitions can be put on one or more filegroups in the database.

In SQL Server 2005, restore automatically handles changes in the number and names of files and filegroups.

Corrected the Remarks section for nonclustered indexes on separate filegroups to reflect SQL Server 2005 behavior.

Offline filegroups in the source database affect database snapshots when you try to do any of the following

Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

Database Snapshots with Offline Filegroups

Partitioned Tables on Separate Filegroups

To restore files and filegroups to a new location

Full-Text Catalogs and Tables in Separate Filegroups

How to: Restore Files and Filegroups over Existing Files (Transact-SQL)

Following are some general recommendations when you are working with files and filegroups