SQL Server 2014 Express making a bat file to run a SQL script to backup at night

[leo57]

This is a SQL Express install so there is no Agent listed, so I can't create a job.
When I run the SQL script below either in the batch file or at sqlcmd prompt it just opens the .sql file in SSMS and does not run it. I want this to run unattended at night. What do I need to do to make this work?
Shown below are my two files: the first is my sql script, second is the command prompt
The SQL script creates a backup file just fine when I run it manually in SSMS, however the purpose of this is unattended.
Or any other suggestions would be great

TIA

-- get todays date
Declare @TodaysDate as nvarchar(10) = Replace(CONVERT(nvarchar(10), getdate(), 103),'/', '')
Declare @FileName as nvarchar(100) = N'D:\SQLBackup\StockTrack_' + @TodaysDate + '.bak' 

BACKUP DATABASE [StockTrack] TO  DISK = @FileName
WITH NOFORMAT, 
NOINIT,  NAME = N'StockTrack-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

sqlcmd -S ServerName\SQLExpress -i C:\mybackup.sql

 

[philhege]

Try Powershell:

http://thomaslarock.com/2012/07/automate-sql-server-express-backups-with-powershell/

-----------
With business clients like mine, you'd be better off herding cats.

 

[softhemc]

As an alternative - SQL Server 2014 Developer Edition DOES have the Agent AND is now FREE (as is SQL Server 2016 Developer Edition - but this is a much more stressfull install!!)

 

[gmmastros]

I've done this a hundred times for my customers. What I usually do is to create a stored procedure in the database, and then use SQLCMD to execute that stored procedure.

Regardless, you're missing something very important on the command line. Try this:

sqlcmd -S ServerName\SQLExpress -i C:\mybackup.sql [!]-Q[/!]

According to the documentation, -Q means "cmdline query" and exit.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[yelworcm]

In order to schedule the job, you could always use Windows Scheduler. You just need to work out how you do the error checking, as I am not too familiar with options around failed jobs.