Im trying to Backup and Restore my database .MDF file in C# WinForms. I think it backup because I saw a .BAK file but when I restore, its not working. Both have no error on runtime but I think theres something wrong with my code. Here's my code:
private void backupToolStripMenuItem1_Click(object sender, EventArgs e)
{
saveFileDialogBackUp.FileName = fileName;// +".bak";
saveFileDialogBackUp.Filter = "Backup File (*.bak)|*.bak";
saveFileDialogBackUp.Title = "File Location";
try
{
saveFileDialogBackUp.InitialDirectory = AppDomain.CurrentDomain.BaseDirectory + @"Sauvegardes";
if (saveFileDialogBackUp.ShowDialog() == DialogResult.OK)
{
SqlCommand cmd = new SqlCommand(@"BACKUP DATABASE [dbCPS] TO DISK = '" +
saveFileDialogBackUp.FileName + ".bak' WITH NOFORMAT, NOINIT, NAME = +
N'dbCPS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 ", +
conn);
conn.Close();
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Backup Successful!", "CPS Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch(Exception error)
{
MessageBox.Show(error.ToString());
}
}
private void restoreToolStripMenuItem_Click(object sender, EventArgs e)
{
openFileDialogBackUp.Filter = "Backup File (*.bak)|*.bak";
openFileDialogBackUp.FileName = "CPS";
openFileDialogBackUp.InitialDirectory = AppDomain.CurrentDomain.BaseDirectory + @"Sauvegardes";
if (openFileDialogBackUp.ShowDialog() == DialogResult.OK)
{
//RestoreDatabase(conn, "dbCPS", openFileDialogBackUp.FileName);
using (SqlConnection connection = new SqlConnection(GlobalVar.connectionString))
{
using (SqlCommand command = new SqlCommand(@"RESTORE DATABASE dbCPS FROM DISK = '" + openFileDialogBackUp.FileName + @"' WITH RECOVERY, MOVE 'dbCPS' TO 'C:\Users\admin\Desktop\New backup\dbCPS.mdf', MOVE 'dbCPS_log' TO 'C:\Users\admin\Desktop\New backup\dbCPS_Log.ldf', REPLACE", connection))
{
connection.Open();
// Add the parameters for the SelectCommand.
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
MessageBox.Show("Database Restored", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
connection.Close();
}
}
}
}
You can try to use
public void BackupDatabase(String databaseName, String userName,
String password, String serverName, String destinationPath)
{
Backup sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "ArchiveDataBase:" +
DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "Archive";
sqlBackup.Database = databaseName;
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
sqlBackup.SqlBackup(sqlServer);
}
public void RestoreDatabase(String databaseName, String filePath,
String serverName, String userName, String password,
String dataFilePath, String logFilePath)
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = databaseName;
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = dataFilePath + databaseName + ".mdf";
String logFileLocation = logFilePath + databaseName + "_Log.ldf";
db = sqlServer.Databases[databaseName];
RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName+"_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.PercentCompleteNotification = 10;
sqlRestore.PercentComplete +=
new PercentCompleteEventHandler(sqlRestore_PercentComplete);
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
}
Alternative
public static bool backup(string path, ref Exception _ex)
{
bool res = true;
try
{
path_to_save = path;
if (!Directory.Exists(path_to_save))
Directory.CreateDirectory(path_to_save);
final_path = path_to_save + Guid.NewGuid().ToString() + ".bak";
Server myServer = new Server("servername");
Backup bkpDBFull = new Backup();
/* Specify whether you want to back up database or files or log */
bkpDBFull.Action = BackupActionType.Database;
Database myDatabase = myServer.Databases["yourdatabase"];
/* Specify the name of the database to back up */
bkpDBFull.Database = myDatabase.Name;
/* You can take backup on several media type (disk or tape), here I am
* using File type and storing backup on the file system */
bkpDBFull.Devices.AddDevice(final_path, DeviceType.File);
bkpDBFull.BackupSetName = "yourdatabase database Backup";
bkpDBFull.BackupSetDescription = "yourdatabase database - Full Backup";
/* You can specify the expiration date for your backup data
* after that date backup data would not be relevant */
bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);
/* You can specify Initialize = false (default) to create a new
* backup set which will be appended as last backup set on the media. You
* can specify Initialize = true to make the backup as first set on the
* medium and to overwrite any other existing backup sets if the all the
* backup sets have expired and specified backup set name matches with
* the name on the medium */
bkpDBFull.Initialize = false;
/* Wiring up events for progress monitoring */
bkpDBFull.PercentComplete += CompletionStatusInPercent;
bkpDBFull.Complete += Backup_Completed;
/* SqlBackup method starts to take back up
* You can also use SqlBackupAsync method to perform the backup
* operation asynchronously */
bkpDBFull.SqlBackup(myServer);
}
catch (Exception ex) { res = false; _ex = ex; }
return res;
}