?
Solved

Microsoft SQL Express 2005 database backup automation problem / error ( command line )

Posted on 2008-11-07
5
Medium Priority
?
1,242 Views
Last Modified: 2012-05-05
I am trying to automate backing up a daatabase in MS SQLExpress 2005 and am doing it from the command line. The exact command I am issuing (the same box that SQL exist on) is:

SqlCmd -E -S (Local) -Q"BACKUP DATABASE [control_point] TO DISK = N'c:\backup\control_point.B
ak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

When issued I get the following errors:

HResult 0x7E, Level 16, State 1
VIA Provider: The specified module could not be found.
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.
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

I know that remote connections are allowed. I found the syntax to use from the command line online. Any help resolving the matter would truly be appreciated.

0
Comment
Question by:bkaila
  • 3
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
Cedric_D earned 2000 total points
ID: 22906729
You should specify credentials for sqlcmd to access SQL Sever.



-E option means trusted connection, i.e. windows authentification for logged-on user.

To test it, login first in SQL Management Studio to (Local) db with the Windows Authentification.

If it works, then probably you use your call in Task Scheduler which is run without logon. Then you should specify username and password at Task Scheduler.

The other reason could be - you have named instance, so you should specify -S SEVERNAME\INSTANCENAME or -S IP-address\INSTANCENAME

And again, test it with SQL Studio first.
0
 

Author Closing Comment

by:bkaila
ID: 31514442
Your awesome!
It was the SEVERNAME\INSTANCENAME that did it

What would the switches be if if you could only use SQL (sa) authentication?

Thank you!
0
 

Author Comment

by:bkaila
ID: 22907436
What would the switches be if if you could only use SQL (sa) authentication?
0
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22908585
To use SQL Authentication instead of Windows, you have to replace -E switch with "-U user -P password" switches.
0
 

Author Comment

by:bkaila
ID: 22908627

Thank you again Cedric!!!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question