{"id":302,"date":"2018-01-30T22:12:16","date_gmt":"2018-01-30T21:12:16","guid":{"rendered":"http:\/\/wchmurze.cloud\/?p=302"},"modified":"2019-08-18T14:55:07","modified_gmt":"2019-08-18T12:55:07","slug":"zanim-blad-i-szef-zapukaja-do-drzwi","status":"publish","type":"post","link":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/","title":{"rendered":"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi"},"content":{"rendered":"

Czy zda\u017cy\u0142o si\u0119 wam odtwarza\u0107 po raz kolejny baz\u0119 danych po logicznym uszkodzeniu plik\u00f3w?<\/p>\n

Wla\u015bciwie kiedy ostatnio sprawdzano j\u0105 dost\u0119pnymi narz\u0119dziami?
\nKiedy ostanio dokonano pe\u0142nej, r\u00f3\u017cnicowej kopii bezpiecze\u0144stwa, jak cz\u0119sto odk\u0142adane s\u0105 logi transakcyjne?
\nCzy baza ma poprawny model recovery ?
\nJe\u015bli na chocia\u017c jedno pytanie pojawi\u0142y si\u0119 w g\u0142owie w\u0105tpliwo\u015bci, to dobrze, \u017ce przed awari\u0105…<\/p>\n

 <\/p>\n

Pomocny skrypt<\/p>\n

 if object_id('tempdb..#DBInfo') is not null\r\n\t\tDROP TABLE #DBInfo\r\n if object_id('tempdb..#DBCC') is not null\r\n\t\tDROP TABLE #DBCC\r\nCREATE TABLE #DBInfo (\r\n       Id bigint IDENTITY(1,1),\r\n       ParentObject VARCHAR(255),\r\n       [Object] VARCHAR(255),\r\n       Field VARCHAR(255),\r\n       [Value] VARCHAR(255)\r\n)\r\nCREATE TABLE #DBCC(\r\nDatabaseName VARCHAR(255),\r\nLastDBCCCHeckDB_RunDate VARCHAR(255)\r\n)\r\nEXECUTE SP_MSFOREACHDB'INSERT INTO #DBInfo Execute (''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'');\r\nINSERT INTO #DBCC (DatabaseName) SELECT [Value] FROM #DBInfo WHERE Field IN (''dbi_dbname'');\r\nUPDATE #DBCC SET LastDBCCCHeckDB_RunDate=(SELECT TOP 1 [Value] FROM #DBInfo WHERE Field IN (''dbi_dbccLastKnownGood'')) where LastDBCCCHeckDB_RunDate is NULL;\r\nTRUNCATE TABLE #DBInfo';\r\n  \r\nSELECT \r\n      database_name = DB_NAME(d.database_id)\r\n    , log_size_gb = CAST(SUM(CASE WHEN d.type_desc = 'LOG' THEN d.size END) * 8. \/ 1024\/1024 AS DECIMAL(10,4))\r\n    , row_size_gb = CAST(SUM(CASE WHEN d.type_desc = 'ROWS' THEN d.size END) * 8. \/ 1024\/1024 AS DECIMAL(10,4))\r\n    , total_size_gb = CAST(SUM(d.size * 8. \/ 1024\/1024) AS DECIMAL(10,4)),\r\n       db.recovery_model_desc,\r\n       cc.LastDBCCCHeckDB_RunDate\r\nFROM sys.master_files  d WITH(NOWAIT)\r\njoin  #DBCC cc\r\non (cc.DatabaseName=DB_NAME(d.database_id) )\r\njoin sys.databases  db\r\non (db.database_id=d.database_id)\r\n\r\nGROUP BY d.database_id,cc.LastDBCCCHeckDB_RunDate,db.recovery_model_desc\r\norder by cc.LastDBCCCHeckDB_RunDate,4 desc\r\n\r\n\r\n;WITH LastBackUp AS\r\n(\r\nSELECT  bs.database_name,\r\n        bs.backup_size,\r\n        bs.backup_start_date,\r\n        bmf.physical_device_name,\r\n        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )\r\nFROM  msdb.dbo.backupmediafamily bmf\r\nJOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id\r\nJOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id\r\nWHERE   bs.[type] = 'D'\r\nAND bs.is_copy_only = 0\r\n)\r\nSELECT \r\n       database_name AS [Database],\r\n       CAST(backup_size \/ 1024\/1024\/1024 AS DECIMAL(10, 2) ) AS [BackupSizeMB],\r\n        backup_start_date AS [Last Full DB Backup Date],\r\n        physical_device_name AS [Backup File Location]\r\nFROM LastBackUp\r\nWHERE Position = 1\r\nORDER BY [Database];\r\n\r\n\r\n<\/pre>\n
Hipotetyczny rezultat powy\u017cszego zapytania<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
database_name<\/td>\ntotal_size_gb<\/td>\nrecovery_model_desc<\/td>\nLastDBCCCHeckDB_RunDate<\/td>\n<\/tr>\n
XYZ<\/td>\n153.8079<\/td>\nSIMPLE<\/td>\n1900-01-01 00:00:00.000<\/td>\n<\/tr>\n
tempdb<\/td>\n58.6021<\/td>\nSIMPLE<\/td>\n1900-01-01 00:00:00.000<\/td>\n<\/tr>\n
HURT<\/td>\n49.2092<\/td>\nSIMPLE<\/td>\n1900-01-01 00:00:00.000<\/td>\n<\/tr>\n
ABC<\/td>\n46.6123<\/td>\nSIMPLE<\/td>\n1900-01-01 00:00:00.000<\/td>\n<\/tr>\n
ZZA<\/strong><\/td>\n0.4893<\/strong><\/td>\nFULL<\/strong><\/td>\n1900-01-01 00:00:00.000<\/strong><\/td>\n<\/tr>\n
model<\/td>\n0.1470<\/td>\nSIMPLE<\/td>\n1900-01-01 00:00:00.000<\/td>\n<\/tr>\n
master<\/td>\n0.0148<\/td>\nSIMPLE<\/td>\n1900-01-01 00:00:00.000<\/td>\n<\/tr>\n
msdb<\/td>\n5.8594<\/td>\nSIMPLE<\/td>\n2015-10-06 13:34:30.730<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Na co zwr\u00f3ci\u0107 uwag\u0119 ?<\/p>\n
Bazy systemowe (master, model,msdb,tempdb) maj\u0105 ustawiony recovery model r\u00f3wny SIMPLE. \u017badna z nich, poza msdb nie by\u0142a nigdy sprawdzona narz\u0119dziem DBCC CheckDB ? To dobrze ?<\/p>\n
Jedyna baza w trybie FULL te\u017a nigdy nie zosta\u0142a sprawdzona.<\/p>\n
Do odwa\u017cnych \u015bwiat nale\u017cy,ale nie do nieroztropnych.<\/p>\n
https:\/\/www.mssqltips.com\/sqlservertip\/4381\/sql-server-dbcc-checkdb-overview\/<\/p>\n","protected":false},"excerpt":{"rendered":"
Czy zda\u017cy\u0142o si\u0119 wam odtwarza\u0107 po raz kolejny baz\u0119 danych po logicznym uszkodzeniu plik\u00f3w? Wla\u015bciwie kiedy ostatnio sprawdzano j\u0105 dost\u0119pnymi narz\u0119dziami? Kiedy ostanio dokonano pe\u0142nej, r\u00f3\u017cnicowej kopii bezpiecze\u0144stwa, jak cz\u0119sto odk\u0142adane s\u0105 logi transakcyjne? Czy baza ma poprawny model recovery … Continued<\/a><\/p>\n","protected":false},"author":1,"featured_media":1073,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[14,8,2],"tags":[],"yoast_head":"\nZanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi - W chmurze o chmurze i nie tylko<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/\" \/>\n<meta property=\"og:locale\" content=\"pl_PL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi - W chmurze o chmurze i nie tylko\" \/>\n<meta property=\"og:description\" content=\"Czy zda\u017cy\u0142o si\u0119 wam odtwarza\u0107 po raz kolejny baz\u0119 danych po logicznym uszkodzeniu plik\u00f3w? Wla\u015bciwie kiedy ostatnio sprawdzano j\u0105 dost\u0119pnymi narz\u0119dziami? Kiedy ostanio dokonano pe\u0142nej, r\u00f3\u017cnicowej kopii bezpiecze\u0144stwa, jak cz\u0119sto odk\u0142adane s\u0105 logi transakcyjne? Czy baza ma poprawny model recovery … Continued\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/\" \/>\n<meta property=\"og:site_name\" content=\"W chmurze o chmurze i nie tylko\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-30T21:12:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-08-18T12:55:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/wchmurze.cloud\/wp-content\/uploads\/2018\/04\/sql-glass.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"700\" \/>\n\t<meta property=\"og:image:height\" content=\"467\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"djkormo\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Napisane przez\" \/>\n\t<meta name=\"twitter:data1\" content=\"djkormo\" \/>\n\t<meta name=\"twitter:label2\" content=\"Szacowany czas czytania\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minuty\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/\"},\"author\":{\"name\":\"djkormo\",\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323\"},\"headline\":\"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi\",\"datePublished\":\"2018-01-30T21:12:16+00:00\",\"dateModified\":\"2019-08-18T12:55:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/\"},\"wordCount\":190,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323\"},\"articleSection\":[\"implementacja\",\"migawka\",\"t-sql\"],\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/\",\"url\":\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/\",\"name\":\"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi - W chmurze o chmurze i nie tylko\",\"isPartOf\":{\"@id\":\"https:\/\/wchmurze.cloud\/#website\"},\"datePublished\":\"2018-01-30T21:12:16+00:00\",\"dateModified\":\"2019-08-18T12:55:07+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/#breadcrumb\"},\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Strona g\u0142\u00f3wna\",\"item\":\"https:\/\/wchmurze.cloud\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/wchmurze.cloud\/#website\",\"url\":\"https:\/\/wchmurze.cloud\/\",\"name\":\"W chmurze o chmurze i nie tylko\",\"description\":\"W chmurze o chmurze i nie tylko\",\"publisher\":{\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/wchmurze.cloud\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"pl-PL\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323\",\"name\":\"djkormo\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pl-PL\",\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/14a901b808871fa98086ae259c45d646?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/14a901b808871fa98086ae259c45d646?s=96&d=mm&r=g\",\"caption\":\"djkormo\"},\"logo\":{\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/image\/\"},\"url\":\"https:\/\/wchmurze.cloud\/index.php\/author\/djkormo\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi - W chmurze o chmurze i nie tylko","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:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/","og_locale":"pl_PL","og_type":"article","og_title":"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi - W chmurze o chmurze i nie tylko","og_description":"Czy zda\u017cy\u0142o si\u0119 wam odtwarza\u0107 po raz kolejny baz\u0119 danych po logicznym uszkodzeniu plik\u00f3w? Wla\u015bciwie kiedy ostatnio sprawdzano j\u0105 dost\u0119pnymi narz\u0119dziami? Kiedy ostanio dokonano pe\u0142nej, r\u00f3\u017cnicowej kopii bezpiecze\u0144stwa, jak cz\u0119sto odk\u0142adane s\u0105 logi transakcyjne? Czy baza ma poprawny model recovery … Continued","og_url":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/","og_site_name":"W chmurze o chmurze i nie tylko","article_published_time":"2018-01-30T21:12:16+00:00","article_modified_time":"2019-08-18T12:55:07+00:00","og_image":[{"width":700,"height":467,"url":"https:\/\/wchmurze.cloud\/wp-content\/uploads\/2018\/04\/sql-glass.jpg","type":"image\/jpeg"}],"author":"djkormo","twitter_card":"summary_large_image","twitter_misc":{"Napisane przez":"djkormo","Szacowany czas czytania":"2 minuty"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/#article","isPartOf":{"@id":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/"},"author":{"name":"djkormo","@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323"},"headline":"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi","datePublished":"2018-01-30T21:12:16+00:00","dateModified":"2019-08-18T12:55:07+00:00","mainEntityOfPage":{"@id":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/"},"wordCount":190,"commentCount":2,"publisher":{"@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323"},"articleSection":["implementacja","migawka","t-sql"],"inLanguage":"pl-PL","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/","url":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/","name":"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi - W chmurze o chmurze i nie tylko","isPartOf":{"@id":"https:\/\/wchmurze.cloud\/#website"},"datePublished":"2018-01-30T21:12:16+00:00","dateModified":"2019-08-18T12:55:07+00:00","breadcrumb":{"@id":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/#breadcrumb"},"inLanguage":"pl-PL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/wchmurze.cloud\/index.php\/2018\/01\/30\/zanim-blad-i-szef-zapukaja-do-drzwi\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Strona g\u0142\u00f3wna","item":"https:\/\/wchmurze.cloud\/"},{"@type":"ListItem","position":2,"name":"Zanim b\u0142\u0105d i szef zapukaj\u0105 do drzwi"}]},{"@type":"WebSite","@id":"https:\/\/wchmurze.cloud\/#website","url":"https:\/\/wchmurze.cloud\/","name":"W chmurze o chmurze i nie tylko","description":"W chmurze o chmurze i nie tylko","publisher":{"@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/wchmurze.cloud\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"pl-PL"},{"@type":["Person","Organization"],"@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323","name":"djkormo","image":{"@type":"ImageObject","inLanguage":"pl-PL","@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/14a901b808871fa98086ae259c45d646?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/14a901b808871fa98086ae259c45d646?s=96&d=mm&r=g","caption":"djkormo"},"logo":{"@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/image\/"},"url":"https:\/\/wchmurze.cloud\/index.php\/author\/djkormo\/"}]}},"_links":{"self":[{"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/posts\/302"}],"collection":[{"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=302"}],"version-history":[{"count":11,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/posts\/302\/revisions"}],"predecessor-version":[{"id":313,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/posts\/302\/revisions\/313"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/media\/1073"}],"wp:attachment":[{"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=302"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=302"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=302"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}