{"id":2982,"date":"2013-03-22T23:32:01","date_gmt":"2013-03-22T23:32:01","guid":{"rendered":"http:\/\/mehmetsalihdeveci.net\/?p=2982"},"modified":"2019-02-28T08:52:46","modified_gmt":"2019-02-28T08:52:46","slug":"sql-server-maintenance-plans-ile-backup-alma","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-maintenance-plans-ile-backup-alma\/","title":{"rendered":"SQL Server Maintenance Plans ile Backup Alma"},"content":{"rendered":"

Merhaba,<\/p>\n

Bu yaz\u0131mda sizlere SQL Server da Maintenance Plans ile Backup plan\u0131 olu\u015fturup uzun vadede tek bir job ile veritaban\u0131n\u0131z\u0131n backup \u0131n\u0131n nas\u0131l al\u0131nabilece\u011fini g\u00f6sterece\u011fim. SQL Server da Backup nas\u0131l al\u0131n\u0131r ne tip backup lar vard\u0131r Backup neden al\u0131n\u0131r bilgisini bilmeyenler bu linkteki yaz\u0131m\u0131<\/a> okuyabilirler.<\/p>\n

Kritik sistemlerin Veritabanlar\u0131 afet (Data Kayb\u0131n\u0131n ya\u015fanabilece\u011fi her olay \u00f6r: Disk Corruption, Sunucunun down olup a\u00e7\u0131lamamas\u0131, Do\u011fal afetler) durumlar\u0131na kar\u015f\u0131 yada kullan\u0131c\u0131 bazl\u0131 hatalara kar\u015f\u0131 (Drop Table, Drop User, Truncate Table, Drop Database) belli aral\u0131klarla mutlaka yedeklerinin al\u0131nmas\u0131 gereklidir.<\/p>\n

Bu Yedekleme stratejisi Database in i\u00e7erdi\u011fi datalar\u0131n kritikli\u011fine g\u00f6re de\u011fi\u015fir. Baz\u0131 veritabanlar\u0131n\u0131n(Kritikli\u011fi az olan) ayda 1 yedekleri al\u0131n\u0131rken baz\u0131 veritabanlar\u0131n\u0131n(Kritik veritabanlar\u0131) haftada 1 full backup \u0131 al\u0131n\u0131rken her g\u00fcn g\u00fcn\u00fcn belirli aral\u0131klar\u0131nda Differential backup \u0131 al\u0131nmaktad\u0131r. Bu backup lar Disklere al\u0131nabildi\u011fi gibi maliyeti \u00e7ok \u00e7ok d\u00fc\u015f\u00fck olan tape lere de al\u0131nabilmektedir.<\/p>\n

Veri hacmi \u00e7ok b\u00fcy\u00fck olan b\u00fcy\u00fck \u015firketler genellikle backup lar\u0131n\u0131 Tape backup \u015feklinde almaktad\u0131r \u00e7\u00fcnk\u00fc petabyte lara varan veriler i\u00e7in disk maliyeti kimi zaman tape in 100 kat\u0131 yada 1000 kat\u0131na \u00e7\u0131kabilmektedir. Bundan dolay\u0131 b\u00fcy\u00fck holdingler petabyte a yak\u0131n databaselerinin backup lar\u0131n\u0131 tape almaktad\u0131r. Bu kadar giri\u015ften sonra \u015fimdi Maintenance Plan ile bir Backup plan\u0131 olu\u015fturup belirli aral\u0131klarla backup al\u0131nmas\u0131n\u0131 g\u00f6sterece\u011fim.<\/p>\n

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

<\/strong><\/p>\n\n

Maintenance Plan<\/strong><\/p>\n

SQL Server da Maintenance Plan ile veritabanlar\u0131m\u0131z\u0131n devaml\u0131l\u0131\u011f\u0131n\u0131 sa\u011flayacak koruyacak ve daha performansl\u0131 \u00e7al\u0131\u015ft\u0131racak d\u00fczenli ve s\u00fcrekli \u00e7al\u0131\u015facak planlar olu\u015fturabiliriz. Bu planlar\u0131 a\u015fa\u011f\u0131daki gibi listeleyebiliriz.<\/p>\n

    \n
  1. Veritabanlar\u0131m\u0131z\u0131n belli bir Schedule a g\u00f6re Full, Differential ve Transaction Log backup lar\u0131n\u0131 d\u00fczenli olarak ald\u0131rabiliriz.<\/span><\/li>\n
  2. Index maintenance yaparak veritaban\u0131m\u0131z\u0131n performans\u0131n\u0131 art\u0131rabiliriz.<\/li>\n
  3. Vertaban\u0131 objelerimizin istatistiklerini g\u00fcncelleyebiriz. \u00d6r: Bir sorgu \u00e7al\u0131\u015fmadan \u00f6nce sql server onun plan\u0131n\u0131 \u00e7\u0131kar\u0131r ve \u00f6yle ko\u015far. Bu istatistikler bazen \u00e7e\u015fitli sebeplerle bozulabiliyor bunlar\u0131n d\u00fczenli aral\u0131klarla kontrol edilip g\u00fcncellenmesi gerekli.<\/li>\n
  4. Veritaban\u0131m\u0131z\u0131n belli aral\u0131klarla b\u00fct\u00fcnl\u00fc\u011f\u00fcn\u00fc kontrol ettirmemiz gerekli bunu manuel olarak DBCC CHECKDB T-Sql koduyla da yapabiliriz. Not: Bu kodu sadece ihtiya\u00e7 duydu\u011fumuzda \u00e7al\u0131\u015ft\u0131rmam\u0131z gerekli.<\/li>\n<\/ol>\n

    Ben bu yaz\u0131mda sizlere Maintenance plan ile Backup plan\u0131 olu\u015fturmay\u0131 anlataca\u011f\u0131m. \u00d6rnek olmas\u0131 a\u00e7\u0131s\u0131ndan Backup stratejim a\u015fa\u011f\u0131daki gibi olacakt\u0131r.<\/p>\n

    TestDatabase ad\u0131ndaki database imin 5 Dk da bir Full Backup \u0131n\u0131 alaca\u011f\u0131m ve 1 saatten daha eski olan Backuplar\u0131n silinmesini sa\u011flayaca\u011f\u0131m. Silmenin sebebi hem 1-2 full backup dan fazlas\u0131n\u0131n gereksiz olmas\u0131ndan dolay\u0131 hemde yer s\u0131k\u0131nt\u0131s\u0131 ya\u015fanmamas\u0131 i\u00e7indir.<\/p>\n

    Not: Normal canl\u0131 sistemlerde b\u00f6yle bir stratejinin olabilece\u011fini d\u00fc\u015f\u00fcnm\u00fcyorum ben burda \u00f6rnek g\u00f6sterebilmek i\u00e7in bu \u015fekilde yapt\u0131m. 5 dk da bir ilgili job \u0131n \u00e7al\u0131\u015ft\u0131\u011f\u0131n\u0131 g\u00f6sterebilmek i\u00e7in bu \u015fekilde yapt\u0131m.<\/p>\n

    Genellikle canl\u0131 sistemlerin backup lar\u0131 haftada yada 3 g\u00fcnde 1 al\u0131n\u0131p her g\u00fcnde Differential backup ve Transaction log backup \u015feklinde al\u0131n\u0131r. Yine belirteyim bu stratejiler kurumdan kuruma veritabanlar\u0131ndan veritabanlar\u0131na g\u00f6re de\u011fi\u015fmektedir.<\/p>\n

    SQL Server Management Studio Kullanarak Maintenance Plan olu\u015fturma<\/strong><\/p>\n

    1.Management Studio dan MYTESTINSTANCE adl\u0131 SQL Instance s\u0131ma ba\u011fland\u0131m ve a\u015fa\u011f\u0131daki gibi daha \u00f6nceden olu\u015fturdu\u011fum TestDatabase adl\u0131 veritaban\u0131yla ilgili i\u015flemleri yapaca\u011f\u0131m.<\/p>\n

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

    2.Maintenance plan tab\u0131 Management Tab\u0131 alt\u0131nda a\u015fa\u011f\u0131daki gibi bulunmaktad\u0131r. Maintenance Plans Tab\u0131na sa\u011f t\u0131klad\u0131\u011f\u0131m\u0131zda a\u015fa\u011f\u0131daki ekran gelecektir.<\/p>\n

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

    3. Bu ad\u0131mda New Maintenance Plan a t\u0131klay\u0131p a\u015fa\u011f\u0131daki gibi Maintenance plan \u0131m\u0131za bir isim veriyoruz. Plan\u0131n Ad\u0131n\u0131 i\u015flemimizden dolay\u0131 Backup diyorum Ok butonuna t\u0131klay\u0131p devam ediyorum.<\/p>\n

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

    4. Yukardaki i\u015flemin devam\u0131nda Management Studio Tool u bize a\u015fa\u011f\u0131daki gibi bir geli\u015ftirme ortam\u0131 sunacakt\u0131r. Burada Toolbox \u0131n i\u00e7erisindeki i\u015flemleri se\u00e7erek plan\u0131m\u0131z\u0131 olu\u015fturabiliriz. Her bir i\u015flem kutucu\u011funun arka plan\u0131nda ilgili T-SQL Kodlar\u0131 bulunmaktad\u0131r.<\/p>\n

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

    5. Yukar\u0131daki ekranda sol altta bulunan ToolBox dan Backup Database Task tool unu s\u00fcr\u00fckleyip pembe ortama s\u00fcr\u00fckl\u00fcyoruz. S\u00fcr\u00fckledi\u011fimizde A\u015fa\u011f\u0131daki gibi kare \u015feklinde bir flow task\u0131 g\u00f6r\u00fcnecektir. Buna \u00e7ift t\u0131klad\u0131\u011f\u0131m\u0131zda Backup plan\u0131 i\u00e7in gerekli ekran a\u015fa\u011f\u0131daki gibi gelecektir.<\/p>\n

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

    6. Backup Database Task ekran\u0131ndan hangi database in Backup \u0131n\u0131n al\u0131naca\u011f\u0131n\u0131, Tape mi Disk e mi al\u0131naca\u011f\u0131, Compress edilip edilmeyece\u011fi gibi opsiyonlar a\u015fa\u011f\u0131daki gibi se\u00e7ilir.<\/p>\n

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

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

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

    7. Bu ad\u0131ma kadar olan ad\u0131mda Backup i\u00e7in gerekli opsiyonlar\u0131 belirledik. TestDatabase adl\u0131 veritaban\u0131n\u0131n Full Backup \u0131n\u0131 Diske S\u0131k\u0131\u015ft\u0131r\u0131lm\u0131\u015f bir \u015fekilde (Compression olacak \u015fekilde) plan olu\u015fturulmu\u015ftur. \u00a0Bu ad\u0131mda Cleanup task\u0131m\u0131z\u0131 da olu\u015fturuyoruz. Disk \u00fczerinde biriken eski backup lar\u0131n silinmesi gerekiyor aksi takdirde disk \u015fi\u015fer ve dolar. Genellikle canl\u0131 sistemlerde en fazla 1 full backuptan 2 tane bulundurulur e\u011fer yer var ise al\u0131nan yeni backuplardan sonra genellikle eski backup lar silinir. Bunun i\u00e7inde Otomatik Cleanup task\u0131 olu\u015fturuyoruz. Soldaki History Cleanup Task \u0131 s\u00fcr\u00fckleyip flowla ba\u011fl\u0131yoruz. Cleanuo task\u0131 m\u0131za Backup al\u0131nan file \u0131 tan\u0131t\u0131p burada 1 saatten eski Backup lar\u0131n silinmesini setliyoruz a\u015fa\u011f\u0131daki gibi.<\/p>\n

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

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

    8. Bu ad\u0131ma kadar Backup ve Cleanup task ini olu\u015fturduk. Bu taskleri hangi aral\u0131klarda \u00e7al\u0131\u015fmas\u0131 gerekti\u011fini plana a\u015fa\u011f\u0131daki schedule dan ekliyoruz. Schedule u sa\u011f \u00fcst k\u00f6\u015fedeki Takvim ikonun t\u0131klayarak a\u015fa\u011f\u0131daki gibi a\u00e7\u0131yoruz. Schedule \u0131n her g\u00fcn 24 saat aral\u0131ks\u0131z olarak her 5 dk da bir \u00e7al\u0131\u015fmas\u0131 \u015feklinde a\u015fa\u011f\u0131daki gibi setlendi.<\/p>\n

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

    9. Taskleri tamamlad\u0131ktan sonra kaydedip \u00e7\u0131k\u0131yoruz. \u00c7\u0131kt\u0131ktan sonra a\u015fa\u011f\u0131daki gibi SQL Server Agent \u0131n alt\u0131nda Jobs Sekmesinin alt\u0131nda Maintanance Plan sonucu olu\u015fan Joblar create edilmi\u015f.<\/p>\n

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

    Job \u0131n \u00e7al\u0131\u015f\u0131p \u00e7al\u0131\u015fmad\u0131\u011f\u0131n\u0131 kontrol etti\u011fimizde a\u015fa\u011f\u0131daki gibi backup lar\u0131n al\u0131nd\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz.<\/p>\n

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

    Yukardaki g\u00f6r\u00fcnt\u00fcden Backuplar\u0131n al\u0131nd\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz. Cleanup task \u0131n\u0131n \u00e7al\u0131\u015f\u0131p \u00e7al\u0131\u015fmad\u0131\u011f\u0131n\u0131da bir ka\u00e7 dakka sonra bakt\u0131\u011f\u0131m\u0131zda g\u00f6r\u00fcyoruz. A\u015fa\u011f\u0131daki g\u00f6r\u00fcnt\u00fcden de g\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere 17:30 da al\u0131nan backup dan sonra \u00fcstteki resimde g\u00f6r\u00fclen 16:30 daki backup \u0131n silinmi\u015f oldu\u011funu g\u00f6r\u00fcyoruz. B\u00f6ylece hem Backup task \u0131 hemde Cleanup task i \u00e7al\u0131\u015f\u0131yor.<\/p>\n

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

    Son olarak Job \u0131m\u0131z\u0131n History sine bakarak hatalar\u0131 g\u00f6rebilir job \u0131n ge\u00e7mi\u015fte neler yapt\u0131\u011f\u0131n\u0131 a\u015fa\u011f\u0131daki gibi g\u00f6rebilirz. Bunun i\u00e7in Job a gelip Sa\u011f t\u0131klay\u0131p gelen men\u00fcde View History e t\u0131klarsak a\u015fa\u011f\u0131daki gibi ilgili Job \u0131n history sini g\u00f6rebiliriz.<\/p>\n

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

    \u00a0<\/a><\/p>\n

    B\u00f6ylece bir yaz\u0131m\u0131n daha sonuna gelmi\u015f bulunmaktay\u0131m. Bir sonraki yaz\u0131da bulu\u015fmak dile\u011fiyle…<\/p>\n

    Esen Kal\u0131n….<\/p>\n\n

     <\/p>\n

    Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda a\u015fa\u011f\u0131daki konularda 7×24 Uzman Dan\u0131\u015fmanlara yada E\u011fitimlere mi\u00a0<\/span>\u0130htiyac\u0131n\u0131z var mehmet.deveci@gridgroup.com.tr adresine mail atarak Bizimle ileti\u015fime ge\u00e7ebilirsiniz.<\/span><\/p>\n

    – Oracle Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle Veritaban\u0131 Bak\u0131m ve Destek<\/span>
    \n– Exadata Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Exadata Bak\u0131m ve Destek<\/span>
    \n– SQL Server Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– SQL Server Veritaban\u0131 Bak\u0131m ve Destek<\/span>
    \n– Goldengate Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Goldengate Bak\u0131m ve Destek<\/span>
    \n– Linux Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Linux Bak\u0131m ve Destek<\/span>
    \n– Oracle EBS Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle EBS Bak\u0131m ve Destek<\/span>
    \n– Weblogic Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Weblogic Bak\u0131m ve Destek<\/span>
    \n– Oracle Veritaban\u0131 E\u011fitimleri<\/span>
    \n– Oracle VM Server Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle VM Server Bak\u0131m ve Destek<\/span>
    \n– Oracle EPPM Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle EPPM Bak\u0131m ve Destek<\/span>
    \n– Oracle Primavera Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle Primavera Bak\u0131m ve Destek<\/span>
    \n– Oracle E\u011fitimleri<\/span>
    \n– SQL Server E\u011fitimleri<\/span>
    \n– Goldengate E\u011fitimleri<\/span>
    \n– Exadata E\u011fitimleri<\/span>
    \n– Linux E\u011fitimleri<\/span>
    \n– Oracle EBS E\u011fitimleri<\/span>
    \n– Oracle VM Server E\u011fitimleri<\/span>
    \n– Weblogic E\u011fitimleri<\/span>
    \n– Oracle EPPM E\u011fitimleri<\/span>
    \n– Oracle Primavera E\u011fitimleri<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

    Merhaba, Bu yaz\u0131mda sizlere SQL Server da Maintenance Plans ile Backup plan\u0131 olu\u015fturup uzun vadede tek bir job ile veritaban\u0131n\u0131z\u0131n backup \u0131n\u0131n nas\u0131l al\u0131nabilece\u011fini g\u00f6sterece\u011fim. SQL Server da Backup nas\u0131l al\u0131n\u0131r ne tip backup lar vard\u0131r Backup neden al\u0131n\u0131r bilgisini bilmeyenler bu linkteki yaz\u0131m\u0131 okuyabilirler. Kritik sistemlerin Veritabanlar\u0131 afet (Data Kayb\u0131n\u0131n ya\u015fanabilece\u011fi her olay \u00f6r: …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[19,23],"tags":[204,508,1031,1515,1577,1583,1620,1625,1628,1654,1655],"class_list":["post-2982","post","type-post","status-publish","format-standard","","category-sql-server","category-veritabani","tag-backup","tag-deveci","tag-mehmet-salih-deveci","tag-salih-deveci","tag-sql-server","tag-sql-server-2008","tag-sql-server-backup","tag-sql-server-da-backup-plani","tag-sql-server-da-maintenance-plans-olusturma","tag-sql-server-maintenace-plans","tag-sql-server-management-studio"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/2982","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=2982"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/2982\/revisions"}],"predecessor-version":[{"id":5896,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/2982\/revisions\/5896"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=2982"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=2982"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=2982"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}