Batch Script to execute SQL command and Save the output as text file

Follow the below steps to execute SQL command and Save the output as text file through batch file:

Step 1: Prepare an SQL query and save the query as sql file (e.g., Test.sql).

Step 2: Create batch file as shown below:
--======================================================================
@ECHO OFF
SETLOCAL

REM Build an pipe delimited text file
SET PATH = C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3;
SqlCmd -S DDWSBDB01CS -d EDW20 -s"|" -E -i "C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3\Test.sql" -o "C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3\Test.Txt"
ECHO.

echo Completed Successfully at %date% %time%>>_date_.txt
--====================================================================

Step 3. Run the batch file, you can notice a text file created in the specified path with the SQL query output.

To get quotes delimited output use the following:
SqlCmd -S 192.2.200.99 -E -s'\t' -i "E:\Work Area\FOF\Iteration24\Employee.sql" -o "E:\Work Area\FOF\Iteration24\OutputQuotes.txt"

To get tab delimited output use the following:
SqlCmd -S 192.2.200.99  -i "E:\Work Area\FOF\Iteration24\Employee.sql" -o "E:\Work Area\FOF\Iteration24\OutputQuotes.txt"

Batch Script to take SQL Server DB Bakup

@ECHO OFF
SETLOCAL
DEL D:\MercuryDBBackup\DBBACKUP\Mercury.bak
echo Deleting Mercury.bak finished at %date% %time%
DEL D:\MercuryDBBackup\DBBACKUP\SalesDW_Mercury.bak
echo Deleting SalesDW_Mercury.bak finished at %date% %time%
DEL D:\MercuryDBBackup\DBBACKUP\Mercury_Scope.bak
echo Deleting Mercury_Scope.bak finished at %date% %time%

REM Build a list of databases to backup
SET DBList=%SystemDrive%\SQLDBList.txt
SqlCmd -E -S 192.168.50.52\MERCURY -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] IN ('Mercury','SalesDW_Mercury','Mercury_Scope')" > "%DBList%"
REM Backup each database, prepending the date to the filename
FOR /F %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S 192.168.50.52\MERCURY -Q "BACKUP DATABASE [%%I] TO Disk='D:\MercuryDBBackup\DBBACKUP\%%I.bak'"
ECHO.
)
REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

echo Completed Successfully at %date% %time%>>_date_.txt

Command to copy fiile from one server to another:

Command to copy fiile from one server to another:

set path=c:\WINDOWS\system32;
echo Copying started at %date% %time%>>_date_.txt
xcopy file://devsql2000/Datafiles/CRM/CRMXMLFile.xml file://dbnew/Datafiles/CRM/CRMXMLFile.xml /s /a /d

echo Copying finished at %date% %time%>>_date_.txt
echo Completed Successfully at %date% %time%>>_date_.txt
echo --------------------------------------------------------------------------- >>_date_.txt