Sql-server – Running a number of .sql files using SQLCMD

error handlingsql-server-2008-r2sqlcmd

I'm trying to put together a batch file to run a large number of .sql files without doing so individually.

So far I have:

@echo off
echo "Enter Server"
set /p SERVER=

echo "Enter Database"
set /p DATABASE=

del *.log

for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -o"%%i.log" -w500 

pause

This works as far as it goes.

However what I really want to do is route any errors to a separate .err file, so that it is very clear at a glance which of the (100+) files failed.

I saw the :Error parameter, but I'm not sure how to apply it here.

Is there an easy way to acheive this?

Best Answer

for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -w500  -b -r  >%%i.log  2>%%i.err

Not perfect since it creates an empty error output file when there are no errors, but a .err file with > 0 bytes will have the errors.

for %%a in (*.err) do if %%~za==0 del "%%a"

..if that's a problem...

Related Solutions

SQLCMD vs SSMS – Running Queries Comparison

The differences are probably due to SET settings, such as SET ARITHABORT. What happens is that these settings are taken into account when deciding on the plan to use, and sometimes you can get stuck with a really bad plan with one setting (and this probably happened because that application used non-optimal parameters), and the opposite setting has a good plan.

You can discover what settings are in play by checking sys.dm_exec_requests and sys.dm_exec_sessions when the query is running. The latter has columns for various settings (but you'll have to figure out the session_id from the former). Once you've discovered the differences, you can manually set the setting to the "better" setting from sqlcmd, and hope that you now get the better plan. Or, you can simply issue WITH RECOMPILE to avoid the parameter sniffing issue in the first place.

A really useful resource to read thoroughly is:

Slow in the Application, Fast in SSMS?

Sql-server – restore could not start database

Your restore is failing because there is no E:\ or F:\ on your machine.

Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):
```
RESTORE DATABASE ECO FROM DISK = 'path\file'
  WITH REPLACE, RECOVERY,
  MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
  MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
  MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
  MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
  MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';
```
In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.

Best Answer

Related Solutions

SQLCMD vs SSMS – Running Queries Comparison

Sql-server – restore could not start database

Related Question