0

I have created a batch with the following. ( Batch file name is BACKUP_TASK.bat )

SQLCMD -E -S SQLEXPRESS 
DECLARE @DatabaseName sysname = N'DEMO_DB',
@DatabaseBackupFileName varchar(255);
SET @DatabaseBackupFileName = 'C:\sql\' + @DatabaseName + '_' + REPLACE(replace(convert(varchar(40),getdate()),' ','_'),':','_') + '.bak'; 
BACKUP DATABASE @DatabaseName TO DISK = @DatabaseBackupFileName; 
GO 
exit 
exit

If I copy paste this code directly to Command Prompt it Executes as Expected.But if I double click on the batch file then the Command Prompt opens up and only the first line is compiled and nothing happen further more. So what I am doing here wrong ? Please help me solve this.

jarlh
  • 42,561
  • 8
  • 45
  • 63
vishnudas
  • 53
  • 1
  • 10
  • 1
    that's because the batch interpreter waits for `sqlcmd` to finish, before trying to execute the next line. – Stephan Jan 08 '20 at 10:53
  • 2
    `sqlcmd.exe` is started by Windows command processor `cmd.exe` and then Windows command processor waits for the termination of started `sqlcmd.exe` before it reads the next command line from batch file, parses it and executes it, see [How does the Windows Command Interpreter (CMD.EXE) parse scripts?](https://stackoverflow.com/questions/4094699/) `sqlcmd.exe` does not read the SQL instructions to execute from the batch file interpreted by `cmd.exe`. Please read the Microsoft documentation of [sqlcmd](https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017). – Mofi Jan 08 '20 at 11:12
  • 4
    Seems like you would be better off putting your SQL statements (up to but *not* including `GO`) in a `.sql` file and using the `-i` switch. – Thom A Jan 08 '20 at 11:15
  • @laru: i have created one SQL file as your suggestion and made the batch file as CALL SQLCMD -E -S SQLEXPRESS -i C:\sql\backup_cmnd.sql ( backup_cmnd.sql contains sql commands ) and finally it Executed as expected.Thank you for your valuable suggestion – vishnudas Jan 08 '20 at 11:49
  • @mofi:Thank you for your suggestion and introducing me to properties of CMD.exe – vishnudas Jan 08 '20 at 11:52
  • @vishnudas learn to avoid recreating the wheel. MS has a old (but still useful) document that covers this entire process in a more complete and automated fashion [here](https://support.microsoft.com/en-us/help/2019698/how-to-schedule-and-automate-backups-of-sql-server-databases-in-sql-se). You really need to put in place a disaster recovery plan - and that involves more than just this one database. – SMor Jan 08 '20 at 13:04
  • @SMor : Thank you for Your Suggestion,Will try the way you suggested. – vishnudas Jan 09 '20 at 12:58

0 Answers0