{"id":775,"date":"2018-04-17T08:00:00","date_gmt":"2018-04-17T13:00:00","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=775"},"modified":"2018-04-16T16:19:57","modified_gmt":"2018-04-16T21:19:57","slug":"sql-scripts-how-to-find-a-databases-compatibility-level","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/","title":{"rendered":"SQL Scripts: How To Find A Database\u2019s Compatibility Level"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-776\" src=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460-1024x604.jpg\" alt=\"\" width=\"800\" height=\"472\" srcset=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460-1024x604.jpg 1024w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460-300x177.jpg 300w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460-768x453.jpg 768w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/p>\n<p>One thing that a lot of DBAs, especially <a href=\"https:\/\/www.google.com\/search?q=define+Accidental+DBA\" target=\"_blank\" rel=\"noopener\">Accidental DBAs<\/a> such as myself, don&#8217;t seem to realize is that just because a database is sitting on a specific version of SQL Server does not mean that database has all of the features of that SQL Server version enabled.\u00a0 To put it another way, you can have a database running on SQL Server 2014, but that database still be set to SQL Server 2008 compatibility and some of the features of 2014 will not be enabled.<\/p>\n<p>Every database on a SQL Server has a Compatibility Level setting, which will enable some (not all) of the features of each version.\u00a0 For example, if you are on SQL Server 2014 and do not wish to use the new <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance\/cardinality-estimation-sql-server\" target=\"_blank\" rel=\"noopener\">Cardinality Estimator<\/a>\u00a0on a specific database, you can simply set that database to SQL Server 2012 Compatibility Level and this will disable that 2014 feature (along with a few others).\u00a0 For a full list of what is enabled at each compatibility level see the\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-transact-sql-compatibility-level\" target=\"_blank\" rel=\"noopener\">Differences Between Compatibility Level sections of the MSDN Docs<\/a>.<\/p>\n<p>While being able to make this change is a handy feature to have, many people do not realize that it is a <em>manual<\/em> process, meaning that SQL Server will not automatically update the compatibility level of a database to match the server version when you put it on a new server.\u00a0 So, even if you migrated your databases to a new server with a higher version of SQL Server, the compatibility level of your databases will not change unless you explicitly go into the database and change the setting.\u00a0 The same thing applies if you do an upgrade to an existing SQL Server. I have seen many cases where databases were on a SQL 2014 or 2106 server, but the database compatibility level was still set to 2008.<\/p>\n<p>There is one exception to this rule.\u00a0 If you restore a database that has a lower compatibility level than the new server allows, SQL Server will upgrade that database&#8217;s compatibility level to the <em>lowest<\/em> version that is allowed on that server.\u00a0 So, if your database is set at 2005 compatibility level, and you restore it to SQL Server 2016, the database&#8217;s compatibility level will be changed to 2008, because that&#8217;s the lowest version SQL Server 2016 will support.\u00a0 (<em>NOTE: This does not mean that you can upgrade directly from SQL Server 2005 to SQL Server 2016.\u00a0 That is a different subject entirely. See <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/install-windows\/supported-version-and-edition-upgrades\" target=\"_blank\" rel=\"noopener\">Supported Version and Edition Upgrades<\/a>.<\/em>)<\/p>\n<h4>How Can I Find My Database&#8217;s Compatibility Level?<\/h4>\n<p>Finding the compatibility level for you database is pretty simple, you can do it by querying the\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/sys-databases-transact-sql\" target=\"_blank\" rel=\"noopener\">sys.databases<\/a> DMV.<\/p>\n<pre class=\"lang:tsql decode:true\">-- Find Compatibility Level for a specific database\nSELECT name, compatibility_level  \nFROM sys.databases \nWHERE name = 'YourDatabaseName';  \n\n-- Find Compatibility Level for all databases on the server\nSELECT name, compatibility_level  \nFROM sys.databases; \n<\/pre>\n<h4><\/h4>\n<h4>How Do I Change My Database&#8217;s Compatibility Level?<\/h4>\n<p>Now before you go all <a href=\"https:\/\/www.merriam-webster.com\/dictionary\/willy-nilly\" target=\"_blank\" rel=\"noopener\">willy-nilly<\/a> and start updating your database compatibility levels, you need to make sure you test this change, ESPECIALLY if you are updating from a pre-2014 level to 2014 or higher.\u00a0 With the 2014 compatibility level come changes to the query engine that could cause significant performance degradation in some rare cases.\u00a0 (I&#8217;ve seen a 30 second query start taking 11 minutes)\u00a0 So, be sure you test, test, test this change before flipping the switch in production.\u00a0 You&#8217;ve been warned.<\/p>\n<pre class=\"lang:tsql decode:true \">--change compatibility level to 2016 for your database\nALTER DATABASE [YourDatabase]\nSET COMPATIBILITY_LEVEL = 130;  \nGO<\/pre>\n<p>Notice that the compatibility level setting is actually a numerical value.\u00a0 That&#8217;s how Microsoft does this, each compatibility level has a numerical value assigned to it, and that&#8217;s what we use in the database settings. Be sure to check this list of\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-transact-sql-compatibility-level\" target=\"_blank\" rel=\"noopener\">Supported Compatibility Level Values<\/a> for the version of SQL Server you are running.<\/p>\n<p>&nbsp;<\/p>\n<p><em>This post is part of an ongoing\u00a0<a href=\"https:\/\/sqlnuggets.com\/blog\/category\/sql-scripts\/\">SQL Scripts<\/a>\u00a0series, in which I list useful queries that I use in my day to day database administration.<\/em><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One thing that a lot of DBAs, especially Accidental DBAs such as myself, don&#8217;t seem to realize is that just because a database is sitting on a specific version of &#8230;<\/p>\n","protected":false},"author":2,"featured_media":776,"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,52],"tags":[39,17],"class_list":["post-775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-db-engine","category-sql-scripts","tag-internals","tag-server-administration"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Scripts: How To Find A Database\u2019s Compatibility Level - 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\/sql-scripts-how-to-find-a-databases-compatibility-level\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Scripts: How To Find A Database\u2019s Compatibility Level - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"One thing that a lot of DBAs, especially Accidental DBAs such as myself, don&#8217;t seem to realize is that just because a database is sitting on a specific version of ...\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2018-04-17T13:00:00+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"2315\" \/>\n\t<meta property=\"og:image:height\" content=\"1365\" \/>\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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/#article\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"SQL Scripts: How To Find A Database\u2019s Compatibility Level\",\"datePublished\":\"2018-04-17T13:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/\"},\"wordCount\":608,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2018\\\/04\\\/black-and-white-blank-challenge-262488-e1523887336460.jpg\",\"keywords\":[\"Internals\",\"Server Administration\"],\"articleSection\":[\"DB Engine\",\"SQL Scripts\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/\",\"name\":\"SQL Scripts: How To Find A Database\u2019s Compatibility Level - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/#primaryimage\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2018\\\/04\\\/black-and-white-blank-challenge-262488-e1523887336460.jpg\",\"datePublished\":\"2018-04-17T13:00:00+00:00\",\"breadcrumb\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2018\\\/04\\\/black-and-white-blank-challenge-262488-e1523887336460.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2018\\\/04\\\/black-and-white-blank-challenge-262488-e1523887336460.jpg\",\"width\":2315,\"height\":1365},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-how-to-find-a-databases-compatibility-level\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Scripts: How To Find A Database\u2019s Compatibility Level\"}]},{\"@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":"SQL Scripts: How To Find A Database\u2019s Compatibility Level - 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\/sql-scripts-how-to-find-a-databases-compatibility-level\/","og_locale":"en_US","og_type":"article","og_title":"SQL Scripts: How To Find A Database\u2019s Compatibility Level - SQL Nuggets","og_description":"One thing that a lot of DBAs, especially Accidental DBAs such as myself, don&#8217;t seem to realize is that just because a database is sitting on a specific version of ...","og_url":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/","og_site_name":"SQL Nuggets","article_published_time":"2018-04-17T13:00:00+00:00","og_image":[{"width":2315,"height":1365,"url":"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/#article","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"SQL Scripts: How To Find A Database\u2019s Compatibility Level","datePublished":"2018-04-17T13:00:00+00:00","mainEntityOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/"},"wordCount":608,"commentCount":0,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg","keywords":["Internals","Server Administration"],"articleSection":["DB Engine","SQL Scripts"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/","url":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/","name":"SQL Scripts: How To Find A Database\u2019s Compatibility Level - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/#primaryimage"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg","datePublished":"2018-04-17T13:00:00+00:00","breadcrumb":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg","width":2315,"height":1365},{"@type":"BreadcrumbList","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"SQL Scripts: How To Find A Database\u2019s Compatibility Level"}]},{"@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\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-cv","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":214,"url":"https:\/\/sqlnuggets.com\/what-features-are-disabled-between-compatibility-levels-in-sql-server\/","url_meta":{"origin":775,"position":0},"title":"What Features Are Disabled Between Compatibility Levels In SQL Server?","author":"Eric Cobb","date":"March 16, 2016","format":false,"excerpt":"Have you ever wondered what features may or may not be available when your database is running in a different compatibility level than your SQL Server version? I recently came across a situation where a database was on SQL Server 2014 (compatibility level 120), but the database compatibility level was\u2026","rel":"","context":"In &quot;DB Engine&quot;","block_context":{"text":"DB Engine","link":"https:\/\/sqlnuggets.com\/category\/db-engine\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":564,"url":"https:\/\/sqlnuggets.com\/notes-sql-2017-azure-install-trace-flags\/","url_meta":{"origin":775,"position":1},"title":"Notes From A SQL 2017 Azure Install &#8211; Trace Flags","author":"Eric Cobb","date":"October 11, 2017","format":false,"excerpt":"Like most Database Administrators, I have a standard set of Trace Flags that I implement on my SQL Servers.\u00a0 These are my tried and true trusty companions, used to help tune SQL Server to my specific needs. However, with SQL Server 2017 some of my long-time trusty Trace Flags are\u2026","rel":"","context":"In &quot;Azure&quot;","block_context":{"text":"Azure","link":"https:\/\/sqlnuggets.com\/category\/azure\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":93,"url":"https:\/\/sqlnuggets.com\/sql-server-maintenance-scripts\/","url_meta":{"origin":775,"position":2},"title":"SQL Server Maintenance Scripts","author":"Eric Cobb","date":"July 9, 2015","format":false,"excerpt":"When it comes to everyday routine maintenance of your SQL Server databases, you can't beat Ola Hallengren's\u00a0SQL Server Maintenance Solution. \u00a0Focusing on\u00a0SQL Server Backups,\u00a0SQL Server Integrity Checks, and SQL Server Index and Statistics Maintenance, it\u00a0has pretty much become the go-to\u00a0maintenance solution for many SQL Server database administrators. \u00a0We have it\u2026","rel":"","context":"In &quot;Tools&quot;","block_context":{"text":"Tools","link":"https:\/\/sqlnuggets.com\/category\/tools\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":641,"url":"https:\/\/sqlnuggets.com\/sql-server-permissions-manager\/","url_meta":{"origin":775,"position":3},"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":545,"url":"https:\/\/sqlnuggets.com\/sql-scripts-change-owner-database\/","url_meta":{"origin":775,"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":27,"url":"https:\/\/sqlnuggets.com\/automating-backups-for-sql-server-2008-r2-express-edition\/","url_meta":{"origin":775,"position":5},"title":"Automating backups for SQL Server 2008 R2 Express Edition","author":"Eric Cobb","date":"June 9, 2015","format":false,"excerpt":"One of the limitations of SQL Server Express is that you can't automate backups in it via the SQL Server Agent like you can the other versions of SQL Server. You have to manually write the backup scripts and then schedule them to run in Windows Scheduled Tasks. This really\u2026","rel":"","context":"In &quot;Backups&quot;","block_context":{"text":"Backups","link":"https:\/\/sqlnuggets.com\/category\/backups\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/775","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=775"}],"version-history":[{"count":0,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/776"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}