locked
MS SQL backup to a local path from remote server RRS feed

  • Question

  • I have an ISP that to say the least is not very helpful. I want to move to another ISP but need a backup of my database. Their Plesk database backup option is not work, and despite ticket after ticket I cannot get them to let me get a backup. So I decided to do my own backup. The problem is I need the backup to backup to the local system, as their MS SQL box is not on the same machine as my web site. I have no idea how their local folder structure works but when I pull the current folder (see below) then folder is in the inetsrv folder. I have a textbox (tbxFileName) that i populate to try and save the database but keep getting an error. Can any one help. Code below:

    protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) {
              string s = System.IO.Directory.GetCurrentDirectory();
     
              tbxFileName.Text = s + "\\dbbackup";
            }
        }
     
    
        protected void btnDoBackup_Click(object sender, EventArgs e)
        {
            try
            {
                string destDir = tbxFileName.Text;
                string StrConString = System.Configuration.ConfigurationManager.ConnectionStrings["MyDBConnString"].ToString();
     
                SqlConnection sqlcon = new SqlConnection(StrConString);
                SqlCommand sqlcmd = new SqlCommand();
                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
     
                //Check that directory already there otherwise create 
                if (!System.IO.Directory.Exists(destDir))
                {
                    System.IO.Directory.CreateDirectory(destDir);
                }
                //Open connection
                sqlcon.Open();
                //query to take backup database
                sqlcmd = new SqlCommand("backup database test to disk='" + destDir + "\\DBBackup" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".Bak'", sqlcon);
                sqlcmd.ExecuteNonQuery();
                //Close connection
                sqlcon.Close();
                ltrlMsg.Text = "Backup database successfully";
            }
            catch (Exception exp)
            {
                ltrlMsg.Text = "ERROR: " + exp.Message;
            }
     
        }

    This is the error I get if I try and run the above it fails at the exists folder point Quote: ERROR: Access to the path 'c:\windows\system32\inetsrv\dbbackup' is denied.


    Warren

    • Moved by Bob Shen Thursday, September 6, 2012 6:11 AM (From:Visual C# General)
    Wednesday, September 5, 2012 5:09 PM

Answers

All replies

  • Hi Warren,

    You are getting that error because of permission to access that system folder, try copy it to another location let say C:\Backup.

    Sam.

    Wednesday, September 5, 2012 5:23 PM
  • This will not work. Can you access it with SSMS? Then it's quite simple otherwise you need to execute the necessary commands blindly from your IIS against it:

    Create a backup at a know SQL Server local drive. Activate the usage of xp_xmdshell. Use it to zip the backup file. Insert zip into a table using a VARBINARY(MAX). Then you can copy it from the table column into a download page..

    Wednesday, September 5, 2012 5:30 PM
  • If you just need a one time back, log into the database using the IP address and SQL Server Management Studio.  It very easy to make a backup then.
     "Warren Machanik from SBPortal" wrote in message news:0afdd85a-435e-4b20-a0d3-99c807da026f@communitybridge.codeplex.com...

    I have an ISP that to say the least is not very helpful. I want to move to another ISP but need a backup of my database. Their Plesk database backup option is not work, and despite ticket after ticket I cannot get them to let me get a backup. So I decided to do my own backup. The problem is I need the backup to backup to the local system, as their MS SQL box is not on the same machine as my web site. I have no idea how their local folder structure works but when I pull the current folder (see below) then folder is in the inetsrv folder. I have a textbox (tbxFileName) that i populate to try and save the database but keep getting an error. Can any one help. Code below:

    protected void Page_Load(object sender, EventArgs e)
         {
                 if (!IsPostBack) {
                     string s = System.IO.Directory.GetCurrentDirectory();
                        tbxFileName.Text = s + "\\dbbackup";
                 }
         }
            protected void btnDoBackup_Click(object sender, EventArgs e)
         {
                 try
                 {
                         string destDir = tbxFileName.Text;
                         string StrConString =
    System.Configuration.ConfigurationManager.ConnectionStrings["MyDBConnString"].ToString();
                            SqlConnection sqlcon = new SqlConnection(StrConString);
                         SqlCommand sqlcmd = new SqlCommand();
                         SqlDataAdapter da = new SqlDataAdapter();
                         DataTable dt = new DataTable();
                            //Check that directory already there otherwise create
                         if (!System.IO.Directory.Exists(destDir))
                         {
                                 System.IO.Directory.CreateDirectory(destDir);
                         }
                         //Open connection
                         sqlcon.Open();
                         //query to take backup database
                         sqlcmd = new SqlCommand("backup database test to disk='" +
    destDir + "\\DBBackup" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".Bak'",
    sqlcon);
                         sqlcmd.ExecuteNonQuery();
                         //Close connection
                         sqlcon.Close();
                         ltrlMsg.Text = "Backup database successfully";
                 }
                 catch (Exception exp)
                 {
                         ltrlMsg.Text = "ERROR: " + exp.Message;
                 }
            }

     This is the error I get if I try and run the above it fails at the exists folder point Quote: ERROR: Access to the path 'c:\windows\system32\inetsrv\dbbackup' is denied.
     -- Warren

    Wednesday, September 5, 2012 6:32 PM
  • Hi thanks for the reply I do not have access to the SQL server, besides using ASP Enterprise manager. I have no idea of their folder structures.


    Warren

    Thursday, September 6, 2012 4:54 AM
  • I do not know what SSMS is will do some research, thanks

    Warren

    Thursday, September 6, 2012 4:55 AM
  • I am unclear to what you mean I only have access to asp.net enterprise manger on the SQL box.

    Warren

    Thursday, September 6, 2012 4:56 AM
  • Hi Warren,

    Thank you for visiting the MSDN forum. I’m afraid that it is not the correct forum about this issue. You can post it in "Where is the forum for..?" http://social.msdn.microsoft.com/Forums/en-US/whatforum/threads. The owner of the forum will direct you to a right forum. Thanks for your understanding.


    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us


    • Edited by Bob Shen Thursday, September 6, 2012 6:15 AM
    Thursday, September 6, 2012 6:11 AM
  • I looked at the link before posting and after scratching my head choose the C# forum since I thought that was the best fit. Which forum should this be posted in? It is a c# question for web forms, and backing SQL progammatically?

    Warren

    Thursday, September 6, 2012 7:00 AM
  • Hi Warren,

    "It is a c# question for web forms"

    You'll need to post it in the dedicated ASP.Net Forum http://forums.asp.net for more efficient responses. Thanks for your understanding.

    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, September 6, 2012 7:24 AM
  • I got the ISP to map a folder for me, and all is working thanks

    Warren

    Thursday, September 6, 2012 10:38 AM