C# ile SQL Veritabanı Yedekleme Otomasyonu

.Net CLI
dotnet add package Microsoft.Data.SqlClient --version 6.0.1
Package Manager
NuGet\Install-Package Microsoft.Data.SqlClient -Version 6.0.1

using Microsoft.Data.SqlClient;
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Threading;
using System.Collections.Generic;

class Program
{
    static int maxFileCount = 14;

    static void Main(string[] args)
    {
        Console.WriteLine("Database Backup Service Started.");
        while (true)
        {
            var now = DateTime.Now;
            if (now.Hour == 03 && now.Minute == 30)
            {
                try
                {
                    BackupDatabase();
                    Console.WriteLine($"Backup completed at {DateTime.Now:yyyy-MM-dd HH:mm:ss}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error during backup: {ex.Message}");
                }
                Thread.Sleep(TimeSpan.FromMinutes(1));
            }
            Thread.Sleep(TimeSpan.FromSeconds(30));
        }
    }

    private static void BackupDatabase()
    {
        string connectionString = @"Server=localhost;Database=..........;User Id=......;Password=......;
                                Trusted_Connection=True;TrustServerCertificate=True;...........;";
        string backupDirectory = Path.GetFullPath(
            Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..", "..", "..", "Yedekler")
        );
        Directory.CreateDirectory(backupDirectory);
        string backupFileName = Path.Combine(
            backupDirectory,
            $"{DateTime.Now:yyyy-MM-dd_HH-mm-ss}.sql"
        );

        try
        {
            using SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();

            var builder = new StringBuilder();
            builder.AppendLine("-- Database Schema Backup");

            using (
                SqlCommand schemaCommand = new SqlCommand(
                    @"SELECT 'CREATE TABLE [' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + '] (' + 
              STUFF((SELECT ', [' + ISNULL(c.COLUMN_NAME, 'UNKNOWN_COLUMN') + '] ' + 
                    CASE 
                        WHEN ic.NAME IS NOT NULL 
                            THEN c.DATA_TYPE + ' IDENTITY(' + CAST(ISNULL(ic.SEED_VALUE, 1) AS VARCHAR) + ',' + CAST(ISNULL(ic.INCREMENT_VALUE, 1) AS VARCHAR) + ')'
                        WHEN c.DATA_TYPE IN ('nvarchar', 'varchar', 'varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 
                            THEN c.DATA_TYPE + '(MAX)' 
                        WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
                            THEN c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' 
                        ELSE c.DATA_TYPE 
                    END +
                    CASE 
                        WHEN pk.COLUMN_NAME IS NOT NULL 
                            THEN ' PRIMARY KEY'
                        ELSE ''
                    END
              FROM INFORMATION_SCHEMA.COLUMNS c
              LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
                  ON c.TABLE_NAME = tc.TABLE_NAME AND c.TABLE_SCHEMA = tc.TABLE_SCHEMA AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
              LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk 
                  ON c.TABLE_NAME = pk.TABLE_NAME AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA AND c.COLUMN_NAME = pk.COLUMN_NAME
              LEFT JOIN sys.identity_columns ic
                  ON OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME), 'U') = ic.OBJECT_ID AND c.COLUMN_NAME = ic.NAME
              WHERE c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ');' 
              FROM INFORMATION_SCHEMA.TABLES t
              WHERE t.TABLE_TYPE = 'BASE TABLE'",
                    connection
                )
            )

            using (SqlDataReader schemaReader = schemaCommand.ExecuteReader())
            {
                while (schemaReader.Read())
                {
                    builder.AppendLine(schemaReader.GetString(0));
                    builder.AppendLine("GO");  // CREATE TABLE komutundan sonra GO ekle
                }
            }

            using (
                SqlCommand tablesCommand = new SqlCommand(
                    "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'",
                    connection
                )
            )
            using (SqlDataReader tablesReader = tablesCommand.ExecuteReader())
            {
                List<(string schema, string table)> tables = new List<(string, string)>();

                while (tablesReader.Read())
                {
                    tables.Add((tablesReader.GetString(0), tablesReader.GetString(1)));
                }

                tablesReader.Close();

                foreach (var (schemaName, tableName) in tables)
                {
                    string fullTableName = $"[{schemaName}].[{tableName}]";

                    // Gerçekten IDENTITY özelliği var mı kontrol et
                    bool hasIdentityColumn = false;
                    using (
                        SqlCommand identityCheckCommand = new SqlCommand(
                            $@"
                        SELECT COUNT(*) FROM sys.identity_columns 
                        WHERE OBJECT_ID(QUOTENAME('{schemaName}') + '.' + QUOTENAME('{tableName}'), 'U') IS NOT NULL",
                            connection
                        )
                    )
                    {
                        hasIdentityColumn = (int)identityCheckCommand.ExecuteScalar() > 0;
                    }

                    // Sadece IDENTITY olan tablolar için IDENTITY_INSERT aç
                    if (hasIdentityColumn)
                    {
                        builder.AppendLine($"SET IDENTITY_INSERT {fullTableName} ON;");
                    }

                    using (
                        SqlCommand dataCommand = new SqlCommand(
                            $"SELECT * FROM {fullTableName}",
                            connection
                        )
                    )
                    using (SqlDataReader dataReader = dataCommand.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            var columns = new StringBuilder();
                            var values = new StringBuilder();

                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                string columnName = dataReader.GetName(i);
                                object value = dataReader[i];

                                columns.Append($"[{columnName}], ");
                                values.Append(
                                    FormatSqlValue(value, dataReader.GetFieldType(i)) + ", "
                                );
                            }

                            if (columns.Length > 2)
                                columns.Length -= 2;
                            if (values.Length > 2)
                                values.Length -= 2;

                            string insertStatement =
                                $"INSERT INTO {fullTableName} ({columns}) VALUES ({values});";
                            builder.AppendLine(insertStatement);
                            builder.AppendLine("GO"); // Her INSERT sonrası GO ekle
                        }
                    }

                    // Sadece IDENTITY olan tablolar için IDENTITY_INSERT kapat
                    if (hasIdentityColumn)
                    {
                        builder.AppendLine($"SET IDENTITY_INSERT {fullTableName} OFF;");
                    }
                }
            }

            File.WriteAllText(backupFileName, builder.ToString(), Encoding.UTF8);
            Console.WriteLine($"Database backup saved to {backupFileName}");

            // Temizleme işlemi (geçici dosyalar veya gereksiz yedekler vb.)
            CleanupBackupFiles(backupDirectory, backupFileName);
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error during backup: {ex.Message}");
        }
    }

    private static void CleanupBackupFiles(string backupDirectory, string latestBackupFile)
    {
        try
        {
            var backupFiles = Directory.GetFiles(backupDirectory, "*.sql");

            // Eğer yedek dosya sayısı belirtilen sınırdan fazlaysa
            if (backupFiles.Length > maxFileCount)
            {
                // Yeni eklenen dosyayı listeden çıkar
                var oldBackupFiles = backupFiles
                    .Where(f => f != latestBackupFile) // Yeni yedeği hariç tut
                    .OrderBy(f => File.GetCreationTime(f)) // En eski dosya ilk sırada olur
                    .ToList();

                // Silinecek dosya sayısını hesapla
                int filesToDelete = backupFiles.Length - maxFileCount;

                // En eski dosyalardan silinmesi gerekenleri sil
                for (int i = 0; i < filesToDelete && i < oldBackupFiles.Count; i++)
                {
                    File.Delete(oldBackupFiles[i]);
                    Console.WriteLine($"Deleted old backup file: {oldBackupFiles[i]}");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error during file cleanup: {ex.Message}");
        }
    }

    private static string FormatSqlValue(object value, Type type)
    {
        if (value == DBNull.Value || value == null)
            return "NULL";

        if (type == typeof(string) || type == typeof(char))
            return $"'{value.ToString().Replace("'", "''")}'";

        if (type == typeof(DateTime))
            return $"'{((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss")}'";

        if (type == typeof(bool))
            return ((bool)value) ? "1" : "0";

        if (type == typeof(decimal) || type == typeof(double) || type == typeof(float))
            return Convert.ToString(value, System.Globalization.CultureInfo.InvariantCulture);

        if (type == typeof(byte[]))
        {
            if (value == null || ((byte[])value).Length == 0)
                return "NULL";
            return $"CONVERT(varbinary(MAX), '{Convert.ToBase64String((byte[])value)}', 0)";
        }

        return value?.ToString() ?? "NULL";
    }
}