In this post I am going to talk about SQL server DB backup and restore via C#.
lets get into the code.
First thing, you have to reference the following two references
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Smo
--------------------------------------------------------------------
1) Create a connection to the SQL Server
private static Server CreateServerConnection(string ServerIP, string SQLInstanceName, string userName, string password)
{
try
{
// Create a new connection to the selected server name
ServerConnection srvConn = new ServerConnection(ServerIP+ @"\" + SQLInstanceName);
// Log in using SQL authentication instead of Windows authentication
srvConn.LoginSecure = false;
// Give the login username
srvConn.Login = userName;
// Give the login password
srvConn.Password = password;
// Create a new SQL Server object using the connection we created
return new Server(srvConn);
}
catch (Exception ex)
{
// Handle Exceptions
}
}
--------------------------------------------------------
2) Perform DB Backup
/// The name of the .bak file
/// SQL Server instance name
/// The name of the Db we want to backup
/// serverIP">IP of the server where the SQL server installed
/// for sql Authentication, example sa
/// Password of the DB user
public static void BackUpDB(string backUpFileName, string SQLInstanceName, string DBName, string serverIP, string userName, string password)
{
try
{
Backup bDatabase = new Backup();
// Set the backup type to a database backup
bDatabase.Action = BackupActionType.Database;
// Set the database that we want to perform a backup on
bDatabase.Database = DBName;
// Set the backup device to a file
BackupDeviceItem bkDevice = new BackupDeviceItem(backUpFileName, DeviceType.File);
// Add the backup device
bDatabase.Devices.Add(bkDevice);
//Check if the file exists
if (File.Exists(backUpFileName))
File.Delete(backUpFileName);
// Perform the backup
bDatabase.SqlBackup(CreateServerConnection(serverIP, SQLInstanceName, UserName, password));
}
catch (Exception ex)
{
// Handle Exceptions
}
}
-------------------------------------------------------------------
3) Perform DB Restore
/// the .bak file
///
///
///
///
///
public static void RestoreDB(string restoreFileName, string SQLInstanceName, string DBName, string serverIP, string userName, string password)
{
try
{
Restore rDatabase = new Restore();
// Set the restore type to a database restore
rDatabase.Action = RestoreActionType.Database;
// Assign a db to restore operation
rDatabase.Database = DBName;
// Set the backup device to restore from file
BackupDeviceItem bkDevice = new BackupDeviceItem(restoreFileName, DeviceType.File);
// Add the backup device to the restore type
rDatabase.Devices.Add(bkDevice);
// Replace the Db if already exists
rDatabase.ReplaceDatabase = true;
if (File.Exists(restoreFileName))
// restore
rDatabase.SqlRestore(CreateServerConnection(serverIP, SQLInstanceName, userName, password));
}
catch (Exception ex)
{
// Handle Exceptions
}
}
---------------------------------------------------
That's it
.. more.
