{"id":2851,"date":"2015-11-17T02:39:56","date_gmt":"2015-11-17T07:39:56","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=2851"},"modified":"2020-02-07T10:27:15","modified_gmt":"2020-02-07T15:27:15","slug":"database-filegroups","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/database-filegroups\/","title":{"rendered":"Database Filegroups"},"content":{"rendered":"
Database filegroups consist\u00a0of files and database objects (tables, indexes, stored procedures, etc). Log files are never part of a filegroup because\u00a0log space and data space are managed separately.<\/div>\n

<\/p>\n

\n

Types of Filegroups<\/h2>\n

There are two types of SQL Server filegroups: primary and user-defined.<\/p>\n

Primary<\/strong>\u00a0filegroup\u00a0includes all pages for system tables, primary data file and all other files that are not part of\u00a0any other filegroup.<\/p>\n

A filegroup that is created using\u00a0FILEGROUP keyword in CREATE DATABASE or ALTER DATABASE statement belongs to user-defined<\/strong> filegroup.<\/p>\n

Assume\u00a0there is a database ‘Test’ that\u00a0has one primary filegroup ‘Group’ stored in Group.mdf and two user-defined filegroups ‘Group1’ and ‘Group2’ stored in Group1.ndf and Group2.ndf respectively. The transaction log is stored in Test.ldf file.<\/p>\n

\"Filegroup<\/p>\n

 <\/p>\n

Each database has a default filegroup. User can specify any filegroup by default in which all new tables and indexes will be located. If the filegroup is not specified then the primary filegroup will be taken by default.<\/p>\n

Each database file can be a part of only one filegroup. If tables and indexes are large, they will be associated with a specified filegroup and will be located there or will be partitioned. That means that the data of partitioned tables and indexes will be divided into units and will be stored in separate filegroups.<\/p>\n

To add a new filegroup to a database use the following command:<\/p>\n

\n
ALTER DATABASE<\/span> Test\r\nADD FILEGROUP<\/span> Group1;\r\nGO<\/span>\r\nALTER DATABASE<\/span> Test\r\nADD FILE<\/span> (NAME = 'DataFile'<\/span>,\r\nFILENAME<\/span> = 'C:\\<\/span>DataFile<\/span>.ndf'<\/span>, SIZE = 2GB, FILEGROWTH = 10GB) TO FILEGROUP<\/span> Group1;<\/pre>\n
How to Backup Database Filegroup<\/h2>\n
Only certain filegroups can be backed up. There are two common ways of doing it:<\/p>\n
Using\u00a0Transact-SQL<\/strong> to backup a filegroup:<\/p>\n
BACKUP DATABASE<\/span> Test FILEGROUP<\/span> = 'Group1'<\/span>\r\nTO DISK<\/span> = 'C:\\Group1.FLG'<\/span><\/pre>\n
The second way to backup database filegroup is to use\u00a0SQL Server Management Studio (SSMS):\u00a0<\/strong><\/p>\n
    \n
  • Right click on the database where the filegroup you want to backup is located<\/li>\n
  • Select “Tasks”, then “Back Up…”<\/li>\n
  • Select \u00a0backup type (“Full” or “Differential”)<\/li>\n
  • Select “Files and filegroups”<\/li>\n
  • Choose filegroup and click\u00a0“OK”
    \n\"Database<\/li>\n
  • Add backup destination<\/li>\n
  • Click “OK”<\/li>\n<\/ul>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    Database filegroups consist\u00a0of files and database objects (tables, indexes, stored procedures, etc). Log files are never part of a filegroup because\u00a0log space and data space are managed separately.<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[157],"tags":[],"yoast_head":"\nDatabase Filegroups - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Database Filegroups\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/academy.sqlbak.com\/database-filegroups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database Filegroups - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Database Filegroups\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/database-filegroups\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-11-17T07:39:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-02-07T15:27:15+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Filegroup-2.png\" \/>\n<meta name=\"author\" content=\"Alexandr Omelchenko\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alexandr Omelchenko\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/database-filegroups\/\",\"url\":\"https:\/\/academy.sqlbak.com\/database-filegroups\/\",\"name\":\"Database Filegroups - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-11-17T07:39:56+00:00\",\"dateModified\":\"2020-02-07T15:27:15+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Database Filegroups\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/database-filegroups\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/database-filegroups\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/database-filegroups\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Database Filegroups\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/academy.sqlbak.com\/#website\",\"url\":\"https:\/\/academy.sqlbak.com\/\",\"name\":\"Sql Server Backup Academy\",\"description\":\"All you need to know about Sql Server database backup\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/academy.sqlbak.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\",\"name\":\"Alexandr Omelchenko\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"caption\":\"Alexandr Omelchenko\"},\"sameAs\":[\"http:\/\/sqlbak.com\"],\"url\":\"https:\/\/academy.sqlbak.com\/author\/alexandr\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Database Filegroups - Sql Server Backup Academy","description":"Learn about SQL Server Database Filegroups","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/academy.sqlbak.com\/database-filegroups\/","og_locale":"en_US","og_type":"article","og_title":"Database Filegroups - Sql Server Backup Academy","og_description":"Learn about SQL Server Database Filegroups","og_url":"https:\/\/academy.sqlbak.com\/database-filegroups\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-11-17T07:39:56+00:00","article_modified_time":"2020-02-07T15:27:15+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Filegroup-2.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/database-filegroups\/","url":"https:\/\/academy.sqlbak.com\/database-filegroups\/","name":"Database Filegroups - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-11-17T07:39:56+00:00","dateModified":"2020-02-07T15:27:15+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Database Filegroups","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/database-filegroups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/database-filegroups\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/database-filegroups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Database Filegroups"}]},{"@type":"WebSite","@id":"https:\/\/academy.sqlbak.com\/#website","url":"https:\/\/academy.sqlbak.com\/","name":"Sql Server Backup Academy","description":"All you need to know about Sql Server database backup","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/academy.sqlbak.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8","name":"Alexandr Omelchenko","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","caption":"Alexandr Omelchenko"},"sameAs":["http:\/\/sqlbak.com"],"url":"https:\/\/academy.sqlbak.com\/author\/alexandr\/"}]}},"_links":{"self":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2851"}],"collection":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/comments?post=2851"}],"version-history":[{"count":16,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2851\/revisions"}],"predecessor-version":[{"id":3758,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2851\/revisions\/3758"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=2851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=2851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=2851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}