In SQL Server 2005 and above, we can make use of SYS.TRIGGERS to list all the Triggers within a database. The below query would help us list the trigger name and the table name on which this trigger is written.
We can make use of "SQLCMD" to execute scripts from Command prompt. If you are new to it I would suggest to go over my introductory posts on them here.
Example 1: Writing a batch file with the TSQL script to backup a DB.
Save the below sample script as a batch file (lets say, DBBackup.Bat).
set /p DBNAME=Enter database name:
sqlcmd -E -S %SQLSERVERNAME% -d master -Q "BACKUP DATABASE [%DBNAME%] TO DISK = N'%BACKUP%'"
We can now go to the SQL Command prompt by typing SQLCMD in Start > Run dialogue box. On executing DBBackup.Bat it would prompt us to enter the name of the database which has to be backed up. The backup file would be stored in C:\ drive. This is just a sample which can be modified or extended according to our need.
Just in case we have already have a Stored procedure to backup the DB for us. Then we can still make use of that SP directly within the Batch file.
Example 2: Calling a .SQL file within the batchfile to do the DB backup.
Save the below script as a SQL file. Say, "Backup.SQL"
Have provided the simpler script for demo purpose. You can get the DB name, backup name as parameters as well. And convert this into a solid Stored proc.
Change TESTDW with your database name.
BACKUP DATABASE TESTDW TO DISK = N'c:\testdw.bak'
Now the batch file DBBackup.BAT can be modified as follows: