{"id":6959,"date":"2018-12-27T17:51:32","date_gmt":"2018-12-27T17:51:32","guid":{"rendered":"https:\/\/dbtut.com\/?p=6959"},"modified":"2018-12-27T17:51:32","modified_gmt":"2018-12-27T17:51:32","slug":"piecemeal-restore","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/","title":{"rendered":"PieceMeal Restore"},"content":{"rendered":"

 <\/p>\n

In the article named “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)<\/a>“, I explained the issue that we can restore only read-write filegroups without restoring the filegroup in read only mode by taking the backup of read-write filegroup.<\/p>\n

In this article we will restore the filegroups one by one. But we will recover the database without waiting for all filegroups to finish restoring.<\/p>\n

For example, you have a database of 50 TB. After restoring only the last file group, we can make the database available without waiting for the rest of the filegroup to be restored. Of course there are some conditions to do this. We will examine these conditions later in this article.<\/p>\n

First, we get the backup of the primary and all read-write filegroup as described in the article “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)<\/a>“, and then we get the backup of read only filegroups.<\/p>\n

In the article “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)<\/a>“, we did not restore read only filegroups, and as a result of the restore process, read only filegroups were also online. In this article, we’ll first restore all read write filegroups, but read only filegroups will remain in recovey pending mode.<\/p>\n

We get the backup of the primary and all read write filegroups with the following script.<\/p>\n

BACKUP DATABASE [Test] READ_WRITE_FILEGROUPS \r\nTO DISK = N'C:\\MSSQL\\READWRITEFILEGROUPS.BAK' WITH NOFORMAT, NOINIT, \r\nNAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,\u00a0 STATS = 1<\/pre>\n
With the following script, we get the backup of read only filegroup.<\/p>\n
BACKUP DATABASE [Test] FILEGROUP = N'READONLYFILEGROUP' \r\nTO\u00a0DISK = N'C:\\MSSQL\\READONLYFILEGROUPBACKUP.BAK' WITH NOFORMAT, NOINIT,\u00a0\r\nNAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,\u00a0 STATS = 1<\/pre>\n
Then, with the following script, we perform the restore process in recovery mode by specifying the PRIMARY and TABLESGROUP filegroups one by one. Notice that unlike the restore script in the article “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)<\/a>“, PARTIAL was added and the filegroups were specified one by one. If you have a log backup that you will restore, you can restore the log backups as described in the article “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)<\/a>” by making the RECOVERY part in the following script NORECOVERY.<\/p>\n
use master\r\nGO\r\nRESTORE DATABASE Test\u00a0\r\nFILEGROUP='PRIMARY' ,FILEGROUP='TABLESGROUP'\r\n\u00a0\u00a0 FROM DISK = N'C:\\MSSQL\\READWRITEFILEGROUPS.BAK'\u00a0\r\n\u00a0\u00a0 WITH PARTIAL,RECOVERY;\u00a0\r\nGO<\/pre>\n
After this restore is complete, read-write filegroups will be accessible, but you cannot access the read only filegroup because it is in recovey pending mode.<\/p>\n
With the help of the following script, you can see that the read only filegroup is waiting in recovey pending mode.<\/p>\n
SELECT [name], [state_desc] FROM Test.sys.database_files;\r\n<\/pre>\n
With the help of the following script, you can complete the process by restoring the read only filegroup.<\/p>\n
RESTORE DATABASE Test FILEGROUP='READONLYFILEGROUP' \r\nFROM DISK = N'C:\\MSSQL\\READONLYFILEGROUPBACKUP.BAK' WITH RECOVERY<\/pre>\n
When you run the following script again, you will see that read only file group is also online.<\/p>\n
SELECT [name], [state_desc]\r\n\r\nFROM Test.sys.database_files;\r\n\r\nGO<\/pre>\n\n
 <\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  In the article named “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)“, I explained the issue that we can restore only read-write filegroups without restoring the filegroup in read only mode by taking the backup of read-write filegroup. In this article we will restore the filegroups one by one. But we will …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-6959","post","type-post","status-publish","format-standard","","category-mssql"],"aioseo_notices":[],"yoast_head":"\nPieceMeal Restore - Database Tutorials<\/title>\n<meta name=\"description\" content=\"PieceMeal Restore\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PieceMeal Restore - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"PieceMeal Restore\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-12-27T17:51:32+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"PieceMeal Restore\",\"datePublished\":\"2018-12-27T17:51:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/\"},\"wordCount\":422,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/\",\"name\":\"PieceMeal Restore - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-12-27T17:51:32+00:00\",\"description\":\"PieceMeal Restore\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PieceMeal Restore\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PieceMeal Restore - Database Tutorials","description":"PieceMeal Restore","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:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/","og_locale":"en_US","og_type":"article","og_title":"PieceMeal Restore - Database Tutorials","og_description":"PieceMeal Restore","og_url":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/","og_site_name":"Database Tutorials","article_published_time":"2018-12-27T17:51:32+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"PieceMeal Restore","datePublished":"2018-12-27T17:51:32+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/"},"wordCount":422,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/","url":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/","name":"PieceMeal Restore - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-12-27T17:51:32+00:00","description":"PieceMeal Restore","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/piecemeal-restore\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"PieceMeal Restore"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6959","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=6959"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6959\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=6959"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=6959"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=6959"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}