SQL Server Express Automated Backup Console Application C# ADO.NET

• Download sample EXE - 5.34 KB
• Download source code - 397.83 KB
• Download Installer package - 153.02 KB

Introduction

This is a simple .NET V 2.0 console application that is designed to be used in conjunction with Windows task scheduler to enable the automated backup of SQL Server Express databases (which does not have SQL Server Agent or any other method of creating a backup schedule). It can also of course be used with SQL Server full version. It has been tested with SQL Server 2005 Express and SQL Server 2008.

The application uses a TSQL script to run through the non system databases under the database engine at the location the connection string points to, and create a backup at the configured location. The application can optionally add a date to the file name.

If the date is included in the file name, the application can be configured to delete databases older than a certain number of days.

This is a handy little application that has been used in the office for a while, which we have decided to contribute to The Code Project for anyone trying to solve a similar problem (we're not trying to win any beauty contests with it!).

Because this program could potentially delete databases with date strings in the name, please make sure you understand how the code works before you install this application on a system containing databases of any importance - use it at your own risk.

Credit for the TSQL goes to http://www.mssqltips.com/tip.asp?tip=1070.

Using the Code

Aside from the logging code, all of the code is in the Program class.

Once installed, use Windows task scheduler to run the application as required.

The application is configured using an App.config file (or SQLScheduleBackup.exe.cofig once compiled). You just need to provide a connection string, and tell the application where you want the backups to be saved.

Use the DeleteOldBackups and DeleteBackupsAfterDays to set the time after which the backups are to be deleted. The backups need to have been created with DateStampBackupFiles set to true as the file name is used rather than file properties to determine the age of the file.

<?xml version="1.0"?>
<configuration>
  <appSettings>
    <!-- SQL Server connection string -->
    <add key="DBConnectionString"
      value="Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=true"/>
    <!-- Path must have trailing slash -->
    <add key="SQLBackupLocation" value="C:\SQL_Server_2005_Express_Backup\"/>
    <!-- Path must have trailing slash -->
    <add key="LoggingPath" value="C:\SQL_Server_2005_Express_Backup\Logs\"/>
    <!-- Boolean-->
    <add key="DateStampBackupFiles" value="True"/>
    <!-- Boolean-->
    <add key="DeleteOldBackups" value="True"/>
    <!-- Integer -->
    <add key="DeleteBackupsAfterDays" value="28"/>
    <!-- Integer -->
    <add key="ConsoleWaitBeforeCloseSeconds" value="60"/>
  </appSettings>
<startup><supportedRuntime version="v2.0.50727"/></startup></configuration>

The Main() method simply controls the program flow.

static void Main( string[] args )
{
    Initialise();
    Console.WriteLine
	( "Cognize Limited Copyright 2009 - http://www.cognize.co.uk\n" );
    Console.WriteLine( "SQL Server Backup is starting...\n" );
    Output( "Starting SQL Database Backup...\n" );
    bool doDateStamp =
      bool.Parse( ConfigurationManager.AppSettings
		["DateStampBackupFiles"].ToString() );
    bool success = DoBackups( m_backupDir, doDateStamp );
    if (success)
    {
        Output( "Backup of SQL Server Databases ran with no errors.\n\n" );
        if (Boolean.Parse
		( ConfigurationManager.AppSettings["DeleteOldBackups"] ) == true)
        {
            DeleteOldBackups();
        }
    }
    int counter = int.Parse( ConfigurationManager.AppSettings
			["ConsoleWaitBeforeCloseSeconds"] );
    Console.WriteLine( "" );
    while ( counter > 0 )
    {
        Thread.Sleep( 1000 ); // Sleep to allow for 1 second timer ticks
        Console.WriteLine( "The application will close in {0} seconds.", counter );
        Console.CursorLeft = 0;
        Console.CursorTop = Console.CursorTop - 1;
        counter--;
    }
} 

The DoBackups() method builds and executes the dynamic TSQL script that is to be run against the master database to initiate the backups.

The SQL is included in the code inline for two reasons. Firstly for portability - there is no need to create a stored procedure in the master database of the server this code is run for. Secondly, the SQL is dynamic in that the application can be configured to add a date string to the file name or not.

The TSQL responsible for doing the back up is:

BACKUP DATABASE @name TO DISK = @fileName 

The rest exists just for setting variables such as file path and date string, and looping through the non system databases on the server.

/// <summary>
/// Backs up non system SQL Server databases to the configured directory.
/// </summary>
/// <param name="backupDir"></param>
/// <param name="dateStamp"></param>
private static bool DoBackups( string backupDir, bool dateStamp )
{
    bool allBackupsSuccessful = false;

    StringBuilder sb = new StringBuilder();

    // Build the TSQL statement to run against your databases.
    // SQL is coded inline for portability, and to allow the dynamic
    // appending of datestrings to file names where configured.

    sb.AppendLine( @"DECLARE @name VARCHAR(50) -- database name  " );
    sb.AppendLine( @"DECLARE @path VARCHAR(256) -- path for backup files  " );
    sb.AppendLine( @"DECLARE @fileName VARCHAR(256) -- filename for backup " );
    sb.AppendLine( @"DECLARE @fileDate VARCHAR(20) -- used for file name " );
    sb.AppendLine( @"SET @path = '" + backupDir + "'  " );
    sb.AppendLine( @"SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) " );
    sb.AppendLine( @"DECLARE db_cursor CURSOR FOR  " );
    sb.AppendLine( @"SELECT name " );
    sb.AppendLine( @"FROM master.dbo.sysdatabases " );
    sb.AppendLine( @"WHERE name NOT IN ('master','model','msdb','tempdb')  " );
    sb.AppendLine( @"OPEN db_cursor   " );
    sb.AppendLine( @"FETCH NEXT FROM db_cursor INTO @name   " );
    sb.AppendLine( @"WHILE @@FETCH_STATUS = 0   " );
    sb.AppendLine( @"BEGIN   " );

    if ( dateStamp )
    {
        sb.AppendLine( @"SET @fileName = @path + @name + '_' + @fileDate + '.bak'  " );
    }
    else
    {
        sb.AppendLine( @"SET @fileName = @path + @name + '.bak'  " );
    }
    sb.AppendLine( @"BACKUP DATABASE @name TO DISK = @fileName  " );
    sb.AppendLine( @"FETCH NEXT FROM db_cursor INTO @name   " );
    sb.AppendLine( @"END   " );
    sb.AppendLine( @"CLOSE db_cursor   " );
    sb.AppendLine( @"DEALLOCATE db_cursor; " );

    string connectionStr =
	ConfigurationManager.AppSettings["DBConnectionString"].ToString();

    SqlConnection conn = new SqlConnection( connectionStr );

    SqlCommand command = new SqlCommand( sb.ToString(), conn );

    try
    {
        conn.Open();
        command.ExecuteNonQuery();
        allBackupsSuccessful = true;
    }
    catch ( Exception ex )
    {
        Output( "An error occurred while running the backup query: " + ex );
    }
    finally
    {
        try
        {
            conn.Close();
        }
        catch (Exception ex)
        {
            Output( "An error occurred while trying to close the database connection:
			" + ex );
        }
    }

    return allBackupsSuccessful;
}

Following our backups, if configured to do so, the program will check backups in the backup directory and see if they are old enough that they require deleting using date in the file name. Of course the age of the file could be worked out using the file properties, but this way we can leave backups that have been created by other means that may not be part of your scheduled backup plan.

/// <summary>
/// Delete back up files in configured directory older than configured days.
/// </summary>
private static void DeleteOldBackups()
{
    String[] fileInfoArr = Directory.GetFiles
	( ConfigurationSettings.AppSettings["SQLBackupLocation"].ToString() );

    for (int i = 0; i < fileInfoArr.Length; i++)
    {
        bool fileIsOldBackUp = CheckIfFileIsOldBackup( fileInfoArr[i] );
        if (fileIsOldBackUp)
        {
            File.Delete( fileInfoArr[i] );
            Output( "Deleting old backup file: " + fileInfoArr[i] );
        }
    }
}

/// <summary>
/// Parses file name and returns true if file is older than configured days.
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
private static bool CheckIfFileIsOldBackup( string fileName )
{
    FileInfo fileInfo = new FileInfo( fileName );
    fileName = fileInfo.Name; // Get the file name without the full path
    bool backupIsOld = false;
    char[] fileNameCharsArray = fileName.ToCharArray();
    string dateString = String.Empty;
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < fileNameCharsArray.Length; i++)
    {
        if (Char.IsNumber( fileNameCharsArray[i] ))
        {
            sb.Append( fileNameCharsArray[i] );
        }
    }

    dateString = sb.ToString();

    if (!String.IsNullOrEmpty( dateString ))
    {
        // Delete only if we have exactly 8 digits
        if (dateString.Length == 8)
        {
            string year = String.Empty;
            string month = String.Empty;
            string day = String.Empty;

            year = dateString.Substring( 0, 4 );
            month = dateString.Substring( 4, 2 );
            day = dateString.Substring( 6, 2 );

            DateTime backupDate = new DateTime( int.Parse( year ),
				int.Parse( month ), int.Parse( day ) );

            int backupConsideredOldAfterDays =
		int.Parse( ConfigurationSettings.AppSettings
		["DeleteBackupsAfterDays"].ToString() );

            // Compare backup date to test if this backup
            // should be treated as an old backup.
            TimeSpan backupAge = DateTime.Now.Subtract( backupDate );

            if (backupAge.Days > backupConsideredOldAfterDays)
            {
                backupIsOld = true;
            }
        }
    }

    return backupIsOld;
}		

History

• 12/12/2009 Version 1 uploaded
• 13/12/2009 Added an installer package and some further descriptions for the code snippets
• 23/12/2009 Minor code tweaks, swapped out depreciated ConfigurationSettings for ConfigurationManager. No change in functionality. Clarified the article text a little.