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;
}
}