{"id":512,"date":"2017-08-15T14:24:10","date_gmt":"2017-08-15T19:24:10","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=512"},"modified":"2017-08-16T08:57:51","modified_gmt":"2017-08-16T13:57:51","slug":"generating-database-snapshot-dbcc-checkdb","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/","title":{"rendered":"Generating A Database Snapshot For DBCC CHECKDB"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-490 size-large\" src=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680-1024x586.jpg\" alt=\"\" width=\"800\" height=\"458\" srcset=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680-1024x586.jpg 1024w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680-300x172.jpg 300w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680-768x439.jpg 768w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg 1280w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/p>\n<p>Many people don&#8217;t realize that DBCC CHECKDB actually uses an internal database snapshot of the database to run the consistency checks on. (See the &#8220;Internal Database Snapshot&#8221; section of the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-checkdb-transact-sql\" target=\"_blank\" rel=\"noopener\">DBCC CHECKDB MSDN Docs<\/a> for more information.) This snapshot is created along side your &#8220;live&#8221; database, and uses the same drive(s) that your database uses. While taking this snapshot can be disabled with the use of TABLOCK, it is usually recommended to allow the snapshot because of the locking SQL Server has to do on the database to run DBCC CHECKDB.<\/p>\n<p>But, there may be times when you want to control where the snapshot DBCC CHECKDB uses is placed, and have it in a different location than your live database. For example, you may not have enough hard drive space for a snapshot to sit beside your production database, or maybe your database is on a SSD and you want the snapshot to be placed on a HDD.<\/p>\n<p>As far as I know, there is no option for CHECKDB to specify where it should place the snapshot. In cases like this, you have to generate your own snapshot and run CHECKDB on that snapshot instead of your live database. This is actually pretty easy to do:<\/p>\n<pre class=\"lang:tsql decode:true\">\/*\r\nThis code generates a snapshot of the MyDB database onto the\r\nE: drive and runs CHECKDB there.\r\n*\/\r\n\r\n--generate snapshot\r\nCREATE DATABASE [MyDB_SS] ON\r\n( NAME = DBFileName, FILENAME = 'E:\\mssql\\data\\MyDB_SS.ss' )\r\nAS SNAPSHOT OF [MyDB];\r\n\r\n--run checkdb on the snapshot\r\nDBCC CHECKDB ([MyDB_SS]) WITH TABLOCK, NO_INFOMSGS, ALL_ERRORMSGS;\r\n\r\n--drop snapshot\r\nDROP DATABASE [MyDB_SS];<\/pre>\n<p>It is important to note the use of the TABLOCK option in the DBCC CHECKDB command above. TABLOCK causes DBCC CHECKDB to obtain locks <em>instead of using an internal database snapshot<\/em>. <del>This means that if you run DBCC CHECKDB on your MyDB_SS snapshot without using TABLOCK, you will actually be taking another snapshot of your snapshot<\/del> (*see edit below). Normally, when running DBCC CHECKDB you would probably want it to take that snapshot, as it prevents blocking and concurrency problems in your live database. <del>However in this case, since we already manually created our snapshot, TABLOCK is necessary to ensure that SQL Server doesn&#8217;t create another snapshot.<\/del> (*see edit below)<\/p>\n<p>*Edit &#8211;\u00a0<em>After publishing this post, <a href=\"https:\/\/twitter.com\/PaulRandal\/status\/897544760683937792\" target=\"_blank\" rel=\"noopener\">Paul Randal told me<\/a> that\u00a0CHECKDB actually does not create a snapshot if the database it&#8217;s pointed at is already a snapshot. \u00a0This is good to know, and means that TABLOCK actually IS NOT required when running DBCC CHECKDB on a user generated snapshot, as I originally said that it was.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many people don&#8217;t realize that DBCC CHECKDB actually uses an internal database snapshot of the database to run the consistency checks on. (See the &#8220;Internal Database Snapshot&#8221; section of the &#8230;<\/p>\n","protected":false},"author":2,"featured_media":490,"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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[28],"tags":[10,37,39],"class_list":["post-512","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-db-engine","tag-database-maintenance","tag-dbcc-checkdb","tag-internals"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Generating A Database Snapshot For DBCC CHECKDB - SQL Nuggets<\/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:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Generating A Database Snapshot For DBCC CHECKDB - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"Many people don&#8217;t realize that DBCC CHECKDB actually uses an internal database snapshot of the database to run the consistency checks on. (See the &#8220;Internal Database Snapshot&#8221; section of the ...\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-15T19:24:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-08-16T13:57:51+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"732\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Eric Cobb\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cfgears\" \/>\n<meta name=\"twitter:site\" content=\"@sqlnugg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Eric Cobb\" \/>\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:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/#article\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"Generating A Database Snapshot For DBCC CHECKDB\",\"datePublished\":\"2017-08-15T19:24:10+00:00\",\"dateModified\":\"2017-08-16T13:57:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/\"},\"wordCount\":382,\"commentCount\":2,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/07\\\/binary-797263_1280-e1502824772680.jpg\",\"keywords\":[\"Database Maintenance\",\"DBCC CheckDB\",\"Internals\"],\"articleSection\":[\"DB Engine\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/\",\"name\":\"Generating A Database Snapshot For DBCC CHECKDB - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/#primaryimage\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/07\\\/binary-797263_1280-e1502824772680.jpg\",\"datePublished\":\"2017-08-15T19:24:10+00:00\",\"dateModified\":\"2017-08-16T13:57:51+00:00\",\"breadcrumb\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/07\\\/binary-797263_1280-e1502824772680.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/07\\\/binary-797263_1280-e1502824772680.jpg\",\"width\":1280,\"height\":732},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/generating-database-snapshot-dbcc-checkdb\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Generating A Database Snapshot For DBCC CHECKDB\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"name\":\"SQL Nuggets\",\"description\":\"Nuggets Of SQL Server Knowledge\",\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/www.sqlnuggets.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\",\"name\":\"SQL Nuggets\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"width\":320,\"height\":54,\"caption\":\"SQL Nuggets\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/sqlnugg\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/ericcobb\\\/\"]},{\"@type\":\"Person\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\",\"name\":\"Eric Cobb\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"caption\":\"Eric Cobb\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/cfgears\"],\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/author\\\/eric-cobb\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Generating A Database Snapshot For DBCC CHECKDB - SQL Nuggets","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:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/","og_locale":"en_US","og_type":"article","og_title":"Generating A Database Snapshot For DBCC CHECKDB - SQL Nuggets","og_description":"Many people don&#8217;t realize that DBCC CHECKDB actually uses an internal database snapshot of the database to run the consistency checks on. (See the &#8220;Internal Database Snapshot&#8221; section of the ...","og_url":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/","og_site_name":"SQL Nuggets","article_published_time":"2017-08-15T19:24:10+00:00","article_modified_time":"2017-08-16T13:57:51+00:00","og_image":[{"width":1280,"height":732,"url":"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg","type":"image\/jpeg"}],"author":"Eric Cobb","twitter_card":"summary_large_image","twitter_creator":"@cfgears","twitter_site":"@sqlnugg","twitter_misc":{"Written by":"Eric Cobb","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/#article","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"Generating A Database Snapshot For DBCC CHECKDB","datePublished":"2017-08-15T19:24:10+00:00","dateModified":"2017-08-16T13:57:51+00:00","mainEntityOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/"},"wordCount":382,"commentCount":2,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg","keywords":["Database Maintenance","DBCC CheckDB","Internals"],"articleSection":["DB Engine"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/","url":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/","name":"Generating A Database Snapshot For DBCC CHECKDB - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/#primaryimage"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg","datePublished":"2017-08-15T19:24:10+00:00","dateModified":"2017-08-16T13:57:51+00:00","breadcrumb":{"@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg","width":1280,"height":732},{"@type":"BreadcrumbList","@id":"http:\/\/www.sqlnuggets.com\/generating-database-snapshot-dbcc-checkdb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"Generating A Database Snapshot For DBCC CHECKDB"}]},{"@type":"WebSite","@id":"http:\/\/www.sqlnuggets.com\/#website","url":"http:\/\/www.sqlnuggets.com\/","name":"SQL Nuggets","description":"Nuggets Of SQL Server Knowledge","publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.sqlnuggets.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/www.sqlnuggets.com\/#organization","name":"SQL Nuggets","url":"http:\/\/www.sqlnuggets.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","width":320,"height":54,"caption":"SQL Nuggets"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/sqlnugg","https:\/\/www.linkedin.com\/in\/ericcobb\/"]},{"@type":"Person","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a","name":"Eric Cobb","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","caption":"Eric Cobb"},"sameAs":["https:\/\/x.com\/cfgears"],"url":"https:\/\/sqlnuggets.com\/author\/eric-cobb\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-8g","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":641,"url":"https:\/\/sqlnuggets.com\/sql-server-permissions-manager\/","url_meta":{"origin":512,"position":0},"title":"SQL Server Permissions Manager","author":"Eric Cobb","date":"November 21, 2017","format":false,"excerpt":"Last week I announced\u00a03 new open source SQL Server projects that I have on GitHub.\u00a0 One of those projects is SQL Server Permissions Manager, and today I would like to discuss it\u00a0in more detail. SQL Server Permissions Manager is a suite of scripts that allows you to take \"snapshots\" of\u2026","rel":"","context":"In &quot;Open Source Projects&quot;","block_context":{"text":"Open Source Projects","link":"https:\/\/sqlnuggets.com\/category\/open-source-projects\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":949,"url":"https:\/\/sqlnuggets.com\/sql-server-permissions-manager-updates-january-2019\/","url_meta":{"origin":512,"position":1},"title":"SQL Server Permissions Manager Updates &#8211; January 2019","author":"Eric Cobb","date":"January 31, 2019","format":false,"excerpt":"I recently made some updates to my SQL Server Permissions Manager project on GitHub. These updates mainly consisted of bug fixes and documentation updates, although I did also add the ability to snapshot server level permissions such as \"sysadmin\" or \"securityadmin\". Here is the (small) list of changes: Fixed bug\u2026","rel":"","context":"In &quot;Open Source Projects&quot;","block_context":{"text":"Open Source Projects","link":"https:\/\/sqlnuggets.com\/category\/open-source-projects\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/organization-chart-2556925_1280.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/organization-chart-2556925_1280.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/organization-chart-2556925_1280.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/organization-chart-2556925_1280.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/organization-chart-2556925_1280.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":311,"url":"https:\/\/sqlnuggets.com\/change-the-isolation-level-of-an-availability-group-database\/","url_meta":{"origin":512,"position":2},"title":"Change The Isolation Level Of An Availability Group Database","author":"Eric Cobb","date":"January 19, 2017","format":false,"excerpt":"I recently needed to change the Transaction Isolation Level of one of our databases in an Availability Group. \u00a0As it turns out, this is not as straight forward as you might think. \u00a0Normally all you would have to do is run an ALTER DATABASE statement, but having the database in\u2026","rel":"","context":"In &quot;Availability Groups&quot;","block_context":{"text":"Availability Groups","link":"https:\/\/sqlnuggets.com\/category\/availability-groups\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1534,"url":"https:\/\/sqlnuggets.com\/setting-the-database-owner-compatibility-level-and-recovery-model-with-powershell\/","url_meta":{"origin":512,"position":3},"title":"Setting The Database Owner, Compatibility Level, And Recovery Model With PowerShell","author":"Eric Cobb","date":"December 30, 2020","format":false,"excerpt":"One of the great things about dbatool is that it allows you to run commands against multiple things. Sometimes you may need to run the same command on every server, or every database on a server, or every database on every server (not recommended). Today we're going to look at\u2026","rel":"","context":"In &quot;Configurations&quot;","block_context":{"text":"Configurations","link":"https:\/\/sqlnuggets.com\/category\/configurations\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":545,"url":"https:\/\/sqlnuggets.com\/sql-scripts-change-owner-database\/","url_meta":{"origin":512,"position":4},"title":"SQL Scripts: How To Change The Owner Of A Database","author":"Eric Cobb","date":"September 19, 2017","format":false,"excerpt":"Sometimes it becomes necessary to change the owner of a database. \u00a0When a database is first created, SQL Server likes to set the owner of that database to the login of whoever created it. \u00a0This also seems to be the case when restoring a database,\u00a0the owner is set to the\u2026","rel":"","context":"In &quot;Security&quot;","block_context":{"text":"Security","link":"https:\/\/sqlnuggets.com\/category\/security\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":1255,"url":"https:\/\/sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/","url_meta":{"origin":512,"position":5},"title":"SQL Scripts: List Databases A Login Can Access","author":"Eric Cobb","date":"January 7, 2020","format":false,"excerpt":"When you are managing your SQL Server database permissions through Active Directory Groups (which you should be!), it can sometimes be difficult to know just which databases a user actually has access to. Sure, you could look up each AD Group that user is a member of and then check\u2026","rel":"","context":"In &quot;Security&quot;","block_context":{"text":"Security","link":"https:\/\/sqlnuggets.com\/category\/security\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/512","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/comments?post=512"}],"version-history":[{"count":0,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/512\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/490"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=512"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}