Backup and Restore database MDF C# WinForm didnt work

Question

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

Answer 1

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