Restore multiple databases programmatically

Question

i'm doing an application of restoring/backup database from/to Microsoft SQL Server.

How can i achieve the effect of capturing all the .bak files in the source folder, while the textboxes of destination databases serves as the new name for the new db and restore it into sql server?

my validation is that if the names in destination database groupbox, it will prompt error instead of restoring it.

This is the interface

here are my codes

CheckDBExist

public List<string> CheckIfDatabaseExists(string SQLServer, string backupRestore)
{
    bool result = false;
    List<string> DBList = new List<string>();
    string sqlConnectionString = this.rbWindow.Checked ?
                "Server=" + this.cboSQLServer.Text.Trim() + ";Database=master;Trusted_Connection=Yes" :
                "Server=" + this.cboSQLServer.Text.Trim() + ";Database=master;uid=" + this.txtUsername.Text.Trim() + ";pwd=" + this.txtPassword.Text.Trim();
    foreach (Control c in groupBox1.Controls)
    {
        if (c.GetType() == typeof(TextBox))
        {
            SqlConnection tmpConn = new SqlConnection(sqlConnectionString);

            string sqlCreateDBQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", c.Text);

            using (tmpConn)
            {
                using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
                {
                    tmpConn.Open();

                    object resultObj = sqlCmd.ExecuteScalar();

                    int databaseID = 0;

                    if (resultObj != null)
                    {
                        int.TryParse(resultObj.ToString(), out databaseID);
                    }

                    tmpConn.Close();

                    result = (databaseID > 0);
                    if ((!result) && (backupRestore == "backup"))
                    {
                        DBList.Add("[" + c.Text + "]");
                    }
                    else if ((result) && (backupRestore == "restore"))
                    {
                        DBList.Add("[" + c.Text + "]");
                    }

                }
            }
        }
    }
    return DBList;
}

Button CLick

private void btnRestore_Click(object sender, EventArgs e)
{
    string outputFolder = this.txtFolder.Text;
    if (string.IsNullOrEmpty(outputFolder))
    {
        MessageBox.Show("Please select source folder!", "Empty Source Folder", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    else
    {
        List<string> ExistDB = new List<string>();
        ExistDB = this.CheckIfDatabaseExists(this.cboSQLServer.Text, "restore");
        if (ExistDB.Count == 0)
        {
            RestoreDatabase(this.cboSQLServer.Text, this.txtFolder.Text);
        }
        else
        {
            MessageBox.Show("Databases " + string.Join(", ", ExistDB) + " exist!", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

Restore DB code

public void RestoreDatabase(string SQLServer, string outputFolder) {

        ServerConnection con = new ServerConnection(SQLServer);
        Server server = new Server(con);            

            foreach (Control c in groupBox3.Controls)
            {

                //try
                //{
                    if (c.GetType() == typeof(TextBox))
                    {
                        Restore destination = new Restore();
                        destination.Action = RestoreActionType.Database;
                        destination.Database = c.Text;
                        string backUpFile = outputFolder + "\\" + destination.Database + ".bak";
                        BackupDeviceItem source = new BackupDeviceItem(backUpFile, DeviceType.File);

                        string logFile = Path.GetDirectoryName(backUpFile);
                        logFile = Path.Combine(logFile, destination.Database + "_Log.ldf");

                        string dataFile = Path.GetDirectoryName(backUpFile);
                        dataFile = Path.Combine(dataFile, destination.Database + ".mdf");


                        destination.Devices.Add(source);
                        DataTable logicalRestoreFiles = destination.ReadFileList(server);
                        destination.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[0][0].ToString(), dataFile));
                        destination.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[1][0].ToString(), logFile));
                        destination.ReplaceDatabase = true;
                        destination.SqlRestore(server);
                    }            
                //}
                //catch (Exception ex)
                //{
                //MessageBox.Show(ex.Message);
                //}                                                            
            }      
             }                   

This is the code that trigger the exception

The error says:

"Cannot open backup device 'D:\TestBackup\VSM642SP2QC__VfsWorkflow.bak'. ?>Operating system error 2(The system cannot find the file >specified.).\r\nRESTORE FILELIST is terminating abnormally."

What should I do?

Answer 1

Add this line of code, immediately before the line where you use ReadFileList

destination.Devices.Add(source);

The restore instance must declare a DeviceType before calling the ReadFileList method. Otherwise, an exception will be thrown. You were declaring a DeviceType, but never hooked it up to your Restore.