{"id":188,"date":"2025-05-04T11:37:29","date_gmt":"2025-05-04T11:37:29","guid":{"rendered":"https:\/\/www.webmobkey.com\/blog\/?p=188"},"modified":"2025-11-16T10:22:02","modified_gmt":"2025-11-16T10:22:02","slug":"c-ile-sql-veritabani-yedekleme-otomasyonu","status":"publish","type":"post","link":"https:\/\/www.webmobdesign.com\/blog\/tr\/c-ile-sql-veritabani-yedekleme-otomasyonu\/","title":{"rendered":"C# ile SQL Veritaban\u0131 Yedekleme Otomasyonu"},"content":{"rendered":"\n<p id=\"e2d7\">Bu yaz\u0131m\u0131zda, C# ile otomatik SQL veritaban\u0131 yedekleme \u00e7\u00f6z\u00fcm\u00fcn\u00fc ele alaca\u011f\u0131z. Ama\u00e7, veritaban\u0131n\u0131 belirli bir saatte her g\u00fcn otomatik olarak yedeklemek ve eski yedeklerin belirli bir say\u0131ya ula\u015ft\u0131\u011f\u0131nda temizlenmesini sa\u011flamakt\u0131r.<\/p>\n\n\n\n<p id=\"db25\"><strong>\u00d6nce indirilmesi gereken paketler<\/strong><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: xml; title: ; notranslate\" title=\"\">\n.Net CLI\ndotnet add package Microsoft.Data.SqlClient --version 6.0.1\nPackage Manager\nNuGet\\Install-Package Microsoft.Data.SqlClient -Version 6.0.1\n<\/pre><\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"a2b8\">\u00c7\u00f6z\u00fcm\u00fcn Temel \u00d6zellikleri<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Otomatik Yedekleme<\/strong>: Uygulama, her g\u00fcn belirli bir saatte (\u00f6rne\u011fin, 21:15) veritaban\u0131 yede\u011fini otomatik olarak al\u0131r.<\/li>\n\n\n\n<li><strong>Yedek Dosyas\u0131 Depolama<\/strong>: Yedek dosyas\u0131, \u015fu anki tarih ve saat bilgisiyle adland\u0131r\u0131l\u0131r ve&nbsp;<code>.sql<\/code>&nbsp;uzant\u0131s\u0131yla kaydedilir.<\/li>\n\n\n\n<li><strong>Dosya Temizleme<\/strong>: Yedekleme dosyalar\u0131 d\u00fczenli olarak kontrol edilir ve belirli bir say\u0131y\u0131 a\u015fan eski yedek dosyalar\u0131 silinir.<\/li>\n\n\n\n<li><strong>Veritaban\u0131na Eri\u015fim<\/strong>:&nbsp;<code>SqlConnection<\/code>&nbsp;kullan\u0131larak SQL Server veritaban\u0131na ba\u011flan\u0131l\u0131r ve her bir tablodan veri \u00e7ekilir.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"b971\">Kodun \u00c7al\u0131\u015fma Prensibi<\/h4>\n\n\n\n<p id=\"ae37\">A\u015fa\u011f\u0131da, otomatik yedekleme i\u015flemini ger\u00e7ekle\u015ftiren C# kodunu bulacaks\u0131n\u0131z. Ad\u0131m ad\u0131m a\u00e7\u0131klamalar\u0131yla birlikte detaylar\u0131 inceleyece\u011fiz.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: xml; title: ; notranslate\" title=\"\">\nusing Microsoft.Data.SqlClient;\nusing System;\nusing System.Data;\nusing System.IO;\nusing System.Text;\nusing System.Threading;\nusing System.Collections.Generic;\n\nclass Program\n{\n    static int maxFileCount = 14;\n\n    static void Main(string&#x5B;] args)\n    {\n        Console.WriteLine(&quot;Database Backup Service Started.&quot;);\n        while (true)\n        {\n            var now = DateTime.Now;\n            if (now.Hour == 03 &amp;&amp; now.Minute == 30)\n            {\n                try\n                {\n                    BackupDatabase();\n                    Console.WriteLine($&quot;Backup completed at {DateTime.Now:yyyy-MM-dd HH:mm:ss}&quot;);\n                }\n                catch (Exception ex)\n                {\n                    Console.WriteLine($&quot;Error during backup: {ex.Message}&quot;);\n                }\n                Thread.Sleep(TimeSpan.FromMinutes(1));\n            }\n            Thread.Sleep(TimeSpan.FromSeconds(30));\n        }\n    }\n\n    private static void BackupDatabase()\n    {\n        string connectionString = @&quot;Server=localhost;Database=..........;User Id=......;Password=......;\n                                Trusted_Connection=True;TrustServerCertificate=True;...........;&quot;;\n        string backupDirectory = Path.GetFullPath(\n            Path.Combine(AppDomain.CurrentDomain.BaseDirectory, &quot;..&quot;, &quot;..&quot;, &quot;..&quot;, &quot;Yedekler&quot;)\n        );\n        Directory.CreateDirectory(backupDirectory);\n        string backupFileName = Path.Combine(\n            backupDirectory,\n            $&quot;{DateTime.Now:yyyy-MM-dd_HH-mm-ss}.sql&quot;\n        );\n\n        try\n        {\n            using SqlConnection connection = new SqlConnection(connectionString);\n            connection.Open();\n\n            var builder = new StringBuilder();\n            builder.AppendLine(&quot;-- Database Schema Backup&quot;);\n\n            using (\n                SqlCommand schemaCommand = new SqlCommand(\n                    @&quot;SELECT 'CREATE TABLE &#x5B;' + t.TABLE_SCHEMA + '].&#x5B;' + t.TABLE_NAME + '] (' + \n              STUFF((SELECT ', &#x5B;' + ISNULL(c.COLUMN_NAME, 'UNKNOWN_COLUMN') + '] ' + \n                    CASE \n                        WHEN ic.NAME IS NOT NULL \n                            THEN c.DATA_TYPE + ' IDENTITY(' + CAST(ISNULL(ic.SEED_VALUE, 1) AS VARCHAR) + ',' + CAST(ISNULL(ic.INCREMENT_VALUE, 1) AS VARCHAR) + ')'\n                        WHEN c.DATA_TYPE IN ('nvarchar', 'varchar', 'varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 \n                            THEN c.DATA_TYPE + '(MAX)' \n                        WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL \n                            THEN c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' \n                        ELSE c.DATA_TYPE \n                    END +\n                    CASE \n                        WHEN pk.COLUMN_NAME IS NOT NULL \n                            THEN ' PRIMARY KEY'\n                        ELSE ''\n                    END\n              FROM INFORMATION_SCHEMA.COLUMNS c\n              LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc \n                  ON c.TABLE_NAME = tc.TABLE_NAME AND c.TABLE_SCHEMA = tc.TABLE_SCHEMA AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'\n              LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk \n                  ON c.TABLE_NAME = pk.TABLE_NAME AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA AND c.COLUMN_NAME = pk.COLUMN_NAME\n              LEFT JOIN sys.identity_columns ic\n                  ON OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME), 'U') = ic.OBJECT_ID AND c.COLUMN_NAME = ic.NAME\n              WHERE c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA\n              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ');' \n              FROM INFORMATION_SCHEMA.TABLES t\n              WHERE t.TABLE_TYPE = 'BASE TABLE'&quot;,\n                    connection\n                )\n            )\n\n            using (SqlDataReader schemaReader = schemaCommand.ExecuteReader())\n            {\n                while (schemaReader.Read())\n                {\n                    builder.AppendLine(schemaReader.GetString(0));\n                    builder.AppendLine(&quot;GO&quot;);  \/\/ CREATE TABLE komutundan sonra GO ekle\n                }\n            }\n\n            using (\n                SqlCommand tablesCommand = new SqlCommand(\n                    &quot;SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'&quot;,\n                    connection\n                )\n            )\n            using (SqlDataReader tablesReader = tablesCommand.ExecuteReader())\n            {\n                List&lt;(string schema, string table)&gt; tables = new List&lt;(string, string)&gt;();\n\n                while (tablesReader.Read())\n                {\n                    tables.Add((tablesReader.GetString(0), tablesReader.GetString(1)));\n                }\n\n                tablesReader.Close();\n\n                foreach (var (schemaName, tableName) in tables)\n                {\n                    string fullTableName = $&quot;&#x5B;{schemaName}].&#x5B;{tableName}]&quot;;\n\n                    \/\/ Ger\u00e7ekten IDENTITY \u00f6zelli\u011fi var m\u0131 kontrol et\n                    bool hasIdentityColumn = false;\n                    using (\n                        SqlCommand identityCheckCommand = new SqlCommand(\n                            $@&quot;\n                        SELECT COUNT(*) FROM sys.identity_columns \n                        WHERE OBJECT_ID(QUOTENAME('{schemaName}') + '.' + QUOTENAME('{tableName}'), 'U') IS NOT NULL&quot;,\n                            connection\n                        )\n                    )\n                    {\n                        hasIdentityColumn = (int)identityCheckCommand.ExecuteScalar() &gt; 0;\n                    }\n\n                    \/\/ Sadece IDENTITY olan tablolar i\u00e7in IDENTITY_INSERT a\u00e7\n                    if (hasIdentityColumn)\n                    {\n                        builder.AppendLine($&quot;SET IDENTITY_INSERT {fullTableName} ON;&quot;);\n                    }\n\n                    using (\n                        SqlCommand dataCommand = new SqlCommand(\n                            $&quot;SELECT * FROM {fullTableName}&quot;,\n                            connection\n                        )\n                    )\n                    using (SqlDataReader dataReader = dataCommand.ExecuteReader())\n                    {\n                        while (dataReader.Read())\n                        {\n                            var columns = new StringBuilder();\n                            var values = new StringBuilder();\n\n                            for (int i = 0; i &lt; dataReader.FieldCount; i++)\n                            {\n                                string columnName = dataReader.GetName(i);\n                                object value = dataReader&#x5B;i];\n\n                                columns.Append($&quot;&#x5B;{columnName}], &quot;);\n                                values.Append(\n                                    FormatSqlValue(value, dataReader.GetFieldType(i)) + &quot;, &quot;\n                                );\n                            }\n\n                            if (columns.Length &gt; 2)\n                                columns.Length -= 2;\n                            if (values.Length &gt; 2)\n                                values.Length -= 2;\n\n                            string insertStatement =\n                                $&quot;INSERT INTO {fullTableName} ({columns}) VALUES ({values});&quot;;\n                            builder.AppendLine(insertStatement);\n                            builder.AppendLine(&quot;GO&quot;); \/\/ Her INSERT sonras\u0131 GO ekle\n                        }\n                    }\n\n                    \/\/ Sadece IDENTITY olan tablolar i\u00e7in IDENTITY_INSERT kapat\n                    if (hasIdentityColumn)\n                    {\n                        builder.AppendLine($&quot;SET IDENTITY_INSERT {fullTableName} OFF;&quot;);\n                    }\n                }\n            }\n\n            File.WriteAllText(backupFileName, builder.ToString(), Encoding.UTF8);\n            Console.WriteLine($&quot;Database backup saved to {backupFileName}&quot;);\n\n            \/\/ Temizleme i\u015flemi (ge\u00e7ici dosyalar veya gereksiz yedekler vb.)\n            CleanupBackupFiles(backupDirectory, backupFileName);\n        }\n        catch (Exception ex)\n        {\n            Console.WriteLine($&quot;Error during backup: {ex.Message}&quot;);\n        }\n    }\n\n    private static void CleanupBackupFiles(string backupDirectory, string latestBackupFile)\n    {\n        try\n        {\n            var backupFiles = Directory.GetFiles(backupDirectory, &quot;*.sql&quot;);\n\n            \/\/ E\u011fer yedek dosya say\u0131s\u0131 belirtilen s\u0131n\u0131rdan fazlaysa\n            if (backupFiles.Length &gt; maxFileCount)\n            {\n                \/\/ Yeni eklenen dosyay\u0131 listeden \u00e7\u0131kar\n                var oldBackupFiles = backupFiles\n                    .Where(f =&gt; f != latestBackupFile) \/\/ Yeni yede\u011fi hari\u00e7 tut\n                    .OrderBy(f =&gt; File.GetCreationTime(f)) \/\/ En eski dosya ilk s\u0131rada olur\n                    .ToList();\n\n                \/\/ Silinecek dosya say\u0131s\u0131n\u0131 hesapla\n                int filesToDelete = backupFiles.Length - maxFileCount;\n\n                \/\/ En eski dosyalardan silinmesi gerekenleri sil\n                for (int i = 0; i &lt; filesToDelete &amp;&amp; i &lt; oldBackupFiles.Count; i++)\n                {\n                    File.Delete(oldBackupFiles&#x5B;i]);\n                    Console.WriteLine($&quot;Deleted old backup file: {oldBackupFiles&#x5B;i]}&quot;);\n                }\n            }\n        }\n        catch (Exception ex)\n        {\n            Console.WriteLine($&quot;Error during file cleanup: {ex.Message}&quot;);\n        }\n    }\n\n    private static string FormatSqlValue(object value, Type type)\n    {\n        if (value == DBNull.Value || value == null)\n            return &quot;NULL&quot;;\n\n        if (type == typeof(string) || type == typeof(char))\n            return $&quot;'{value.ToString().Replace(&quot;'&quot;, &quot;''&quot;)}'&quot;;\n\n        if (type == typeof(DateTime))\n            return $&quot;'{((DateTime)value).ToString(&quot;yyyy-MM-dd HH:mm:ss&quot;)}'&quot;;\n\n        if (type == typeof(bool))\n            return ((bool)value) ? &quot;1&quot; : &quot;0&quot;;\n\n        if (type == typeof(decimal) || type == typeof(double) || type == typeof(float))\n            return Convert.ToString(value, System.Globalization.CultureInfo.InvariantCulture);\n\n        if (type == typeof(byte&#x5B;]))\n        {\n            if (value == null || ((byte&#x5B;])value).Length == 0)\n                return &quot;NULL&quot;;\n            return $&quot;CONVERT(varbinary(MAX), '{Convert.ToBase64String((byte&#x5B;])value)}', 0)&quot;;\n        }\n\n        return value?.ToString() ?? &quot;NULL&quot;;\n    }\n}\n<\/pre><\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"9a95\">Kodun A\u00e7\u0131klamas\u0131<\/h4>\n\n\n\n<p id=\"8ebd\"><strong>Main Fonksiyonu<\/strong>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Program ba\u015flad\u0131\u011f\u0131nda s\u00fcrekli bir d\u00f6ng\u00fcye girer ve her saniye saati kontrol eder. Saat 21:15 oldu\u011funda&nbsp;<code>BackupDatabase<\/code>&nbsp;fonksiyonunu \u00e7a\u011f\u0131r\u0131r ve bir dakika s\u00fcreyle bekler.<\/li>\n<\/ul>\n\n\n\n<p id=\"69f6\"><strong>BackupDatabase Fonksiyonu<\/strong>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Veritaban\u0131na ba\u011flanmak i\u00e7in&nbsp;<code>SqlConnection<\/code>&nbsp;kullan\u0131l\u0131r ve ard\u0131ndan veritaban\u0131ndaki her tablonun verileri bir&nbsp;<code>INSERT INTO<\/code>&nbsp;komutuyla yedek dosyas\u0131na yaz\u0131l\u0131r.<\/li>\n\n\n\n<li>Yedek dosyalar\u0131 belirli bir dizine kaydedilir. E\u011fer dizinde fazla dosya varsa, eski dosyalar silinir.<\/li>\n<\/ul>\n\n\n\n<p id=\"724b\"><strong>CleanupBackupFiles Fonksiyonu<\/strong>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Yedekleme dosyalar\u0131n\u0131 kontrol eder ve belirlenen say\u0131y\u0131 a\u015fan dosyalar\u0131 siler.<\/li>\n<\/ul>\n\n\n\n<p id=\"34a4\"><strong>FormatSqlValue Fonksiyonu<\/strong>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL de\u011ferlerini uygun formata d\u00f6n\u00fc\u015ft\u00fcr\u00fcr, \u00f6rne\u011fin string\u2019leri tek t\u0131rnak i\u00e7ine al\u0131r, tarihleri do\u011fru formatta kaydeder ve&nbsp;<code>byte[]<\/code>&nbsp;verileri base64 format\u0131nda kaydeder.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"c2f0\">Sonu\u00e7<\/h4>\n\n\n\n<p id=\"f587\">Bu otomatik yedekleme \u00e7\u00f6z\u00fcm\u00fc, veritaban\u0131 yedeklerini d\u00fczenli olarak almay\u0131 ve yedekleme dosyalar\u0131n\u0131 y\u00f6netmeyi kolayla\u015ft\u0131r\u0131r. Yedekleme i\u015flemi gece saatlerinde yap\u0131labilir, eski dosyalar otomatik olarak silinir ve yaln\u0131zca en g\u00fcncel yedekler saklan\u0131r. Bu yakla\u015f\u0131m, veritaban\u0131 y\u00f6netimini daha verimli ve g\u00fcvenli hale getirir.<a href=\"https:\/\/medium.com\/@ahmet-akn?source=post_page---post_author_info--3487ac7b1fc6---------------------------------------\"><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Bu yaz\u0131m\u0131zda, C# ile otomatik SQL veritaban\u0131 yedekleme \u00e7\u00f6z\u00fcm\u00fcn\u00fc ele alaca\u011f\u0131z. Ama\u00e7, veritaban\u0131n\u0131 belirli bir saatte &hellip; <a title=\"C# ile SQL Veritaban\u0131 Yedekleme Otomasyonu\" class=\"hm-read-more\" href=\"https:\/\/www.webmobdesign.com\/blog\/tr\/c-ile-sql-veritabani-yedekleme-otomasyonu\/\"><span class=\"screen-reader-text\">C# ile SQL Veritaban\u0131 Yedekleme Otomasyonu<\/span>Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[51],"tags":[],"class_list":["post-188","post","type-post","status-publish","format-standard","hentry","category-web-tr"],"_links":{"self":[{"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/posts\/188","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/comments?post=188"}],"version-history":[{"count":1,"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/posts\/188\/revisions"}],"predecessor-version":[{"id":321,"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/posts\/188\/revisions\/321"}],"wp:attachment":[{"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/media?parent=188"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/categories?post=188"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.webmobdesign.com\/blog\/wp-json\/wp\/v2\/tags?post=188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}