{"id":3320,"date":"2015-12-17T05:43:01","date_gmt":"2015-12-17T10:43:01","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=3320"},"modified":"2023-10-17T04:52:35","modified_gmt":"2023-10-17T08:52:35","slug":"file-backups","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/file-backups\/","title":{"rendered":"File Backups"},"content":{"rendered":"

SQL Server allows to make backups and restore not only the entire database but also a single filegroup or even a separate file. \u00a0This is called a\u00a0file backup. <\/em>This type of backup\u00a0contains all data from one or more files or filegroups.<\/p>\n

File backups are often used to increase the recovery process speed by restoring only damaged files, without restoring the rest of the database, if necessary. Assume there is a database that contains several files. Each file is stored on a separate disk and suddenly one disk fails. In this case the recovery process will not take more time because there is no need to restore the entire database, it will be enough\u00a0to restore\u00a0only the file from the failed disk.<\/p>\n

Another use case is when\u00a0some filegroups in your database are updated more frequently than others. In this case you can backup them separately using a file backup.<\/p>\n

One of the biggest disadvantages of using file backup is that such backups are difficult to manage. It takes more time to manage every file backup separately. Also,\u00a0if failure\u00a0occurs and the backup of the damaged file is lost the entire database becomes unrecoverable.<\/p>\n

Let’s explore how does full file backup work\u00a0under different recovery models.\u00a0Assume there is a\u00a0database “Adventureworks” which contains two filegroups “Group1” and “Group2”, each filegroup includes two files “File1” and “File2” for filegroup “Group1” and “File3” and “File4” for “Group2”.<\/p>\n

Case for Simple Recovery Model<\/span><\/a><\/h2>\n

To make sure that the database can be restored, under the simple recovery model, \u00a0all read-write files<\/em> must be backed up together. It’s quite simple to accomplish this as there is no need to backup all read-write files one by one, you just need to use the READ_WRITE_FILEGROUPS option (known as a partial backup):<\/p>\n

BACKUP DATABASE<\/span>\u00a0Adventureworks\u00a0READ_WRITE_FILEGROUPS, \r\n   FILEGROUP = 'Group1', \r\n   FILEGROUP = 'Group2' \r\nTO DISK<\/span>\u00a0= 'Groups.bck'<\/span><\/pre>\n
\"Full<\/p>\n
Case for Full Recovery Model<\/h2>\n
If the database runs under the full recovery model you should backup the transaction log, regardless of the\u00a0file backup strategy you chose. All transaction log backups beginning with the first file backup and a complete set of \u00a0full file backups equal to a full database backup. Please check the picture bellow to understand how it works:<\/p>\n
BACKUP<\/span> LOG <\/span>Adventureworks TO DISK<\/span> = 'log_13_00.bak'<\/span> \r\nBACKUP DATABASE <\/span>Adventureworks FILEGROUP<\/span> = 'Group2'<\/span> TO DISK<\/span> = 'Group2.bck'<\/span> \r\nBACKUP<\/span> LOG <\/span>Adventureworks TO DISK<\/span> = 'log_14_00.bak'<\/span> \r\nBACKUP DATABASE <\/span>Adventureworks FILEGROUP<\/span> = 'Group1'<\/span> TO DISK<\/span> = 'Group1.bck'\r\n<\/span>BACKUP<\/span> LOG <\/span>Adventureworks TO DISK<\/span> = 'log_15_00.bak'<\/span> \r\nBACKUP DATABASE <\/span>Adventureworks FILEGROUP<\/span> = 'Group3'<\/span> TO DISK<\/span> = 'Group3.bck'<\/span><\/pre>\n
\"Full<\/p>\n
Note though, that using this backup strategy leads to a complex database restoration process.<\/p>\n
If any file is offline then the whole filegroup containing this file is offline too and cannot be backed up.<\/p>\n
SQL Server backups only one file at a time. Of course, it is possible to backup multiple files in one operation, but should you need to restore only one file, the restore operation will take\u00a0more time (to find the file, the whole backup will be read).<\/p>\n
Case for Differential File Backup<\/h2>\n
SQL Server allows to make not only full file or filegroup backups but also a differential file backup. Creating a full file backup makes it possible to apply a differential file backup. If the database runs under the simple recovery model the differential file backup can be applied only to read-only filegroups. As far as the full recovery model is concerned, a differential file backup can be applied to any filegroup which has been previously backed up. Differential file backups reduce time to restore database because less transaction log backups have to be restored. It is better to use differential file backups under the following circumstances:<\/p>\n
    \n
  • There is a need to backup some files more frequently than other<\/li>\n
  • Some files are really large and changes in these files occur infrequently<\/li>\n<\/ul>\n
    Making a differential file backup is quite simple, just add “WITH DIFFERENTIAL” option:<\/p>\n
    BACKUP DATABASE <\/span>Adventureworks \r\n   FILE<\/span> = '<\/span>File2<\/span>'<\/span>,\r\n   FILE<\/span> = 'File3' \r\n<\/span>TO DISK<\/span> = 'Groups_diff.bck'<\/span> WITH DIFFERENTIAL<\/span><\/pre>\n
    \"Differential<\/p>\n
    How to Create\u00a0a File Backup\u00a0with\u00a0SQL Server Management Studio<\/strong><\/h2>\n
    Here is how you can create a file backup using SSMS:<\/p>\n
      \n
    1. Right click on the database you want to backup<\/li>\n
    2. Select \u201cTasks\u201d, then \u201cBack up\u2026\u201d<\/li>\n
    3. Choose “Files and Filegroups”\u00a0and in the new window check in the files or file groups you want to backup<\/li>\n
    4. Add a backup destination<\/li>\n
    5. Click \u201cOK\u201d\"File<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
      SQL Server allows to make backups and restore not only the entire database but also a single filegroup or even a separate file. \u00a0This is called a\u00a0file backup. This type of backup\u00a0contains all data from one or more files or filegroups. File backups are often used to increase the recovery process speed by restoring only […]<\/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":"\nFile Backups - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server File Backups\" \/>\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\/file-backups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"File Backups - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server File Backups\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/file-backups\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-17T10:43:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T08:52:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Full-file-backup-simple-recovery-model-2-648x350.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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/file-backups\/\",\"url\":\"https:\/\/academy.sqlbak.com\/file-backups\/\",\"name\":\"File Backups - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-12-17T10:43:01+00:00\",\"dateModified\":\"2023-10-17T08:52:35+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server File Backups\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/file-backups\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/file-backups\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/file-backups\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"File Backups\"}]},{\"@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":"File Backups - Sql Server Backup Academy","description":"Learn about SQL Server File Backups","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\/file-backups\/","og_locale":"en_US","og_type":"article","og_title":"File Backups - Sql Server Backup Academy","og_description":"Learn about SQL Server File Backups","og_url":"https:\/\/academy.sqlbak.com\/file-backups\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-12-17T10:43:01+00:00","article_modified_time":"2023-10-17T08:52:35+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Full-file-backup-simple-recovery-model-2-648x350.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/file-backups\/","url":"https:\/\/academy.sqlbak.com\/file-backups\/","name":"File Backups - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-12-17T10:43:01+00:00","dateModified":"2023-10-17T08:52:35+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server File Backups","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/file-backups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/file-backups\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/file-backups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"File Backups"}]},{"@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\/3320"}],"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=3320"}],"version-history":[{"count":42,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3320\/revisions"}],"predecessor-version":[{"id":3845,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3320\/revisions\/3845"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=3320"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=3320"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=3320"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}