Post reply

Execute sql statements using batch file

  • March 19, 2009 at 2:00 pm

    #127178

    Hello guys,

    I have a server which has sql express installed. It only has database engine and does not have sql agent. I need to backup all the databases for this instance everyweek and cannot automate it as there is no agent installed. Is there a way of executing .sql file from batch file. I can then schedule the batch file from windows scheduling agent. If possible can you also tell me the syntax as I tried to google it but could not get the syntax anywhere.

    thanks a tonn,

    Nikhil

  • March 19, 2009 at 2:10 pm

    #963109

    Does sql express have the sqlcmd.exe installed? If so, you can use that.

  • March 19, 2009 at 2:34 pm

    #963138

    Thanks for the reply,

    SQLCMD is not installed

  • March 19, 2009 at 2:54 pm

    #963164

    I see this article for sql express 2008, so it must be available.

    http://msdn.microsoft.com/en-us/library/ms165702.aspx

    Looks like it's in the sql express toolkit:

    http://msdn.microsoft.com/en-us/library/ms365251.aspx

    dl: http://www.microsoft.com/downloads/details.aspx?FamilyID=e8ad606a-0960-4efd-8bd7-b21370c7be2b&DisplayLang=en

  • March 19, 2009 at 3:11 pm

    #963173

    Thanks for the reply,

    I dont think I can install anything on this server, so that is the reason I used to take manual backups instead. I read stuff which says it is possible but could not find out how? Do you know how to execute SQL script using batch file.

    Thanks a ton,

    Nikhil

  • March 19, 2009 at 3:51 pm

    #963201

    You just need to install the sqlcmd.exe whereever you want to run the backups from.

    If you open cmd and type sqlcmd /? (of course you need to be in the bin directory where sqlcmd is installed or have that in your path environment variable somewhere)

    You would create the backup script and save that to backupscript.sql file

    Create your .bat file something like this:

    sqlcmd -s Myserver -UMyUser -PMyPassword -i backupscript.sql -o outputfile.txt

    You could also use integrated authentication by using the -E switch, but remember, it will run under the context of whatever account you use to schedule it. The account will also need backup permissions - db_backupoperator in the desired databases.

    Hope this helps!

  • March 19, 2009 at 10:23 pm

    #963303

    you can use osql utility for running your script as a batch..

    Shekhar..:)

    Thanks ,

    Shekhar

  • March 24, 2009 at 9:33 am

    #965325

    Thanks a tonn dude,,

    for all the help.. it worked....

  • March 24, 2009 at 10:28 am

    #965382

    osql uses odbc

    sqlcmd is the new replacement which uses ole db. I would assume it would be the wisest choice to use - it would probably give your solution the longest life.

    "The osql utility uses the ODBC database application programming interface (API). It is a replacement for the isql command prompt utility based on the DB-Library API. Both utilities are provided with Microsoft SQL Server™ 2000. The DB-Library API remains at a SQL Server 6.5 level; therefore, applications that depend on DB-Library, such as isql, do not support some SQL Server 2000 features. For example, isql cannot access columns defined with the ntext data type and truncates any char, varchar, nchar, or nvarchar columns longer than 255 bytes. It also cannot retrieve results as XML documents. Except for these limitations in isql, both osql and isql support the same features. For more information about features not supported by isql, see Connecting Early Version Clients to SQL Server 2000."

  • March 24, 2009 at 2:08 pm

    #965588

    Thanks for tht information.

    Nikhil

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply