{"id":3196,"date":"2015-12-04T04:26:50","date_gmt":"2015-12-04T09:26:50","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=3196"},"modified":"2023-10-17T05:22:31","modified_gmt":"2023-10-17T09:22:31","slug":"partial-backup","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/partial-backup\/","title":{"rendered":"Partial Backup"},"content":{"rendered":"

The peculiarity\u00a0of partial backup is that it does not contain read-only filegroups. So it’s a good choice if your database has a read-only filegroup and there is no need to backup it all of the time. Though\u00a0all recovery models in SQL Server support partial backup, it\u00a0was primarily\u00a0designed for use under the simple recovery model as it makes backup of huge databases, containing one or\u00a0more read-only filegroups, more flexible.<\/p>\n

A\u00a0partial backup can be applied\u00a0both to\u00a0full or differential backups, though it is not applicable to transaction log backup. As far as differential partial backup is concerned,\u00a0only the data extents that have changed in the filegroups since the previous partial backup are recorded into it. Please note\u00a0that differential partial backups can be applied only with partial backups.<\/p>\n

Let’s consider the picture below to understand how\u00a0partial backup and differential partial backup work:<\/p>\n

\"Partial<\/p>\n

\n
\n
\n

Below I’ll explain what is shown in the picture. Take a huge database that uses a simple recovery model, where the full database backup was made at 12:00:<\/p>\n

BACKUP DATABASE<\/span>\u00a0your_database<\/em>\u00a0TO DISK<\/span>\u00a0= 'full.bak'<\/span><\/pre>\n
Because the size of the database is large, and it takes more time to make backup, a decision was made to create a partial database backup, which includes the primary filegroup and read-write secondary filegroup:<\/p>\n
BACKUP DATABASE<\/span>\u00a0your_database<\/em>\u00a0READ_WRITE_FILEGROUPS TO DISK<\/span>\u00a0= 'partial_backup_full.bak'<\/span><\/pre>\n<\/div>\n<\/div>\n<\/div>\n
This backup takes less time than a full database backup. Following that some changes were made in the primary filegroup and the read-write secondary filegroup. These changes were stored in the following differential partial backup that was created at 14:00:<\/p>\n
BACKUP DATABASE<\/span>\u00a0your_database <\/em>READ_WRITE_FILEGROUPS TO DISK<\/span> = 'partial_backup_diff.bak'<\/span> WITH DIFFERENTIAL<\/span><\/pre>\n
Note that SQL Server Management Studio does not support partial backups.<\/p>\n
Here is another, more interesting case. Assume that the database was changed from read-only to read-write after a partial backup was made. In this case, perhaps, some secondary read-write filegroups will not be included in the partial backup, therefore, when the time comes to make a differential partial backup the following error occurs:<\/p>\n
Msg 233, Level 20, State 0, Line 2<\/span>\r\nA transport-level error has occurred when sending the request to the server.<\/span><\/pre>\n
To avoid\u00a0this error, make another partial backup before\u00a0a differential partial backup. Following this operation, the new\u00a0partial backup\u00a0will contain every read-write secondary filegroup.<\/p>\n
Restore Process<\/h3>\n
The commands for the database restore process from the example above should be executed in the following order:<\/code><\/p>\n
RESTORE<\/span> DATABASE<\/span><\/span> your_database <\/em>FROM<\/span> DISK<\/span><\/span> = <\/span>'full.bak'<\/span>, <\/span>WITH<\/span> NORECOVERY<\/span><\/span><\/code> GO<\/span><\/code> \r\nRESTORE<\/span> DATABASE<\/span><\/span> your_dataabse <\/em>FROM<\/span> DISK<\/span><\/span> = <\/span><\/code>'partial_backup_full.bak', <\/span>WITH<\/span> NORECOVERY <\/span><\/span> GO <\/span>RESTORE<\/span> DATABASE<\/span><\/span> your_database<\/em> FROM<\/span> DISK<\/span><\/span> = <\/span>'partial_backup_diff.bak',<\/span> WITH<\/span> RECOVERY<\/span> <\/span> GO<\/span><\/pre>\n","protected":false},"excerpt":{"rendered":"
The peculiarity\u00a0of partial backup is that it does not contain read-only filegroups. So it’s a good choice if your database has a read-only filegroup and there is no need to backup it all of the time. Though\u00a0all recovery models in SQL Server support partial backup, it\u00a0was primarily\u00a0designed for use under the simple recovery model as […]<\/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":"\nPartial Backup - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Partial Backup\" \/>\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\/partial-backup\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Partial Backup - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Partial Backup\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/partial-backup\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-04T09:26:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:22:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Partial-Backup-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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/partial-backup\/\",\"url\":\"https:\/\/academy.sqlbak.com\/partial-backup\/\",\"name\":\"Partial Backup - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-12-04T09:26:50+00:00\",\"dateModified\":\"2023-10-17T09:22:31+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Partial Backup\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/partial-backup\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/partial-backup\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/partial-backup\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Partial Backup\"}]},{\"@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":"Partial Backup - Sql Server Backup Academy","description":"Learn about SQL Server Partial Backup","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\/partial-backup\/","og_locale":"en_US","og_type":"article","og_title":"Partial Backup - Sql Server Backup Academy","og_description":"Learn about SQL Server Partial Backup","og_url":"https:\/\/academy.sqlbak.com\/partial-backup\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-12-04T09:26:50+00:00","article_modified_time":"2023-10-17T09:22:31+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Partial-Backup-648x350.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\/partial-backup\/","url":"https:\/\/academy.sqlbak.com\/partial-backup\/","name":"Partial Backup - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-12-04T09:26:50+00:00","dateModified":"2023-10-17T09:22:31+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Partial Backup","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/partial-backup\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/partial-backup\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/partial-backup\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Partial Backup"}]},{"@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\/3196"}],"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=3196"}],"version-history":[{"count":33,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3196\/revisions"}],"predecessor-version":[{"id":3867,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3196\/revisions\/3867"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=3196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=3196"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=3196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}