1

On the tail end of database backup project and I've run into a issue where the Deflate compression I put in can't seem to find the path I saved the backup to. Being that the default backup location (used here) is on a network drive is there something else I need to do to make sure the path is found by Deflate? As of now I get System.IO.DirectoryNotFoundException: 'Could not find a part of the path. The purpose of the tool is to be able to put in any server you want to access, get a list of available DB's and then choose the one you want to backup.

I had this issue before locally, but all i had to do was give SQLserver the proper permissions to the folder.

using (SqlConnection newConn = new SqlConnection(connString))
        using (SqlCommand sqlCmd = new SqlCommand(query, newConn))
        {
            newConn.Open();
            value = sqlCmd.ExecuteScalar();
            canCompress = !(value == null || Convert.ToInt32(value) == 0);

            //----------------------------------
            //SQL Commands to run backup process
            //----------------------------------
            Interface.WriteLine("Creating backup");               
            if (canCompress)
            {
                sqlCmd.CommandText = "BACKUP DATABASE [" + connBuilder.InitialCatalog + "] "
                                    + "TO DISK = '" + backupFile + "' "
                                    + "WITH COPY_ONLY, COMPRESSION, NOFORMAT, NOINIT, "
                                    + "NAME = '" + backupName + "', "
                                    + "SKIP, REWIND, NOUNLOAD, STATS = 10";
                sqlCmd.ExecuteNonQuery();
            }
            else
            {
                sqlCmd.CommandText = "BACKUP DATABASE [" + connBuilder.InitialCatalog + "] "
                                    + "TO DISK = '" + backupFile + "' "
                                    + "WITH COPY_ONLY, NOFORMAT, NOINIT, "
                                    + "NAME = '" + backupName + "', "
                                    + "SKIP, REWIND, NOUNLOAD, STATS = 10";
                sqlCmd.ExecuteNonQuery();
            }

            //----------------------------------
            //Grab Backup File
            //----------------------------------
            query = "SELECT physical_device_name "
                    + "FROM msdb.dbo.backupset b "
                    + "JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id "
                    + "WHERE database_name = '" + connBuilder.InitialCatalog + "' "
                    + "ORDER BY backup_finish_date DESC ";

            using (SqlConnection connection = new SqlConnection(connString))
            using (SqlCommand cmd = new SqlCommand(query, connection))
            {
                connection.Open();
                value = cmd.ExecuteScalar();
                if (value != null)
                    backupFile = (string)value;
                else
                    throw new Exception("Unable to find backup file.");
            }

            //Set which files should be uploaded.
            if (canCompress)
            {
                fileToUpload = backupFile;
            }
            else
            {
                fileToUpload = Deflate.CompressFile(backupFile); //Point of error message
                File.Delete(backupFile);
            }

            return fileToUpload;
        }


static class Deflate
{
        public static string CompressFile(string sourcePath, string destPath = null)
        {
            if (destPath == null)
                destPath = Path.Combine(Path.GetDirectoryName(sourcePath), Path.GetFileNameWithoutExtension(sourcePath) + ".cmp");

            using (FileStream originalFileStream = File.OpenRead(sourcePath))
            using (FileStream compressedFileStream = File.Create(destPath))
            using (DeflateStream compressionStream = new 
    DeflateStream(compressedFileStream, CompressionMode.Compress))
     {
          originalFileStream.CopyTo(compressionStream);
          compressedFileStream.Flush();
      }

            FileInfo sourceInfo = new FileInfo(sourcePath); //Remove the .bak extension on compression?
            FileInfo destInfo = new FileInfo(destPath); //Remove the .bak extension on compression?
            Console.WriteLine("Compressed {0} from {1} to {2} bytes.", Path.GetFileName(sourcePath), sourcePath.Length, destInfo.Length);
            return destPath;
        }
 }
Red Rabbit
  • 115
  • 1
  • 11
  • Easiest way to do a full backup is to deattach the database and make copy of mdf and ldf files. Then reattach the database. Anybody can do rather than just an admin. You can then attach copies when needed. – jdweng Aug 28 '19 at 16:46
  • I recommend using SMO for this. You should keep in mind that the default Backup Path is accessible by the SQL Server Process and normally not for other users. – Daniel Schmid Aug 28 '19 at 17:00

0 Answers0