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?

That is strange. I never used those classes. But what about using a Sql Server command, instead of that? Just execute the command "restore [database name] from disk = [backup file location]. — Renato Afonso
You declare a device type (source), but you never hook this up to your Restore object. From the MSDN, "The restore instance must declare a DeviceType before calling this method. Otherwise, an exception will be thrown." — Richard Hansell

Richard Hansell Richard Hansell · Accepted Answer · 2017-03-07T10:44:43

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.

Restore multiple databases programmatically

1 Answers