{"id":2842,"date":"2015-11-10T04:47:35","date_gmt":"2015-11-10T09:47:35","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=2842"},"modified":"2020-02-07T10:32:13","modified_gmt":"2020-02-07T15:32:13","slug":"database-files","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/database-files\/","title":{"rendered":"Database Files"},"content":{"rendered":"

Every database of\u00a0SQL Server at least has a log file and data file. Log files contain all information that is necessary to restore transactions in the database. Data files contain data and objects (indexes, tables, etc.).<\/p>\n

Types of Database Files<\/h2>\n

There are three types of database files in SQL Server:<\/p>\n

    \n
  1. Primary\u00a0data file.<\/strong> Every database has only one primary data file from which all other files in the database start. Locations of all data files in the database are recorded not only in the master database but also in the primary data file. Use *.mdf\u00a0extension to mark a primary data file.<\/li>\n
  2. Secondary data file. <\/strong>This type of data file includes all data files other than the primary data files. A\u00a0database can have both, several secondary data files or none\u00a0of them. Use *.ndf extension to mark a secondary data file.<\/li>\n
  3. Log file.\u00a0<\/strong>This file type is crucial\u00a0for database restore process. All log information is stored in the log file. Each database must have at least one log file. Use *.ldf\u00a0extension to mark a log data file.<\/li>\n<\/ol>\n

    These name extensions (*.mdf, *.ndf and *.ldf) in SQL Server are not necessary, but they help us to discern\u00a0the types of data files.<\/p>\n

    File Names<\/h2>\n

    All files have logical and physical names. Physical file name is a path to the data file on disk. Logical name\u00a0is a short alias that refer to the physical file. Here’s how it looks:<\/p>\n

    ALTER DATABASE<\/span> Test\r\nMODIFY FILE<\/span>\r\nNAME = NameTest - (logical file name)<\/span>\r\nFILENAME<\/span> = 'C:\\NameTest.mdf'<\/span> - (physical file name)<\/span><\/pre>\n
    Data File Pages<\/h2>\n
    The disk space allocated to data file in SQL Server is logically divided into pages. The pages are numbered sequentially starting with zero. Because every file has its own ID number, page identification requires the page number and the file ID. The first page in each file is a file header and the following few pages contain system information. Database boot page (contains database attributes) is stored in the first log file and in the primary data file.<\/p>\n
    \"Database<\/p>\n
    Database File Size<\/h2>\n
    Each file grows by growth increments, as defined by user. File size increases every time it is filled with information. It is also necessary to specify the maximum file size, otherwise the file will grow until\u00a0it has used all free space on the disk.<\/p>\n
    How to Backup a Database File<\/h2>\n
    You can backup a single database file named ‘DataFile’ using the following\u00a0Transact-SQL<\/strong> command:<\/p>\n
    BACKUP DATABASE<\/span>\u00a0Test\r\nFILE<\/span> = 'DataFile'<\/span> TO DISK<\/span> = 'D:\\DataFile.bak'<\/span>;<\/pre>\n
    The other way to backup a database file 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 \u00a0the backup type (“Full” or “Differential”)<\/li>\n
    • Select “Files and filegroups”<\/li>\n
    • Choose file and click “OK”\u00a0(It is possible to select one or more individual files, or select a filegroup to automatically choose all the files in that filegroup)
      \n\"Database<\/li>\n
    • Add backup destination<\/li>\n
    • Click “OK”<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
      Every database of\u00a0SQL Server at least has a log file and data file. Log files contain all information that is necessary to restore transactions in the database. Data files contain data and objects (indexes, tables, etc.).<\/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 Files - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server database files\" \/>\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-files\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database Files - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server database files\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/database-files\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-11-10T09:47:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-02-07T15:32:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Data-file-pages-1.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-files\/\",\"url\":\"https:\/\/academy.sqlbak.com\/database-files\/\",\"name\":\"Database Files - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-11-10T09:47:35+00:00\",\"dateModified\":\"2020-02-07T15:32:13+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server database files\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/database-files\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/database-files\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/database-files\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Database Files\"}]},{\"@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 Files - Sql Server Backup Academy","description":"Learn about SQL Server database files","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-files\/","og_locale":"en_US","og_type":"article","og_title":"Database Files - Sql Server Backup Academy","og_description":"Learn about SQL Server database files","og_url":"https:\/\/academy.sqlbak.com\/database-files\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-11-10T09:47:35+00:00","article_modified_time":"2020-02-07T15:32:13+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Data-file-pages-1.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-files\/","url":"https:\/\/academy.sqlbak.com\/database-files\/","name":"Database Files - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-11-10T09:47:35+00:00","dateModified":"2020-02-07T15:32:13+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server database files","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/database-files\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/database-files\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/database-files\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Database Files"}]},{"@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\/2842"}],"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=2842"}],"version-history":[{"count":29,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2842\/revisions"}],"predecessor-version":[{"id":3763,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2842\/revisions\/3763"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=2842"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=2842"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=2842"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}