Today I will describe some code snippets on how to backup your MS SQL Server database to a ‘.bak’ file using C#. Later on we will zip our .bak file using open source C# zip library as bak files seems to have high compression ratio.
Backing up files require us to connect to the database server and than use of Microsoft.SqlServer.Mangement.Smo objects to create actual backup.
Assuming we have some private fileds ready:
private Server sqlsrv; //initialized when connection succeedes
private string dbName;
private string backupFile;
private string serverName;
private string sqlUser;
private string sqlPass;
Lets first create a connection to our database server and initialize our Server type object:
private bool connectToSQLServer()
{
try
{
ServerConnection serverConn = new ServerConnection(this.serverName);
// Log in into sqlserver
serverConn.LoginSecure = false;
// Give the login username
serverConn.Login = this.sqlUser;
// Give the login password
serverConn.Password = this.sqlPass;
// create a new sql server object
sqlsrv = new Server(serverConn);
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
return false;
}
return true;
}
If all goes fine and we have our Server (sqlsrv) object ready we can start our backup operation:
public bool bakBackup()
{
//If the connection returns false return from this method too.
if (!connectToSQLServer())
return false;
try
{
// Create a new backup object
Backup bkpDatabase = new Backup();
// Set the type to database
bkpDatabase.Action = BackupActionType.Database;
// set the database name we want to actually backup
bkpDatabase.Database = dbName;
// To get the file from me actual backup create BackupDeviceItem
BackupDeviceItem bkpDevice = new BackupDeviceItem(this.backupFile, DeviceType.File);
// add the backup file device to our backup
bkpDatabase.Devices.Add(bkpDevice);
// execute the actual backup using Smo
bkpDatabase.SqlBackup(sqlsrv);
//verify if the file exist
if (File.Exists(this.backupFile))
return true;
else
return false;
}
catch (Exception ex)
{
Console.WriteLine("Backup file couldn't be created" + ex.StackTrace);
return false;
}
}
As a side note I would mention that the only two folders I have found on my system where I can actually perform the backup (store tha bak file) is “C:Program FilesMicrosoft SQL ServerMSSQL.3MSSQLBackup” or “C:Program FilesMicrosoft SQL ServerMSSQL.3MSSQLData” folders, this has something to do with permissions setup and I’ll will update this post once I find the solution. You can read my post on stackoverflow regarding this issue.
Assuming that all went ok, lets create a zip file which will compress our bak file to the size more than 2 times smaller than the original bak file.
For file comression I will use great C# library ‘SharpZipLib’ which you can find here.
Before using the snippet below you would have to add a reference to the library in your project.
public static void Zip(string sourceFile, string destinationFile, int BufferSize)
{
FileStream fileStreamIn = new FileStream(sourceFile, FileMode.Open, FileAccess.Read);
FileStream fileStreamOut = new FileStream(destinationFile, FileMode.Create, FileAccess.Write);
ZipOutputStream zipOutputStream = new ZipOutputStream(fileStreamOut);
byte[] buffer = new byte[BufferSize];
ZipEntry entry = new ZipEntry(Path.GetFileName(sourceFile));
zipOutputStream.PutNextEntry(entry);
int size;
do
{
size = fileStreamIn.Read(buffer, 0, buffer.Length);
zipOutputStream.Write(buffer, 0, size);
} while (size > 0);
zipOutputStream.Close();
fileStreamOut.Close();
fileStreamIn.Close();
}
Looks pretty easy? Well, actually it is! As a source and destination file arguments provide full path to your file (.bak) as source and (.zip) as destination and you are done.
You can of course change one name to another using simple call:
string zipFileName = fileName.Replace(".bak", ".zip");
Happy coding!