Having problem with SQLCMD - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Having problem with SQLCMD

Author

Topic

whitemtntn
Starting Member

5 Posts

Posted - 2014-06-30 : 12:07:00

In Mgmt Studio I created 3 Backup Devices and am attempting to use SQLCMD to run backups
The command is:
sqlcmd -S MYSERVER\MSSQLSERVER -E -Q "BACKUP MillCfg TO MillCfgBkup"

(For "MYSERVER" I put the Windows name of the server. We are using the default instance.)

However, it seems I am having problem connecting to my instance. From a command prompt I get:
HResult 0x57, Level 16, State 1
SQL Server Network Interfaces: Connection string is not valid [87].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

If I run the command in Mgmt Studio I get:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'S'.
That's even if I just run sqlcmd -S MYSERVER\MSSQLSERVER

We're using SQL Server 2008R2 EXPRESS
Any help is appreciated. Thanks very much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-30 : 12:32:29

Use MYSERVER only when using the default instnace.

For Management Studio, you'd have to use xp_cmdshell to call sqlcmd.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

whitemtntn
Starting Member

5 Posts

Posted - 2014-06-30 : 12:59:45

ok-- I enabled the use of XP_cmdshell in "Surface Area Configuration"
Since I am using the default instance, I am using MYSERVER...
What next?
Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-30 : 13:02:21

Next is to try the sqlcmd command again using just MYSERVER and not including an instance name. Once you have the command working, you can then throw it into xp_cmdshell if you want to run it via Management Studio. Please note that this is not recommended though and is why it is disabled by default. It is a security risk.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

whitemtntn
Starting Member

5 Posts

Posted - 2014-06-30 : 13:15:14

Using an elevated command prompt I did try:
sqlcmd -S MYSERVER -E -Q "BACKUP MillCfg TO MillCfgBkup"

Now I get this message:
Msg 102, Level 15, State 1, Server MYSERVER, Line 1
Incorrect syntax near 'MillCfg'.
I verified "MillCfg" is the correct name of the database, and "MillCfgBackup" is the correct name of the Backup Device I created.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-30 : 13:24:31

BACKUP DATABASE MillCfg TO MillCfgBkup

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

whitemtntn
Starting Member

5 Posts

Posted - 2014-06-30 : 13:33:14

Hey that worked. Thanks very much for your help!
Now I have 3 of these commands to run. I verified that they all work.
I want to put them in a batch file and schedule it. Any reason I can't do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-30 : 13:37:40

Yes a batch file is fine.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

whitemtntn
Starting Member

5 Posts

Posted - 2014-06-30 : 14:16:27

Thanks again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-30 : 14:17:30