How to back up sql server 2014 db data via bat file?

Question

I am using this query in bat file to back-up db,

sqlcmd -S .\MSSQLSERVER -E -Q "EXEC sp_BackupDb @backupLocation='D:\SQLBackups\', @databaseName='TestDb', @backupType='F'" pause

It is throwing this error,

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Inte rfaces: Connection string is not valid [87]. Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : 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..

Can someone please help? Thanks in advance.

Try removing the instance name from the server name specification: `-S .` instead of `-S .\MSSQLSERVER`. — Dan Guzman, Jun 15 '16 at 12:12
Does sqlcmd -S .\MSSQLSERVER -E -Q "select 1" return an error?l — PollusB, Jun 15 '16 at 18:25

score 0 · Answer 1 · edited May 23 '17 at 12:33

you can use SQLCMD in a .bat file to make this bakcup, in this link you have an example stackoverflow link

SQLCMD:

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses OLE DB to execute Transact-SQL batches.

here is the link for examples and definition: msdn link definition

for example:

sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT , NOUNLOAD , NAME = N'%DATABASENAME% backup', NOSKIP , STATS = 10, NOFORMAT"

hope this help!

How to back up sql server 2014 db data via bat file?

1 Answers1