none
Permissions - Backing up .bak file to another folder with c# RRS feed

  • Question

  • I have some code I want to use to back up my MS SQL Server database to a certain folder. The code is running and not giving any errors but when I look in the destination folder there is no .bak file

    What can  do so that the code saves the bak file in the folder I select not just in the MSSQL\Backup folder

    Does it have anything to do with file/folder permissions? How can i give SQL Server permissions to write to my folder? 

    public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            SqlConnection con = new SqlConnection("Data Source=" + ".\\SQL2014" + @";Initial Catalog = " + "tENDAI" + @";Integrated Security=SSPI;");
    
            private void Form1_Load(object sender, EventArgs e)
            {
    
            }
    
            private void BtnBackUpBrowse_Click(object sender, EventArgs e)
            {
                FolderBrowserDialog dlg = new FolderBrowserDialog();
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    TxtBackUpLocation.Text = dlg.SelectedPath;
                    BtnBackup.Enabled = true;
                }
            }
    
            private void BtnBackup_Click(object sender, EventArgs e)
            {
    
                if (TxtBackUpLocation.Text=="")
                {
                    MessageBox.Show("Select your backup location");
                    return;
                }
    
                string myBackUpPath = "";
                string backup = "EXEC  master.dbo.xp_instance_regread   N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer',N'BackupDirectory'";
                SqlDataAdapter myDataAdapter = new SqlDataAdapter(backup, con);
                DataTable myDataTable = new DataTable();
                myDataAdapter.Fill(myDataTable);
                if (myDataTable.Rows.Count > 0)
                {
                    myBackUpPath = Convert.ToString(myDataTable.Rows[0][1]);
                }
    
                string database = con.Database.ToString();
                try
                {
                    string myServerBackUpFilename= myBackUpPath + "\\" + "database" + "_" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".bak'";
                    string myBackUpFilename = "database" + "_" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".bak'";
    
                    string cmd = "BACKUP DATABASE [" + database + "] TO DISK='" + myServerBackUpFilename;
    
                        using (SqlCommand command = new SqlCommand(cmd, con))
                        {
                            if (con.State != ConnectionState.Open)
                            {
                                con.Open();
                            }
                            command.ExecuteNonQuery();
                            con.Close();
    
                            MessageBox.Show("database backup done successefully");
    
                        //System.IO.File.Copy(myServerBackUpFilename, @"C:\Users\Krish\Documents\ck" + @"\"+ myBackUpFilename);
    
                            BtnBackup.Enabled = false;
                        }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
    
            private void BtnRestoreBrowse_Click(object sender, EventArgs e)
            {
                OpenFileDialog dlg = new OpenFileDialog();
                dlg.Filter = "SQL SERVER database backup files|*.bak";
                dlg.Title = "Database restore";
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    TxtRestoreLocation.Text = dlg.FileName;
                    BtnRestore.Enabled = true;
                }
            }
    
            private void BtnRestore_Click(object sender, EventArgs e)
            {
                string database = con.Database.ToString();
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                try
                {
                    string sqlStmt2 = string.Format("ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
                    SqlCommand bu2 = new SqlCommand(sqlStmt2, con);
                    bu2.ExecuteNonQuery();
    
                    string sqlStmt3 = "USE MASTER RESTORE DATABASE [" + database + "] FROM DISK='" + TxtRestoreLocation.Text + "'WITH REPLACE;";
                    SqlCommand bu3 = new SqlCommand(sqlStmt3, con);
                    bu3.ExecuteNonQuery();
    
                    string sqlStmt4 = string.Format("ALTER DATABASE [" + database + "] SET MULTI_USER");
                    SqlCommand bu4 = new SqlCommand(sqlStmt4, con);
                    bu4.ExecuteNonQuery();
    
                    MessageBox.Show("database restoration done successefully");
                    con.Close();
    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }    
            
        }


    If you think it you can achieve it

    Monday, February 11, 2019 11:53 AM

Answers

  • Hi tendaimare,

     

    Thank you for your posting.

     

    Would you like to know how to grant the SQL Server Database Engine, file system access to the location where database files are stored? Please refer to Configure File System Permissions for Database Engine Access. Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by tendaimare Tuesday, February 12, 2019 3:17 PM
    Tuesday, February 12, 2019 7:15 AM

All replies

  • Yep, if the account that runs backup command does not have permission on the folder the error will be thrown.

    Make sure that this account has   all needed permissions.

    >>>How can i give SQL Server permissions to write to my folder? 

    Make sure that you connect  to the  server as admin and right click on the folder --properties -- security tab


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, February 11, 2019 12:06 PM
    Answerer
  • What's wrong with using SMO?

    Monday, February 11, 2019 12:10 PM
  • Database backups are run from the server context.  The backup folder must be accessible to the SQL Server and the SQL Server service account.  You cannot backup to a local folder on your hard drive without creating a network share to your local hard drive.

    Monday, February 11, 2019 12:14 PM
    Moderator
  • This is something along the lines I am looking for. How do I make my backup folder accessible to SQL Server. I have the Folder that I have designated for backup files. I went to the Properties of the floder then Security tab what can I do after that I pressed Edit in the Group or User names section. >> Pressed Add >> Advanced >> Find Now and got stuck. I dont know which user to select as my SQL instance is not showing up as an option

    How can I procced after this


    If you think it you can achieve it

    Monday, February 11, 2019 1:22 PM
  • You need to find out what service account your SQL Server is using (Services on the SQL Server machine) and specify that service account. This assumes that it uses a domain account (if on a different machine).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, February 11, 2019 2:50 PM
    Moderator
  • Okay I have found out the service account. How do I use it to allow SQL Server to write in one of my folders.

    I searched in myDesignatedBackupFolder>>Properties>>Security>>Edit>>Add>>Advanced>>Find Now and I cannot find the SQL Server service account listed there


    If you think it you can achieve it

    Monday, February 11, 2019 2:58 PM
  • You have to type the name, it won't show up when browsing. And, the client and server need to be on the same machine for this to work.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, February 11, 2019 3:01 PM
    Moderator
  • Hi tendaimare,

     

    Thank you for your posting.

     

    Would you like to know how to grant the SQL Server Database Engine, file system access to the location where database files are stored? Please refer to Configure File System Permissions for Database Engine Access. Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by tendaimare Tuesday, February 12, 2019 3:17 PM
    Tuesday, February 12, 2019 7:15 AM
  • Hi Rachel 

    I followed the instructions on the link you posted even though I had gone through almost a similar procedure before. When I followed the procedure on this link the one diference was that  did not get a Multiple names Dialogue as stated in the steps. I was doing a lot of my coding on a Windows 10 Home 64 Bit machine so I thought that could be the problem so i moved the code to a Windows 10 Pro 64 Bit machine and I followed the same steps on the link you attached on that machine and the code is still not backing up in my designated backup folder

    While going through the code I have realized that when I query for the backup directory as shown in the picture my designated backup directory is not even present in the datatable - I was expecting to find "C:\Users\MyUserName\Documents\ck" also in that list. But after running the code it is still backing up in the Program Files SQL backup folder


    If you think it you can achieve it

    Tuesday, February 12, 2019 3:08 PM
  • Thanks I changed The back up button code and now it works.Thanks Rachel_Wang and TiborK and others that pointed me in the right direction
    private void BtnBackup_Click(object sender, EventArgs e)
            {
    
                if (TxtBackUpLocation.Text=="")
                {
                    MessageBox.Show("Select your backup location");
                    return;
                }
    
                string myBackUpPath = TxtBackUpLocation.Text;
    
                string database = con.Database.ToString();
                try
                {
                    string myServerBackUpFilename= myBackUpPath + "\\" + "database" + "_" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".bak'";
                    string myBackUpFilename = "database" + "_" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".bak'";
    
                    string cmd = "BACKUP DATABASE [" + database + "] TO DISK='" + myServerBackUpFilename;
    
                        using (SqlCommand command = new SqlCommand(cmd, con))
                        {
                            if (con.State != ConnectionState.Open)
                            {
                                con.Open();
                            }
                            command.ExecuteNonQuery();
                            con.Close();
    
                            MessageBox.Show("database backup done successefully");
    
    
                            BtnBackup.Enabled = false;
                        }               
    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }


    If you think it you can achieve it

    Tuesday, February 12, 2019 3:17 PM
  • If you need to manage your database, you should set up scheduled jobs to back up your databases (both full and log backup).

    Ola Hallengren has a script to make things easy. You can find the script here:

    https://ola.hallengren.com/

    Tuesday, February 12, 2019 4:08 PM
    Moderator