{"id":2434,"date":"2014-06-06T11:00:08","date_gmt":"2014-06-06T15:00:08","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2434"},"modified":"2014-11-13T12:24:08","modified_gmt":"2014-11-13T17:24:08","slug":"backup-service-master-key","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/","title":{"rendered":"How to Back Up the Service Master Key"},"content":{"rendered":"
\n
\n\"BACKUP<\/a>\n<\/div>\n

Introduction<\/h3>\n

\nA few weeks ago I introduced the SQL Server Service Master Key<\/a> (SMK). As you can read there, the SMK is the root of the encryption hierarchy and potentially is the only key to a lot of your data. As such, it deserves special attention when it comes to your Disaster Recovery Plan.\n<\/p>\n

The BACKUP SERVICE MASTER KEY Statement<\/h3>\n

\nSQL Server already stores multiple copies of the SMK in the master<\/span> database, but it is still a good idea to have a separate backup of it. In fact, Books Online<\/a> says: \"The service master key should be backed up and stored in a secure, off-site location. Creating this backup should be one of the first administrative actions performed on the server.\"\n<\/p>\n

\nSo, any time you set up a new server you should take a backup of this important key. Also, every time the SMK changes you need to take a new backup as well. To take a backup you can use the BACKUP SERVICE MASTER KEY<\/span> statement like this:\n<\/p>\n

\n[sql]\nBACKUP SERVICE MASTER KEY
\n TO FILE = 'T:\\Backup\\ServiceMasterKey_20140606.bak'
\n ENCRYPTION BY PASSWORD = '%%%%%%%%%%';
\n[\/sql]\n<\/div>\n

\nThe statement has only two mutable parts: The path and file name of the backup file and the password that you would like the backup to be protected with.\n<\/p>\n

\nAs this file is essentially a replacement key for your encrypted data, so treat it with the same care. Pick a good password and store the file in a secure offsite location. Moreover, do not forget to store the password somewhere safe too. Without that password, the backup file is essentially worthless.\n<\/p>\n

Summary<\/h3>\n

\nThe Service Master Key is the root of the SQL Server encryption hierarchy. It is a very important asset that you need to protect. That includes taking a backup after it is created or altered. To take a backup you can use the BACKUP SERVICE MASTER KEY<\/span> statement.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

In SQL Server the Service Master Key is probably the most important encryption entity. Because of that, it needs to be protected and backed up. Read on to discover how to take a backup of this key encryption key.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2435,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[69,32,5,34],"tags":[70,18,273,38,171,15],"class_list":["post-2434","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-backup","category-cryptography","category-general","category-security","tag-backup-2","tag-best-practice","tag-cryptography","tag-security-2","tag-service-master-key","tag-sql-server"],"acf":[],"yoast_head":"\nHow to Back Up the Service Master Key - sqlity.net<\/title>\n<meta name=\"description\" content=\"In SQL Server the Service Master Key is probably the most important encryption entity. Read on to discover how to take a backup of this key encryption key.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Back Up the Service Master Key - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"In SQL Server the Service Master Key is probably the most important encryption entity. Read on to discover how to take a backup of this key encryption key.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-06-06T15:00:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:24:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/BackupServiceMasterKey.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"846\" \/>\n\t<meta property=\"og:image:height\" content=\"469\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\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\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Back Up the Service Master Key\",\"datePublished\":\"2014-06-06T15:00:08+00:00\",\"dateModified\":\"2014-11-13T17:24:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/\"},\"wordCount\":331,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/BackupServiceMasterKey.jpg\",\"keywords\":[\"backup\",\"Best Practice\",\"Cryptography\",\"security\",\"Service Master Key\",\"SQL Server\"],\"articleSection\":[\"Backup\",\"Cryptography\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/\",\"name\":\"How to Back Up the Service Master Key - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/BackupServiceMasterKey.jpg\",\"datePublished\":\"2014-06-06T15:00:08+00:00\",\"dateModified\":\"2014-11-13T17:24:08+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"In SQL Server the Service Master Key is probably the most important encryption entity. Read on to discover how to take a backup of this key encryption key.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/BackupServiceMasterKey.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/BackupServiceMasterKey.jpg\",\"width\":846,\"height\":469,\"caption\":\"BACKUP SERVICE MASTER KEY\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2434\\\/backup-service-master-key\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Back Up the Service Master Key\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Back Up the Service Master Key - sqlity.net","description":"In SQL Server the Service Master Key is probably the most important encryption entity. Read on to discover how to take a backup of this key encryption key.","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:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/","og_locale":"en_US","og_type":"article","og_title":"How to Back Up the Service Master Key - sqlity.net","og_description":"In SQL Server the Service Master Key is probably the most important encryption entity. Read on to discover how to take a backup of this key encryption key.","og_url":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-06-06T15:00:08+00:00","article_modified_time":"2014-11-13T17:24:08+00:00","og_image":[{"width":846,"height":469,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/BackupServiceMasterKey.jpg","type":"image\/jpeg"}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Back Up the Service Master Key","datePublished":"2014-06-06T15:00:08+00:00","dateModified":"2014-11-13T17:24:08+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/"},"wordCount":331,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/BackupServiceMasterKey.jpg","keywords":["backup","Best Practice","Cryptography","security","Service Master Key","SQL Server"],"articleSection":["Backup","Cryptography","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/","url":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/","name":"How to Back Up the Service Master Key - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/BackupServiceMasterKey.jpg","datePublished":"2014-06-06T15:00:08+00:00","dateModified":"2014-11-13T17:24:08+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"In SQL Server the Service Master Key is probably the most important encryption entity. Read on to discover how to take a backup of this key encryption key.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/BackupServiceMasterKey.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/BackupServiceMasterKey.jpg","width":846,"height":469,"caption":"BACKUP SERVICE MASTER KEY"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2434\/backup-service-master-key\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Back Up the Service Master Key"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/BackupServiceMasterKey.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Dg","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2434","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2434"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2434\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2435"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}