{"id":2427,"date":"2014-06-02T11:00:00","date_gmt":"2014-06-02T15:00:00","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2427"},"modified":"2014-11-13T12:24:34","modified_gmt":"2014-11-13T17:24:34","slug":"backup-asymmetric-key","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/","title":{"rendered":"The Missing BACKUP ASYMMETRIC KEY statement"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nBack in How to Back Up your Certificates<\/a> I recommended that you always keep a separate backup of your important certificates around. The same is true for asymmetric keys. However, there we run into a slight complication.\n<\/p>\n

The BACKUP ASYMMETRIC KEY Attempt<\/h3>\n

\nWhile SQL Server's T-SQL dialect is not always consistent, in many cases you can guess what a statement should look like. To back up an asymmetric key we would expect a statement like this:\n<\/p>\n

\n[sql]\nBACKUP ASYMMETRIC KEY AnAsymmetricKey
\n[\/sql]\n<\/div>\n

\nThis would be followed by the specification of where the backup should be written to, just as it is the case with other T-SQL backup statements. However, when trying to execute this stub, we get a discouraging error:\n<\/p>\n

\n\"No<\/a>\n<\/p>\n

\nInstead of complaining about an incomplete statement, SQL Server tells us that is does not know the combination of BACKUP<\/span> and ASYMMETRIC<\/span>. That does not bode well. Let us check, if the Intelligent Code Completion<\/a> feature can be of any help.\n<\/p>\n

\n\"No<\/a>\n<\/p>\n

\nNo luck here either. It seems there is no way to back up your asymmetric keys.\n<\/p>\n

The Workaround<\/h3>\n

\nWell, there is indeed no built-in way in SQL Server to back up an asymmetric key. However, as we have seen in my article about strong name files<\/a>, you can create asymmetric keys outside of SQL Server and then import them. If you create all your asymmetric keys that way, you can keep a copy of the original file around as a \"backup\".\n<\/p>\n

\nAnother way to deal with this \"lack of backup\" is to use certificates. In most situations that call for an asymmetric key, a certificate can be used just as well.\n<\/p>\n

Database Backups<\/h3>\n

\nAsymmetric keys are stored in the database that they were created in. That means they are (like certificates too) backed up with every database backup. That might be enough protection for you because if something goes awry, you can just restore the entire database. I just like to have an additional layer of protection, as these keys often are literally the key to the kingdom<\/strike> data.\n<\/p>\n

Summary<\/h3>\n

\nSQL Server does not provide a BACKUP ASYMMETRIC KEY<\/span> statement or any other way to back up your asymmetric keys. There are two ways to deal with that situation. You can either generate all asymmetric keys outside of SQL Server and back up the key source or you can avoid using asymmetric keys altogether.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Is the BACKUP ASYMMETRIC KEY statement really missing from the T-SQL language? Read on to discover the truth and see if there are any viable alternatives.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2429,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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},"jetpack_post_was_ever_published":false},"categories":[69,5,34],"tags":[143,70,38,15],"class_list":["post-2427","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-backup","category-general","category-security","tag-asymmetric-key","tag-backup-2","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nThe Missing BACKUP ASYMMETRIC KEY statement - sqlity.net<\/title>\n<meta name=\"description\" content=\"Is the BACKUP ASYMMETRIC KEY statement really missing from the T-SQL language? Read on to discover the truth and see if there are any viable alternatives.\" \/>\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\/2427\/backup-asymmetric-key\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Missing BACKUP ASYMMETRIC KEY statement - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Is the BACKUP ASYMMETRIC KEY statement really missing from the T-SQL language? Read on to discover the truth and see if there are any viable alternatives.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-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-02T15:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:24:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/No_Backup_of_Asymmetric_Keys.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"768\" \/>\n\t<meta property=\"og:image:height\" content=\"479\" \/>\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\\\/2427\\\/backup-asymmetric-key\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"The Missing BACKUP ASYMMETRIC KEY statement\",\"datePublished\":\"2014-06-02T15:00:00+00:00\",\"dateModified\":\"2014-11-13T17:24:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/\"},\"wordCount\":408,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/No_Backup_of_Asymmetric_Keys.jpg\",\"keywords\":[\"asymmetric key\",\"backup\",\"security\",\"SQL Server\"],\"articleSection\":[\"Backup\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/\",\"name\":\"The Missing BACKUP ASYMMETRIC KEY statement - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/No_Backup_of_Asymmetric_Keys.jpg\",\"datePublished\":\"2014-06-02T15:00:00+00:00\",\"dateModified\":\"2014-11-13T17:24:34+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Is the BACKUP ASYMMETRIC KEY statement really missing from the T-SQL language? Read on to discover the truth and see if there are any viable alternatives.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/No_Backup_of_Asymmetric_Keys.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/No_Backup_of_Asymmetric_Keys.jpg\",\"width\":768,\"height\":479,\"caption\":\"No BACKUP ASYMMETRIC KEY statement.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2427\\\/backup-asymmetric-key\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Missing BACKUP ASYMMETRIC KEY statement\"}]},{\"@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":"The Missing BACKUP ASYMMETRIC KEY statement - sqlity.net","description":"Is the BACKUP ASYMMETRIC KEY statement really missing from the T-SQL language? Read on to discover the truth and see if there are any viable alternatives.","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\/2427\/backup-asymmetric-key\/","og_locale":"en_US","og_type":"article","og_title":"The Missing BACKUP ASYMMETRIC KEY statement - sqlity.net","og_description":"Is the BACKUP ASYMMETRIC KEY statement really missing from the T-SQL language? Read on to discover the truth and see if there are any viable alternatives.","og_url":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-06-02T15:00:00+00:00","article_modified_time":"2014-11-13T17:24:34+00:00","og_image":[{"width":768,"height":479,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/No_Backup_of_Asymmetric_Keys.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\/2427\/backup-asymmetric-key\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"The Missing BACKUP ASYMMETRIC KEY statement","datePublished":"2014-06-02T15:00:00+00:00","dateModified":"2014-11-13T17:24:34+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/"},"wordCount":408,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/No_Backup_of_Asymmetric_Keys.jpg","keywords":["asymmetric key","backup","security","SQL Server"],"articleSection":["Backup","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/","url":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/","name":"The Missing BACKUP ASYMMETRIC KEY statement - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/No_Backup_of_Asymmetric_Keys.jpg","datePublished":"2014-06-02T15:00:00+00:00","dateModified":"2014-11-13T17:24:34+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Is the BACKUP ASYMMETRIC KEY statement really missing from the T-SQL language? Read on to discover the truth and see if there are any viable alternatives.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/No_Backup_of_Asymmetric_Keys.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/No_Backup_of_Asymmetric_Keys.jpg","width":768,"height":479,"caption":"No BACKUP ASYMMETRIC KEY statement."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2427\/backup-asymmetric-key\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"The Missing BACKUP ASYMMETRIC KEY statement"}]},{"@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\/No_Backup_of_Asymmetric_Keys.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-D9","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2427","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=2427"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2427\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2429"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2427"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}