Simple C# Console Application for creating databases backups for MS SQL Server

Hi all, a couple of words what is happening here. We have C# console application which makes backups of databases on the MS SQL Server and I just would like to focus on some points. As we can see in the LINQ query, application skips databases such as “master”, “msdb” and so on, in the app.config you can setting up parameters like ms sql server name, login and password. So this is very simple application that can back up all your databases :)
Below you can see source code:

Changes in the Program.cs file:

     
        /// <summary>
        /// Mains the specified args.
        /// </summary>
        /// <param name="args">The args.</param>
        static void Main(string[] args)
        {
            Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - started", DateTime.Now));
            //Get all databases
            Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - get all databases", DateTime.Now));
            List<string> dbList = GetAllDatabasesList();
            if (dbList != null && dbList.Count > 0)
            {
                //Create backups for all databases        
                CreateDataBaseBackups(dbList);
            }
            Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - ended \r\n\r\n\r\n", DateTime.Now));
        }
        /// <summary>
        /// Gets all databases list.
        /// </summary>
        /// <returns></returns>
        static List<string> GetAllDatabasesList()
        {
            var result = new List<string>();
            string selectServerName = ConfigurationManager.AppSettings["MsSqlServerName"];
            string login = ConfigurationManager.AppSettings["MsSqlServerLogin"];
            string password = ConfigurationManager.AppSettings["MsSqlServerPassword"];
            string currentConnectionString = String.Format("Data Source={0}; user id={1};Password={2}", selectServerName, login, password);
            try
            {
                using (SqlConnection sqlConx = new SqlConnection(currentConnectionString))
                {
                    sqlConx.Open();
                    DataTable dataBasesInfoTable = sqlConx.GetSchema("Databases");
                    sqlConx.Close();
                    EnumerableRowCollection<DataRow> dataBaseInfo = from myRow in dataBasesInfoTable.AsEnumerable()
                                                                    where !myRow.Field<string>("database_name").ToLower().Equals("master") &&
                                                                    !myRow.Field<string>("database_name").ToLower().Equals("tempdb") &&
                                                                    !myRow.Field<string>("database_name").ToLower().Equals("model") &&
                                                                    !myRow.Field<string>("database_name").ToLower().Equals("msdb")
                                                                    select myRow;
                    Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - {1} database(s) ready for backups", DateTime.Now, dataBaseInfo.Count()));
                    result = (from row in dataBaseInfo select row.Field<string>("database_name")).ToList();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - Exception: {1}", DateTime.Now, ex.Message));
            }
            return result;
        }
        /// <summary>
        /// Creates the data base backups.
        /// </summary>
        /// <param name="databasesList">The databases list.</param>
        static void CreateDataBaseBackups(List<string> databasesList)
        {
            string dbBuckupsPath = ConfigurationManager.AppSettings["DbBackupsPath"];
            string backUpCreationCommand = ConfigurationManager.AppSettings["BackUpCreationCommand"];
            Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - create backups for all databases", DateTime.Now));
            foreach (var databaseName in databasesList)
            {
                try
                {
                    if (!Directory.Exists(dbBuckupsPath))
                    {
                        Directory.CreateDirectory(dbBuckupsPath);
                    }
                    string fullBackUpPathToBakFile = Path.Combine(dbBuckupsPath, string.Concat(databaseName, "_", DateTime.Now.ToString("yyyy-dd-MM_HH-mm-ss"), ".bak"));
                    using (Process osqlProcess = System.Diagnostics.Process.Start("osql", string.Format(backUpCreationCommand, databaseName, fullBackUpPathToBakFile)))
                    {
                        osqlProcess.WaitForExit();
                    }
                    if (!File.Exists(fullBackUpPathToBakFile))
                    {
                        Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - An 'isql' is not made a backup of the database, backup process is not finished correct for " + databaseName, DateTime.Now));
                        continue;
                    }
                    Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - (OK) {1}", DateTime.Now, databaseName));
                }
                catch (Exception ex)
                {
                    Console.WriteLine(string.Format("{0:d/M/yyyy HH:mm:ss} - (FAILED) {1}: {2}", DateTime.Now, databaseName, ex.Message));
                }
            }
        }

 

Changes in the App.config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<appSettings>
		<add key="DbBackupsPath" value="D:\dbbackup"/>
		<add key="MsSqlServerName" value="(local)\SQL2008"/>
		<add key="MsSqlServerLogin" value="your_login"/>
		<add key="MsSqlServerPassword" value="your_password"/>
		<add key="BackUpCreationCommand" value="-S (local)\SQL2008 -Uyour_login -Pyour_password -Q&quot;BACKUP DATABASE [{0}] TO DISK ='{1}'&quot;"/>
	</appSettings>
</configuration>

You may also to consider creation of scheduler for this app and write all output to log file. Your scheduler might run next batch file.

Text inside DatabaseBackup.bat

DatabaseBackup.exe > log.txt

 

Attached file with source code: DatabaseBackup.zip (Visual Studio 2012)

  • Tom

    Good one!

    • http://powerdotnetcore.com/ Sergey Boiko

      thank you!