Executing a SQL statement from command prompt:Sometimes we need to execute some –T-SQL script from command prompt. This is a common requirement when some t-sql scripts are needed to be scheduled by windows scheduler.
Here I will explain how we can execute T-SQL script from command prompt. Below I created an executable file (.bat) to create a backup of AdventureWorks database so that I can schedule this executable using Windows scheduler.
Create a text file for example c:\sql.txt and write below backup script in it:
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\SQL_Backup\AdventureWorks.bak' WITH INIT
Create another batch file for example Backup.bat and write the below statement inside it:
sqlcmd -S ServerName -U yourLogin -P yourPassowrd -i c:\sql.txt
Now we can execute this Backup.bat file from command prompt or can schedule using windows scheduler. Thus we have scheduled a task that is not dependent on SQL server Agent service.