Correct the Syntex for : Backup Locally and then Copy to the Network Share

Question

2.33/5 (3 votes)

See more:

Sir,

I have Syntax to Backup SQL DataBase Locally and then Copy to the Network Share.

My Local Path is D:\db and Share Path C:\Data and backupfile : dbP.bak
DataBase Name : hospitaA

Please correct the Syntax to use it.

The Syntax :

SET LocalFolder=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQLBackup
SqlCmd -E -Q "Backup Database MyDB To Disk=’%LocalFolder%\MyDB.bak’"
XCopy "%LocalFolder%\MyDB.bak" "\\192.168.16.55\BackupDatabases" /Z /V
DEL "%LocalFolder%\MyDB.bak"

Posted 10-Nov-11 1:55am

LebneizTech

Updated 10-Nov-11 2:10am

Mehdi Gholam

v2

Add a Solution

Comments

Mehdi Gholam 10-Nov-11 8:11am

Whats wrong with it?

LebneizTech 10-Nov-11 8:24am

Sir, It do not RUN on SQL Query Analyzer due to Syntax Error

3 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)

Heino Zunzer · Answer 1 · 2011-11-10T02:22:00

Solution 1

Correct Syntax:

VB

SET LocalFolder=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQLBackup
SqlCmd -E -Q "backup database MyDB to disk='$(LocalFolder)\MyDB.bak'"
XCopy "%LocalFolder%\MyDB.bak" "\\192.168.16.55\BackupDatabases" /Z /V
DEL "%LocalFolder%\MyDB.bak"

Posted 10-Nov-11 2:22am

Heino Zunzer

Comments

LebneizTech 10-Nov-11 8:27am

Sir It is not running on SQL Query Analyzer due to Syntax Error

Heino Zunzer 10-Nov-11 13:35pm

erm, yes, because it is not SQL Query Analyzer syntax... its syntax for a batch
file.
sqlcmd is a command line tool to execute SQL Server commands from the command line.
Copy the above code to notepad.exe, save as Backup.bat and run backup.bat from a command prompt.

LebneizTech 11-Nov-11 2:52am

Sir, May I can RUN this Syntax from Client Side (.NET .aspx Page),so that I can able to store my Backup on Network Disk

LebneizTech 11-Nov-11 5:52am

Sir, there is Connection error :

C:\Documents and Settings\PP>sqlcmd -U "sa" -P "sa" -S "A" -Q "backup database HospitalA to disk='D:\DB\MyDB.bak'"
ERROR :
HResult 0x15, Level 16, State 1
Encryption not supported on the client.
Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connection.
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote connections..
Please Correct

Heino Zunzer 11-Nov-11 6:01am

Please ensure that you understand, what you are doing. Don't execute blindly code you copy off the internet. Google the tools at least and see what the parameters mean.
http://msdn.microsoft.com/en-us/library/ms162773.aspx

In this case of course you need to provide a proper server name. -S is the parameter for the sql server instance name, and I doubt your SQL Server is called "A". It will be something like "localhost" or "srv-blablasql".
-U stands for user. (btw, "sa" is the sql sys admin user. you sure he has password "sa". If so, fire your DBA).
-P is for password.
-Q is for Query

and sqlcmd has many more possible parameters, just look at the link.

Heino Zunzer · Answer 2 · 2011-11-10T22:39:00

oh, I see...

C#

string sqlConnectionString = "Data Source=(local);Initial Catalog=MyDB;Integrated Security=True";
string sqlCommand = "backup database MyDB to disk='C:\\Backup\\MyDB.bak'";

SqlConnection con = new SqlConnection(sqlConnectionString);
SqlCommand com = new SqlCommand(sqlCommand);
com.ExecuteNonQuery();
con.Close();

// copy file to newtwork location
...

Note that the path C:\Backup\MyDB.bak will be on your SQL Server machine! Not on the client or anywhere else. SQL Server can only reference local drives and UNC paths. (But don't use network shares for backup. Just don't.)
Backup locally on your SQL Server and then have move the file to a network location.

Hope that helps.

Amir Mahfoozi · Answer 3 · 2011-11-10T23:59:00

As others say they are bunch of command line commands so do not try to run them in query analyzer.
instead make a batch file and put them in there and run the batch file.

And if you want to run the batch file from client sides of an ASP.NET web form then consider using this code in your webform :

C#

System.Diagnostics.Process.Start("backup.bat");

For more information read these resources :

http://forums.asp.net/t/164085.aspx/1[^]

http://support.microsoft.com/kb/555134[^]

After your comment :

Please try this one :

C#

System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = @"cmd.exe";
process.StartInfo.Arguments = @"/k f:\cpy.bat";
process.StartInfo.WorkingDirectory = @"f:\";
process.Start();

Please change the batch file name and working directory to a proper one. And remember to add an Exit command at the end of your batch file.

If it helped you please mark it as an answered question or vote it up.

Hope It Helps

Correct the Syntex for : Backup Locally and then Copy to the Network Share

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0