sm sql database backup in C#.net

BitBlt 452 Practically a Master Poster Featured Poster · 2011-05-04T04:15:34+00:00

I'm a little hesitant to post on this, as I'm a database guy not a C# guy. But just looking over the code you posted, there are a couple of items that seem odd to me:
1. Line 12, where does variable "DBpath" get populated?
2. Line 16, where does variable "srvr" get populated?

That being said, you may also want to check the permissions on your connection's login. MSSQL requires specific permissions to be assigned before a login can perform backup. It doesn't have to be "sa" or anything, but it can't just be any old user.
Next question has more to do with application design...why are you trying to execute a backup here rather than using standard SQL Server best practices such as full recovery model with periodic transaction log backups? Understand, I'm not saying you CAN'T do it the way you're doing it, it's just unusual to do this in a production environment.

Anyway, if these questions/comments aren't helpful I apologize for wasting your time. Good luck!

judithSampathwa -2 Posting Pro in Training · 2011-05-04T21:33:39+00:00

I'm a little hesitant to post on this, as I'm a database guy not a C# guy. But just looking over the code you posted, there are a couple of items that seem odd to me:
1. Line 12, where does variable "DBpath" get populated?
2. Line 16, where does variable "srvr" get populated?
That being said, you may also want to check the permissions on your connection's login. MSSQL requires specific permissions to be assigned before a login can perform backup. It doesn't have to be "sa" or anything, but it can't just be any old user.
Next question has more to do with application design...why are you trying to execute a backup here rather than using standard SQL Server best practices such as full recovery model with periodic transaction log backups? Understand, I'm not saying you CAN'T do it the way you're doing it, it's just unusual to do this in a production environment.
Anyway, if these questions/comments aren't helpful I apologize for wasting your time. Good luck!

I have added the whole code below

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;

namespace DBBackupApp
{
    public partial class DBBackup : Form
    {
        DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
        private static Server srvr;
        private string DBpath = Application.StartupPath;

        public DBBackup()
        {
            InitializeComponent();
        }

