Unable to take SQL database backup in C# in client side

Question

0.00/5 (No votes)

See more:

My software installed in 3 systems. 1. is main server system and remaining two are clients. power on that server system and running software in 2 client systems and sharing data like updating, inserting, taking reports Etc. After working I want to take backup in client systems itself. For that I wrote one C# code but I am unable to take backup in client systems.It is creating one folder but failed to create file. Same code if I ran in server, then backup is working.

Here is my code

string drive = Path.GetPathRoot("D:");   // e.g. K:\
                con.Close();
                con.Open();

                if (!Directory.Exists(drive))
                {
                    MessageBox.Show("Drive " + drive + " not found or inaccessible",
                                    "Error", MessageBoxButtons.OK);
                    con.Close();
                    con.Open();
                    CreateIfMissing("C:\\Billindiaback");
                    string query = string.Format("BACKUP DATABASE inventoryDB  TO DISK = 'C:\\Billindiaback\\InventoryDB{0}.Bak' WITH FORMAT, MEDIANAME = 'dbName', NAME = 'Full Backup of dbName';", DateTime.Now.ToString("hhmmssddMMyyyy"));
                    SqlDataAdapter sda = new SqlDataAdapter(query, con);
                    sda.SelectCommand.ExecuteNonQuery();
                    con.Close();

                }
                else
                {
                    con.Close();
                    con.Open();
                    CreateIfMissing("D:\\Billindiaback");
                    string query = string.Format("BACKUP DATABASE inventoryDB  TO DISK = 'D:\\Billindiaback\\InventoryDB{0}.Bak' WITH FORMAT, MEDIANAME = 'dbName', NAME = 'Full Backup of dbName';", DateTime.Now.ToString("hhmmssddMMyyyy"));
                    SqlDataAdapter sda = new SqlDataAdapter(query, con);
                    sda.SelectCommand.ExecuteNonQuery();
                //MessageBox.Show(sda.SelectCommand.ExecuteNonQuery().ToString());

                }

What I have tried:

I don't know where I did mistake. I checked properties of that folder and add permissions for network service also. But unable to solve that.

Posted 31-Oct-17 3:36am

vijay_bale

Updated 31-Oct-17 4:27am

v2

Add a Solution

Comments

F-ES Sitecore 31-Oct-17 9:53am

It's going to use those folders on the server where the back-up is running, not on the client where you are calling it from. Try and save the backup to a network folder that maps to the client like \\clientname\somefolder\somefile.bak - not sure if that is supported though.

vijay_bale 31-Oct-17 9:57am

In client side, in which drive system will create that folder

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2017-10-31T04:06:00

You can't do that. The backup operation runs as one of the SQL Server accounts ON THE SERVER, NOT THE CLIENT. That account will NOT have permissions to the client machines network shares, if there are any. It doesn't appear as though there are.

A backup is not an operation typically done by clients. It's normally done as a scheduled job in SQL Server. The backup file is usually placed on a network share, off the SQL Server. This share has to be setup and the account running the backup job given permissions to write files to the share.

Your code has nothing to do with the problem, other than giving the wrong path to write the backup to.

massimiliano aronica · Answer 2 · 2017-10-31T04:27:00

1. I wouldn't use SqlDataAdapter class, it's not appropriate for your purpose. What you need is an SqlCommand :

SqlCommand comm= new SqlCommand(query,objConnection)
objConnection.Open();
comm.ExecuteNonQuery();
objConnection.Close();

2.Ensure you have writing rights in the folder where you point backup to.
3.If you need backup file at client side, download it from the generated file.

If I'm not wrong, ExecuteNonQuery has an async execution, so you may have the control back when backup probably is not yet completed. Take care of that.

Greetings!! :)

Unable to take SQL database backup in C# in client side

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0