advertising-profit-control/AdvertisingProfitControlData/Backup.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;

namespace AdvertisingProfitControlData
{
    public class Backup
    {
        public static readonly string DefaultBackupLocation = Path.GetPathRoot(Environment.SystemDirectory) + @"Users\" + Environment.UserName + @"\AppData\Local\APC\Backups\";
        public static readonly string DefaultBackupExtension = "apcdbbak";
        //Define the error states that certain operations may return.
        public const byte PrimaryBackupFailed = 5;
        public const byte PrimaryBackupVerifyFailed = 10;
        public const byte OptionalBackupFailed = 15;
        public const byte OptionalBackupVerifyFailed = 25;
        public byte State;
        public string ErrorMessage = string.Empty;

        public bool SingleFileBackup(string connectionString, string optionalBackupFilePath = "")
        {
            var defaultBackupFile = DefaultBackupLocation + DateTime.Now.ToString("yy-MM-dd") + "." +  DefaultBackupExtension;
            var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionString);
            try
            {
                using (var sqlConnection = new SqlConnection(sqlConStrBuilder.ConnectionString))
                {
                    sqlConnection.InfoMessage += (s, e) =>
                    {
                        if (e.Errors.Count > 0)
                        {
                            State += e.Errors[e.Errors.Count - 1].State % 5 == 0 ? e.Errors[e.Errors.Count - 1].State : (byte) 0;
                        }
                    };
                    //Note: To get a SqlException an error must be raised at level 11 or higher.
                    //Checksum command needs to be applied in order to properly verify the media as pointed out here:
                    //http://www.edwinmsarmiento.com/the-scariest-lie-we-believed-about-backup-verification/
                    //RAISERROR(message string, level, state) levels above 11 throw SQLExceptions.
                    var query =
                        @"DECLARE @BackupSuccess BIT --1 is true 0 is false
                        SET @BackupSuccess = 0
                        BEGIN TRY
                            BACKUP DATABASE " + sqlConStrBuilder.InitialCatalog + @" TO DISK = N'" + defaultBackupFile + @"' WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, CHECKSUM
                            SET @BackupSuccess = 1
                            RESTORE VERIFYONLY FROM DISK = N'"+ defaultBackupFile + @"' WITH NOUNLOAD, NOREWIND, CHECKSUM
                        END TRY
                        BEGIN CATCH
	                        IF @BackupSuccess = 0
		                        BEGIN
			                        RAISERROR('Backup failed', 9, " + PrimaryBackupFailed + @")
		                        END
	                        ELSE
		                        BEGIN
			                        RAISERROR('Verify failed', 9, " + PrimaryBackupVerifyFailed + @")
		                        END
                        END CATCH";
                    using (var command = new SqlCommand(query, sqlConnection))
                    {
                        sqlConnection.Open();
                        command.ExecuteNonQuery();
                        var defaultBackupVerificationFailedFile =
                            defaultBackupFile.Insert(defaultBackupFile.IndexOf(".", StringComparison.Ordinal),
                                " (Verification Failed)");
                        if (File.Exists(defaultBackupVerificationFailedFile))
                        {
                            File.Delete(defaultBackupVerificationFailedFile);
                        }
                        if (State == PrimaryBackupVerifyFailed)
                        {
                            File.Move(defaultBackupFile, defaultBackupVerificationFailedFile);
                        }
                        if (optionalBackupFilePath == string.Empty)
                        {
                            return State != PrimaryBackupFailed;
                        }
                        var optionalBackupFile = optionalBackupFilePath + DateTime.Now.ToString("yy-MM-dd") + "." + DefaultBackupExtension;
                        command.CommandText =
                            @"DECLARE @BackupSuccess BIT
                            SET @BackupSuccess = 0
                            BEGIN TRY
                                BACKUP DATABASE " + sqlConStrBuilder.InitialCatalog + @" TO DISK = N'" + optionalBackupFile + @"' WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, CHECKSUM
                                SET @BackupSuccess = 1
                                RESTORE VERIFYONLY FROM DISK = N'" + optionalBackupFile + @"' WITH NOUNLOAD, NOREWIND, CHECKSUM
                            END TRY
                            BEGIN CATCH
	                            IF @BackupSuccess = 0
		                            BEGIN
			                            RAISERROR('Optional backup failed', 9, " + OptionalBackupFailed + @")
		                            END
	                            ELSE
		                            BEGIN
			                            RAISERROR('Optional backup verify failed', 9, " + OptionalBackupVerifyFailed + @")
		                            END
                            END CATCH";
                        command.ExecuteNonQuery();
                        var optionalBackupVerficationFailedFile =
                            optionalBackupFile.Insert(optionalBackupFile.IndexOf(".", StringComparison.Ordinal),
                                " (Verification Failed)");
                        if (State == OptionalBackupFailed || State == (OptionalBackupFailed + PrimaryBackupFailed) || State == (OptionalBackupFailed + PrimaryBackupVerifyFailed))
                        {
                            return false;
                        }
                        if (File.Exists(optionalBackupVerficationFailedFile))
                        {
                            File.Delete(optionalBackupVerficationFailedFile);
                        }
                        if (State == OptionalBackupVerifyFailed || State == (OptionalBackupVerifyFailed + PrimaryBackupVerifyFailed) || State == (OptionalBackupVerifyFailed + PrimaryBackupFailed))
                        {
                            File.Move(optionalBackupFile, optionalBackupVerficationFailedFile);
                        }
                        return State == PrimaryBackupFailed;
                    }
                }
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                return false;
            }
        }

        public List<FileInfo> GetSingleFileBackupFilesList()
        {
            var directoryInfo = new DirectoryInfo(DefaultBackupLocation);
            var files = directoryInfo.GetFiles("*." + DefaultBackupExtension);
            return files.ToList();
        }
    }
}