{"id":492,"date":"2016-12-16T13:21:22","date_gmt":"2016-12-16T12:21:22","guid":{"rendered":"https:\/\/solidt.eu\/blog\/?p=492"},"modified":"2020-04-10T17:40:59","modified_gmt":"2020-04-10T16:40:59","slug":"c-mssql-database-backup-upgrade-script-restore-on-error","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/c-mssql-database-backup-upgrade-script-restore-on-error\/","title":{"rendered":"C# MSSQL Database backup + upgrade script + restore on error"},"content":{"rendered":"\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"csharp\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">using DbUp;\nusing System;\nusing System.Collections.Generic;\nusing System.Linq;\nusing System.Reflection;\nusing System.Text;\nusing System.Threading.Tasks;\nusing System.Configuration;\nusing DbUp.Engine;\nusing System.Data.SqlClient;\nusing System.IO;\n\nnamespace Chronos.Database.Console\n{\n    class Program\n    {\n        static string BackupDatabaseToFile(string connectionString)\n        {\n            System.Console.ForegroundColor = ConsoleColor.White;\n            System.Console.WriteLine(\"Creating database backup..\");\n            using (var connection = new SqlConnection(connectionString))\n            {\n                \/\/connection.Database\n                connection.Open();\n\n                var backupPathSql = \"SELECT SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile]\";\n                var backupPath = \"\";\n\n                using(var command = new SqlCommand(backupPathSql, connection))\n                {\n                    backupPath = (string)command.ExecuteScalar();\n                }\n\n                var fileName = String.Concat(connection.Database, \"_\", DateTime.Now.ToString(\"yyyyMMddHHmmss\"), \".bak\");\n                var backupFile = String.Join(\"\\\\\", backupPath.TrimEnd('\\\\'), fileName);\n\n                System.Console.WriteLine($\"Backup file: {backupFile}\");\n\n                var escapedBackupFile = backupFile.Replace(\"'\", \"''\");\n                var backupSql = $\"BACKUP DATABASE {connection.Database} TO DISK = '{escapedBackupFile}'\";\n\n                using (var command = new SqlCommand(backupSql, connection))\n                {\n                    command.ExecuteNonQuery();\n                }\n\n                System.Console.ForegroundColor = ConsoleColor.Green;\n                System.Console.WriteLine(\"Create backup: Success!\");\n                System.Console.ResetColor();\n\n                System.Console.WriteLine($\"Removing existing backup files older than 7 days..\");\n\n                \/\/ Remove old database backups\n                foreach (var file in Directory.GetFiles(backupPath, $\"{connection.Database}_*.bak\"))\n                {\n                    \/\/ Prevent deletion of the above created backup\n                    if (!String.Equals(Path.GetFileName(file), fileName, StringComparison.OrdinalIgnoreCase))\n                    { \n                        var fileAge = DateTime.Now - File.GetCreationTime(file);\n                        var fileAgeInDays = Math.Round(fileAge.TotalDays, 5);\n                        System.Console.WriteLine($\"Existing backup: {file}; Age: {fileAgeInDays} days\");\n                        if (fileAge.TotalDays > 7) {\n                            System.Console.WriteLine($\"Deleting backup file: {file}\");\n                            File.Delete(file);\n                        }\n                    }\n                }\n\n                return backupFile;\n            }\n        }\n\n        static void RestoreDatabaseFromFile(string connectionString, string backupFile)\n        {\n            System.Console.ForegroundColor = ConsoleColor.White;\n            System.Console.WriteLine($\"Restoring database backup from file: {backupFile}\");\n            \n            using (var connection = new SqlConnection(connectionString))\n            {\n                string restoreDatabaseName = connection.Database;\n                connection.Open();\n                \/\/ Switch to the master database to prevent 'Database is in use'\n                connection.ChangeDatabase(\"master\");\n\n\n                var singleUserModeSql = $\"ALTER DATABASE {restoreDatabaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE\";\n                using (var command = new SqlCommand(singleUserModeSql, connection))\n                {\n                    command.ExecuteNonQuery();\n                }\n\n                var escapedBackupFile = backupFile.Replace(\"'\", \"''\");\n                var backupSql = $\"RESTORE DATABASE {restoreDatabaseName} FROM DISK = '{escapedBackupFile}' WITH REPLACE\";\n\n                using (var command = new SqlCommand(backupSql, connection))\n                {\n                    command.ExecuteNonQuery();\n                }\n\n                var multiUserModeSql = $\"ALTER DATABASE {restoreDatabaseName} SET MULTI_USER\";\n                using (var command = new SqlCommand(multiUserModeSql, connection))\n                {\n                    command.ExecuteNonQuery();\n                }\n\n                System.Console.ForegroundColor = ConsoleColor.Green;\n                System.Console.WriteLine(\"Restore backup: Success!\");\n                System.Console.ResetColor();\n            }\n        }\n\n        static int Main(string[] args)\n        {\n            var configConnectionString = ConfigurationManager.ConnectionStrings[\"Chronos\"].ConnectionString;\n            var connectionString = args.FirstOrDefault() ?? configConnectionString;\n\n            var databaseBackupFile = \"\";\n\n            try\n            {\n                databaseBackupFile = BackupDatabaseToFile(connectionString);\n            }\n            catch (Exception ex)\n            {\n                System.Console.ForegroundColor = ConsoleColor.Red;\n                System.Console.WriteLine(ex.ToString());\n                System.Console.ResetColor();\n#if DEBUG\n                System.Console.ReadLine();\n#endif\n                return -1;\n            }\n\n            var upgrader =\n                DeployChanges.To\n                    .SqlDatabase(connectionString)\n                    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())\n                    .LogToConsole()\n                    .Build();                        \n\n            var result = upgrader.PerformUpgrade();\n\n            if (!result.Successful)\n            {\n                System.Console.ForegroundColor = ConsoleColor.Red;\n                System.Console.WriteLine(\"Database upgrade finished with errors!\");\n                System.Console.WriteLine(result.Error);\n                System.Console.ResetColor();\n\n                \/\/ Try a database restore                \n                try\n                {\n                    RestoreDatabaseFromFile(connectionString, databaseBackupFile);\n                }\n                catch (Exception ex)\n                {\n                    System.Console.ForegroundColor = ConsoleColor.Red;\n                    System.Console.WriteLine(ex.ToString());\n                    System.Console.ResetColor();\n                }\n\n                System.Console.ForegroundColor = ConsoleColor.Red;\n                System.Console.WriteLine(\"Database upgrade failed!\");                \n#if DEBUG\n                System.Console.ReadLine();\n#endif\n                return -1;\n            }\n\n\n            System.Console.ForegroundColor = ConsoleColor.Green;\n            System.Console.WriteLine(\"Success!\");\n            System.Console.ResetColor();\n#if DEBUG\n            System.Console.ReadLine();\n#endif\n            return 0;\n        }\n    }\n}\n<\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[6],"tags":[],"class_list":["post-492","post","type-post","status-publish","format-standard","hentry","category-dotnet"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=492"}],"version-history":[{"count":2,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/492\/revisions"}],"predecessor-version":[{"id":3705,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/492\/revisions\/3705"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}