Click here to Skip to main content
14,972,848 members
Please Sign up or sign in to vote.
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
Updated 10-Nov-11 2:10am
v2
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

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"
   
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.
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.
   
v2
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
   
v2
Comments
LebneizTech 11-Nov-11 7:00am
   
Sir, from this Syntax : System.Diagnostics.Process.Start("cmd.exe");
Command Prompt is Opening, But not Batch File(backup.bat) Stored in Root Folder is not Opening.
As I have to open backup.bat
Amir Mahfoozi 11-Nov-11 7:48am
   
I have updated the answer.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900