        private void Backup_Load(object sender, EventArgs e)
        {
            this.Cursor = Cursors.Default;
            WindowState = FormWindowState.Normal;
            cmbServer.Enabled = false;
            //cmbDataBase.Text = "Administrator";
            cmbDataBase.Enabled = false;

            try
            {
                chk_Insec.Checked = true;

                // If there are any servers
                if (dtServers.Rows.Count > 0)
                {
                    // Loop through each server in the DataTable
                    foreach (DataRow drServer in dtServers.Rows)
                    {
                        cmbServer.Items.Add(drServer["Name"]);
                        cmbServer.Text = Convert.ToString(drServer["Name"]);
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("ERROR: There are no available servers.\nOr there is an error while loading server name", "Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            finally
            {
                this.Cursor = Cursors.Default;
            }
            this.Cursor = Cursors.Default;
        }

        private void chk_Insec_CheckedChanged(object sender, EventArgs e)
        {
            if (chk_Insec.CheckState == CheckState.Checked)
            {
                txt_username.Enabled = false;
                txt_username.Text = string.Empty;

                txt_password.Enabled = false;
                txt_password.Text = string.Empty;
            }
            if (chk_Insec.CheckState == CheckState.Unchecked)
            {
                txt_username.Enabled = true;
                txt_password.Enabled = true;
            }
        }

        private void server_change_Click(object sender, EventArgs e)
        {
            cmbServer.Enabled = true;
        }

        private void cmbDataBase_Click(object sender, EventArgs e)
        {
            cmbDataBase.Items.Clear();
            try
            {
                if (cmbServer.SelectedItem != null && cmbServer.SelectedItem.ToString() != "")
                {
                    this.Cursor = Cursors.WaitCursor;
                    // Create a new connection to the selected server name
                    ServerConnection srvConn = new ServerConnection(cmbServer.SelectedItem.ToString());
                    // Log in using SQL authentication instead of Windows authentication
                    srvConn.LoginSecure = true;
                    if (chk_Insec.CheckState == CheckState.Checked)
                    {
                        // Create a new SQL Server object using the connection we created
                        srvr = new Server(srvConn);
                        // Loop through the databases list
                        foreach (Database dbServer in srvr.Databases)
                        {
                            // Add database to combobox
                            cmbDataBase.Items.Add(dbServer.Name);
                        }
                    }
                    if (chk_Insec.CheckState == CheckState.Unchecked)
                    {
                        // Give the login username
                        srvConn.Login = txt_username.Text;
                        // Give the login password
                        srvConn.Password = txt_password.Text;
                        // Create a new SQL Server object using the connection we created
                        srvr = new Server(srvConn);
                        // Loop through the databases list
                        foreach (Database dbServer in srvr.Databases)
                        {
                            // Add database to combobox
                            cmbDataBase.Items.Add(dbServer.Name);
                        }
                    }
                }
                else
                {
                    // A server was not selected, show an error message
                    MessageBox.Show("ERROR: Contact Administrator!!", "Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch (Exception)
            {
                MessageBox.Show("ERROR: An error ocurred while loading the avaiable DataBasees", "Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                this.Cursor = Cursors.Arrow;
            }
        }

        private void db_change_Click(object sender, EventArgs e)
        {
            cmbDataBase.Enabled = true;
        }

        private void cmbServer_SelectedValueChanged(object sender, EventArgs e)
        {
            cmbServer.Enabled = false;
        }

        private void cmbDataBase_SelectedValueChanged(object sender, EventArgs e)
        {
            cmbDataBase.Enabled = false;
        }

        private void btn_backup_Click(object sender, EventArgs e)
        {
            if (cmbDataBase.SelectedIndex.ToString().Equals(""))
            {
                MessageBox.Show("Please choose Database", "Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (srvr != null)
            {
                try
                {
                    btn_backup.Enabled = false;
                    btn_restore.Enabled = false;
                    db_change.Enabled = false;
                    server_change.Enabled = false;

                    //Use this line if you have already created a bakup file.
                    File.Delete(DBpath + "\\backup.bak");
                    this.Cursor = Cursors.WaitCursor;
                    // If the user has chosen a path where to save the backup file
                    // Create a new backup operation
                    Backup bkpDatabase = new Backup();
                    // Set the backup type to a database backup
                    bkpDatabase.Action = BackupActionType.Database;
                    // Set the database that we want to perform a backup on
                    bkpDatabase.Database = cmbDataBase.SelectedItem.ToString();
                    // Set the backup device to a file
                    BackupDeviceItem bkpDevice = new BackupDeviceItem(DBpath + "\\Backup.bak", DeviceType.File);
                    // Add the backup device to the backup
                    bkpDatabase.Devices.Add(bkpDevice);
                    // Perform the backup
                    bkpDatabase.SqlBackup(srvr);
                    MessageBox.Show("Bakup of Database " + cmbDataBase.Text + " successfully created", "Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception x)
                {
                    MessageBox.Show("ERROR: An error ocurred while backing up DataBase"+x, "Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    this.Cursor = Cursors.Arrow;

                    btn_backup.Enabled = true;
                    btn_restore.Enabled = true;
                    db_change.Enabled = true;
                    server_change.Enabled = true;
                }
            }
            else
            {
                MessageBox.Show("ERROR: A connection to a SQL server was not established.", "Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.Cursor = Cursors.Arrow;
            }
        }

        private void btn_restore_Click(object sender, EventArgs e)
        {
            if (cmbDataBase.SelectedIndex.ToString().Equals(""))
            {
                MessageBox.Show("Please choose Database", "Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            // If there was a SQL connection created
            if (srvr != null)
            {
                try
                {
                    btn_backup.Enabled = false;
                    btn_restore.Enabled = false;
                    db_change.Enabled = false;
                    server_change.Enabled = false;

                    this.Cursor = Cursors.WaitCursor;
                    // If the user has chosen the file from which he wants the database to be restored
                    // Create a new database restore operation
                    Restore rstDatabase = new Restore();
                    // Set the restore type to a database restore
                    rstDatabase.Action = RestoreActionType.Database;
                    // Set the database that we want to perform the restore on
                    rstDatabase.Database = cmbDataBase.SelectedItem.ToString();
                    // Set the backup device from which we want to restore, to a file
                    BackupDeviceItem bkpDevice = new BackupDeviceItem(DBpath + "\\Backup.bak", DeviceType.File);
                    // Add the backup device to the restore type
                    rstDatabase.Devices.Add(bkpDevice);
                    // If the database already exists, replace it
                    rstDatabase.ReplaceDatabase = true;
                    // Perform the restore
                    rstDatabase.SqlRestore(srvr);
                    MessageBox.Show("Database " + cmbDataBase.Text + " succefully restored", "Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception)
                {
                    MessageBox.Show("ERROR: An error ocurred while restoring the database", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    this.Cursor = Cursors.Arrow;

                    btn_backup.Enabled = true;
                    btn_restore.Enabled = true;
                    db_change.Enabled = true;
                    server_change.Enabled = true;
                }
            }
            else
            {
                MessageBox.Show("ERROR: A connection to a SQL server was not established.", "Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.Cursor = Cursors.Arrow;
            }
        }
    }
}

BitBlt 452 Practically a Master Poster Featured Poster · 2011-05-04T23:23:38+00:00

Okay, I think I see the problem.
On line 18 above, you are setting the DBPath to be the Application.StartupPath. This directory probably does not exist on your server, thus the operating system error 3 (see this link: http://msdn.microsoft.com/en-us/library/ms681382(v=vs.85).aspx).

Using a BackupDeviceItem SMO object, SQL Server can only see its own file system. The only way it can see another machine is a) have a drive mapping to the other machine, with appropriate rights or b) use a UNC name to point to the other machine, with appropriate rights. Here's a link to an interesting thread that may also give you some additional pointers: http://stackoverflow.com/questions/728658/creating-sql-server-backup-file-bak-with-c-to-any-location

Hope this helps. Good luck!

judithSampathwa -2 Posting Pro in Training · 2011-05-05T00:36:44+00:00

Okay, I think I see the problem.
On line 18 above, you are setting the DBPath to be the Application.StartupPath. This directory probably does not exist on your server, thus the operating system error 3 (see this link: http://msdn.microsoft.com/en-us/library/ms681382(v=vs.85).aspx).
Using a BackupDeviceItem SMO object, SQL Server can only see its own file system. The only way it can see another machine is a) have a drive mapping to the other machine, with appropriate rights or b) use a UNC name to point to the other machine, with appropriate rights. Here's a link to an interesting thread that may also give you some additional pointers: http://stackoverflow.com/questions/728658/creating-sql-server-backup-file-bak-with-c-to-any-location
Hope this helps. Good luck!

ok so i changed the DB path to the server path
as @"\\Server-0\D:Document\Data\"

but this gives me an operating system error 53

how can i avoid this

BitBlt 452 Practically a Master Poster Featured Poster · 2011-05-05T00:46:39+00:00

From the MSDN link I posted:
ERROR_BAD_NETPATH 53 (0x35) The network path was not found.

You'll have to fix your code to construct a proper directory path and file name, either to the SQL Server machine or to whatever network resource you want to backup to. Until you do that you ill continue to have problems.

judithSampathwa -2 Posting Pro in Training · 2011-05-05T02:01:01+00:00

From the MSDN link I posted:
ERROR_BAD_NETPATH 53 (0x35) The network path was not found.
You'll have to fix your code to construct a proper directory path and file name, either to the SQL Server machine or to whatever network resource you want to backup to. Until you do that you ill continue to have problems.

how can i do that the flder path where the database sites the folder is not shared how can i do this???

BitBlt 452 Practically a Master Poster Featured Poster · 2011-05-05T02:10:05+00:00

You don't need the path where the database sits. The server connection object takes care of getting to the database. The path you specify is the folder where you want the backup file to be stored. You need permissions to that folder to write to it. Look at the other link I posted. Some other user had the same kind of problem as you. Read his code and compare it to yours.

judithSampathwa -2 Posting Pro in Training · 2011-05-05T03:41:38+00:00

You don't need the path where the database sits. The server connection object takes care of getting to the database. The path you specify is the folder where you want the backup file to be stored. You need permissions to that folder to write to it. Look at the other link I posted. Some other user had the same kind of problem as you. Read his code and compare it to yours.

which code are you referring to for me to read?
please prove the links

BitBlt 452 Practically a Master Poster Featured Poster · 2011-05-05T05:07:12+00:00

which code are you referring to for me to read?
please prove the links

Right after you posted your long program listing, I included a post with links. Those are the ones I am referring to. Please refer to those.

I apologize if I have added to your confusion. It is obvious I am not helping you. Perhaps someone else will be better able to assist with your problem. I will not post on this thread again.

sm sql database backup in C#.net

Recommended Answers

All 9 Replies

sm sql database backup in C#.net

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers