{"id":32,"date":"2014-01-02T21:24:14","date_gmt":"2014-01-02T13:24:14","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=32"},"modified":"2014-01-02T21:24:14","modified_gmt":"2014-01-02T13:24:14","slug":"backup-and-restore-resource-database","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html","title":{"rendered":"Backup and Restore Resource Database"},"content":{"rendered":"

Sebelumnya saya pernah menulis apa itu resource database disini<\/a>, dan berikut adalah lanjutannya yaitu bagaimana untuk melakukan Backup dan Restore Resource Database.<\/p>\n

Untuk melakukan aktivitas Backup dan Resore Resource Database tidak dapat menggunakan dengan metode yang sama dengan system databases yang lain, karena Resource Database tidak dapat terlihat di SQL Server Management Studio.<\/p>\n

Kegiatan Restore dan Backup Resource Database dapat dilakukan dengan menggunakan Copy dan paste file.<\/p>\n

Backup<\/font><\/p>\n

untuk dapat melakukan backup, kita akan menggunakan fitur xp_cmdshell, namun sebelum dapat menggunakan fitur ini, harus diaktifkan terlebih dahulu dengan cara sebagai berikut :<\/p>\n

\n
\n
USE<\/span> master<\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
-- To allow advanced options to be changed.<\/span><\/pre>\n
<\/p>\n
EXEC<\/span> sp_configure 'show advanced options'<\/span>, 1<\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
-- To update the currently configured value for advanced options.<\/span><\/pre>\n
<\/p>\n
RECONFIGURE<\/span><\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
-- To enable the feature.<\/span><\/pre>\n
<\/p>\n
EXEC<\/span> sp_configure 'xp_cmdshell'<\/span>, 1<\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
-- To update the currently configured value for this feature.<\/span><\/pre>\n
<\/p>\n
RECONFIGURE<\/span><\/pre>\n
<\/p>\n
GO<\/pre>\n
<\/div>\n<\/div>\n
Selanjutnya kita melakukan aktivitas copy file resource database yang terdiri dari 2 file yaitu mssqlsystemresource.mdf and mssqlsystemresource.ldf dengan cara sebagai berikut :<\/div>\n
 <\/div>\n
Untuk SQL Server 2008 :<\/div>\n
\n
\n
use<\/span> master<\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
\/* Copy Resource Database Files Using XP_CMDSHELL *\/ <\/span><\/pre>\n
<\/p>\n
EXEC<\/span> xp_cmdshell 'COPY \"C:\\Program Files\\Microsoft SQL Server\\<\/pre>\n
<\/p>\n
    MSSQL10.MSSQLSERVER\\MSSQL\\Binn\\mssqlsystemresource.mdf\" <\/pre>\n
<\/p>\n
    \"E:\\SystemDatabaseBackups\\SS2008\"'<\/span>, NO_OUTPUT<\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
 <\/pre>\n
<\/p>\n
EXEC<\/span> xp_cmdshell 'COPY \"C:\\Program Files\\Microsoft SQL Server\\<\/pre>\n
<\/p>\n
    MSSQL10.MSSQLSERVER\\MSSQL\\Binn\\mssqlsystemresource.ldf\" <\/pre>\n
<\/p>\n
    \"E:\\SystemDatabaseBackups\\SS2008\"'<\/span>,NO_OUTPUT<\/pre>\n
<\/p>\n
GO<\/pre>\n
<\/div>\n<\/div>\n
Note : karena String setelah xp_cmdshell terlalu panjang, pada script diatas saya tulis menjadi 3 line untuk setiap string. namun pada prakteknya harus ditulis dalam 1 line.<\/p>\n
Untuk SQL Server 2008 R2 :<\/p>\n
\n
\n
use<\/span> master<\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
\/* Copy Resource Database Files Using XP_CMDSHELL *\/ <\/span><\/pre>\n
<\/p>\n
EXEC<\/span> xp_cmdshell 'COPY \"C:\\Program Files\\Microsoft SQL Server\\<\/pre>\n
<\/p>\n
    MSSQL10_50.SS2008R2\\MSSQL\\Binn\\mssqlsystemresource.mdf\" <\/pre>\n
<\/p>\n
    \"E:\\SystemDatabaseBackups\\SS2008R2\"'<\/span>, NO_OUTPUT<\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
 <\/pre>\n
<\/p>\n
EXEC<\/span> xp_cmdshell 'COPY \"C:\\Program Files\\Microsoft SQL Server\\<\/pre>\n
<\/p>\n
    MSSQL10_50.SS2008R2\\MSSQL\\Binn\\mssqlsystemresource.ldf\" <\/pre>\n
<\/p>\n
    \"E:\\SystemDatabaseBackups\\SS2008R2\"'<\/span>,NO_OUTPUT<\/pre>\n
<\/p>\n
GO<\/pre>\n
<\/div>\n<\/div>\n
Note : karena String setelah xp_cmdshell terlalu panjang, pada script diatas saya tulis menjadi 3 line untuk setiap string. namun pada prakteknya harus ditulis dalam 1 line.<\/p>\n
Restore<\/font><\/p>\n
untuk proses restorenya sangat mudah, setelah database master telah di rebuild ulang dari backup database master sebelumnya kemudian pastikan sql server servicesnya dimatikan, kemudian copy kan file mssqlsystemresource.mdf and mssqlsystemresource.ldf ke folder yang semestinya. kemudian aktifkan kembali SQL Server Services nya.<\/p>\n","protected":false},"excerpt":{"rendered":"
Sebelumnya saya pernah menulis apa itu resource database disini, dan berikut adalah lanjutannya yaitu bagaimana untuk melakukan Backup dan Restore Resource Database. Untuk melakukan aktivitas Backup dan Resore Resource Database tidak dapat menggunakan dengan metode yang sama dengan system databases yang lain, karena Resource Database tidak dapat terlihat di SQL \u2026<\/p>\n
 Continue reading<\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[9,12,13],"class_list":["post-32","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-backup","tag-resource-database","tag-restore"],"yoast_head":"\nBackup and Restore Resource Database - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Backup and Restore Resource Database - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"Sebelumnya saya pernah menulis apa itu resource database disini, dan berikut adalah lanjutannya yaitu bagaimana untuk melakukan Backup dan Restore Resource Database. Untuk melakukan aktivitas Backup dan Resore Resource Database tidak dapat menggunakan dengan metode yang sama dengan system databases yang lain, karena Resource Database tidak dapat terlihat di SQL \u2026 Continue reading\" \/>\n<meta property=\"og:url\" content=\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-02T13:24:14+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\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\":\"Article\",\"@id\":\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html#article\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"Backup and Restore Resource Database\",\"datePublished\":\"2014-01-02T13:24:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html\"},\"wordCount\":221,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"keywords\":[\"Backup\",\"Resource Database\",\"Restore\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html\",\"url\":\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html\",\"name\":\"Backup and Restore Resource Database - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"datePublished\":\"2014-01-02T13:24:14+00:00\",\"breadcrumb\":{\"@id\":\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Backup and Restore Resource Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Backup and Restore Resource Database - Suherman Blog","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":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html","og_locale":"en_US","og_type":"article","og_title":"Backup and Restore Resource Database - Suherman Blog","og_description":"Sebelumnya saya pernah menulis apa itu resource database disini, dan berikut adalah lanjutannya yaitu bagaimana untuk melakukan Backup dan Restore Resource Database. Untuk melakukan aktivitas Backup dan Resore Resource Database tidak dapat menggunakan dengan metode yang sama dengan system databases yang lain, karena Resource Database tidak dapat terlihat di SQL \u2026 Continue reading","og_url":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2014-01-02T13:24:14+00:00","author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html#article","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"Backup and Restore Resource Database","datePublished":"2014-01-02T13:24:14+00:00","mainEntityOfPage":{"@id":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html"},"wordCount":221,"commentCount":0,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"keywords":["Backup","Resource Database","Restore"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html#respond"]}]},{"@type":"WebPage","@id":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html","url":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html","name":"Backup and Restore Resource Database - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"datePublished":"2014-01-02T13:24:14+00:00","breadcrumb":{"@id":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/suherman.asia\/w2\/backup-and-restore-resource-database.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"Backup and Restore Resource Database"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/32","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=32"}],"version-history":[{"count":1,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/32\/revisions"}],"predecessor-version":[{"id":33,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/32\/revisions\/33"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=32"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=32"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=32"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}