Executing series of SQL commands on the command line

Question

I want to run a series of SQL statements against a SQL Server 2005 database from the command line.

When I launch 1st statement

osql -E -S <Server_Name>\<Instance_Name> -d <Server_Name>

it is going to prompt window 1> from there after I am unable to proceed further through script.

How to give input to 1> prompt I mean giving next SQL statement

BACKUP DATABASE  TO DISK = 'c:\test.bak' WITH INIT,SKIP

and finally exit to that prompt

I tried with && but I guess that is for only commandline commands.

rene · Accepted Answer · 2012-11-17 12:14:30Z

2

You ae looking for the -Q switch on the sqlcmd tool (don't use osql on sqlserver 2005 or higher) (type sqlcmd /? to see all options) or lookit up on msdn

sqlcmd -E -S <Server_Name>\<Instance_Name> -d <Server_Name> -Q "BACKUP DATABASE TO DISK = 'c:\test.bak' WITH INIT,SKIP"

Alternatively you can create a sqlscript file where you put all the sql statements in you want to execute. Assuming you name your file myscript.sql the osql command would go like this:

 sqlcmd -E -S <Server_Name>\<Instance_Name> -d <Server_Name> -i myscript.sql

edited Nov 17, 2012 at 12:14

answered Nov 17, 2012 at 11:55

rene

42.6k78 gold badges122 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

Also: as of SQL Server 2005 - you should use sqlcmd instead of osql

Aacini · Accepted Answer · 2012-11-17 20:59:36Z

1

Perhaps you may want to try a small trick that emerged from other question in this forum (that was deleted unfortunately).

You may insert the input for a command directly in the lines below the command and then execute the file NOT as Batch file, but as input por cmd.exe (this is similar to a here document in Linux). For example:

script.TXT:

@echo off
osql -E -S <Server_Name>\<Instance_Name> -d <Server_Name>
BACKUP DATABASE  TO DISK = 'c:\test.bak' WITH INIT,SKIP
exit

Execute previous "script" this way:

cmd < script.TXT

If you perform this test, please report the result...

Antonio

answered Nov 17, 2012 at 20:59

Aacini

67.7k12 gold badges74 silver badges114 bronze badges

2 Comments

kunpr05 Over a year ago

Thanks for reply but it did not help me... because again i am using some batch commands in script.txt what u suggested...

Aacini Over a year ago

You may execute previous script.TXT file inside your normal Batch file, that is, in your Batch file include cmd < script.TXT line and put in script.TXT just the commands for OSQL... This way you may even dinamically create the .TXT file in the Batch file with selected (not fixed) commands! (interesting idea, isn't it?)

Collectives™ on Stack Overflow

Executing series of SQL commands on the command line

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related