Backup and Restore a SQL Server Database in C#

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SqlClient;

namespace Erris.UnitTests.UITests
{
internal class DatabaseBackups
{
///

/// Restore the given database, using the given backup file.
/// Note that the database must exist. Any current connections to the database will be forcibly closed, for the Restore operation.
///

///
///
///
///
internal static void RestoreDatabase(string cxnString, string backupFilePath, string srcLogicalName, string mdfDir)
{
using (SqlConnection cxn = new SqlConnection(cxnString))
{
Console.WriteLine("RestoreDatabase - begin: " + cxn.Database);

//do not open the cxn, since we need to restore the database!

Server server = new Server(cxn.DataSource);

//setDatabaseOffline(): we need to make sure there are no open connections to the database, before we can Restore.
setDatabaseOffline(cxnString);

{
Database unitTestingDatabase = server.Databases[cxn.Database];
dropDatabase(unitTestingDatabase); //to make sure we restore database - there was issue where database was not actually updated
}

//if (unitTestingDatabase == null)
{
// unitTestingDatabase = new Database(server, cxn.Database);

// unitTestingDatabase.Create();

Microsoft.SqlServer.Management.Smo.Restore restore = new Microsoft.SqlServer.Management.Smo.Restore();

restore.NoRecovery = false;
restore.Action = RestoreActionType.Database;

//we cannot assume that the db files are named after the db (NOT default SQL Server behaviour).
string mainMdfPath = mdfDir + @"\" + cxn.Database + ".mdf";
File.Delete(mainMdfPath);
restore.RelocateFiles.Add(new RelocateFile(srcLogicalName, mainMdfPath));
string logMdfPath = mdfDir + @"\" + cxn.Database + ".ldf";
File.Delete(logMdfPath);
restore.RelocateFiles.Add(new RelocateFile(srcLogicalName + "_log", logMdfPath));

restore.ReplaceDatabase = true;

BackupDeviceItem bdi = default(BackupDeviceItem);

bdi = new BackupDeviceItem(backupFilePath, DeviceType.File);

restore.Devices.Add(bdi);

restore.Database = cxn.Database;

//restore.PercentCompleteNotification = 10;
restore.SqlRestore(server);

//Restore completed.
//Set database online again.
Database unitTestingDatabase = server.Databases[cxn.Database];
unitTestingDatabase = server.Databases[cxn.Database];
unitTestingDatabase.Refresh();
unitTestingDatabase.SetOnline();
server.Refresh();
}
Console.WriteLine("RestoreDatabase - end: " + cxn.Database);
}
}

private static void dropDatabase(Database unitTestingDatabase)
{
if(unitTestingDatabase != null)
unitTestingDatabase.Drop();
}

private static void setDatabaseOffline(string cxnString)
{
using (SqlConnection cxn = new SqlConnection(cxnString))
{
cxn.Open();

string cmdText = string.Format("ALTER DATABASE {0} SET OFFLINE WITH ROLLBACK IMMEDIATE", //rolls back any current txns
cxn.Database
);
using (SqlCommand cmd = new SqlCommand(cmdText, cxn))
{
cmd.ExecuteNonQuery();
}

cxn.Close();
}
}

internal static void BackupDatabase(string srcCxn, string sharedBackupDir, string srcLogicalName, TimeSpan timeout, string serverBackupDirFromClient, out string localSharedBackupFile, out string backupFilePath)
{
SqlConnection cxn = new SqlConnection(srcCxn);

string dbName = cxn.Database;

Console.WriteLine("BackupDatabase - begin: " + dbName);

backupFilePath = sharedBackupDir + "\\" + dbName + ".bak";

//first, delete the backup file, if it exists.
//this is to make sure the backup file only contains 1 restore set.
localSharedBackupFile = serverBackupDirFromClient + @"\" + Path.GetFileName(backupFilePath);
if (File.Exists(localSharedBackupFile))
File.Delete(localSharedBackupFile);

//note: if there is an existing .bak file, SQL Server will just overwrite it [ok].

ServerConnection conn = new ServerConnection(cxn);
Server server = new Server(conn);

Database database = server.Databases[dbName];

//To Avoid TimeOut Exception

server.ConnectionContext.StatementTimeout = (int)timeout.TotalSeconds;

RecoveryModel recoverymodel = database.DatabaseOptions.RecoveryModel;

//Define a Backup object variable.

Backup backup = new Backup();

//backup.CompressionOption = BackupCompressionOptions.On;

//Specify the type of backup, the description, the name, and the database to be backed up.

backup.Action = BackupActionType.Database;

backup.BackupSetDescription = "Full backup of " + dbName + ".";

backup.BackupSetName = "BACKUP_FOR_TFS_WEBSITE";

backup.Database = dbName;

//Declare a BackupDeviceItem
BackupDeviceItem bdi = new BackupDeviceItem(backupFilePath, DeviceType.File);

//Add the device to the Backup object.
backup.Devices.Add(bdi);

//Set the Incremental property to False to specify that this is a full database backup.

backup.Incremental = false;
backup.CopyOnly = true; //do not affect logs of source database

//Set the expiration date.
backup.ExpirationDate = DateTime.Today;

//Specify that the log must be truncated after the backup is complete.
backup.LogTruncation = BackupTruncateLogType.Truncate;

//Run SqlBackup to perform the full database backup on the instance of SQL Server.
backup.SqlBackup(server);

//Remove the backup device from the Backup object.
backup.Devices.Remove(bdi);

Console.WriteLine("BackupDatabase - end: " + dbName);
}
}
}