Running Large Sql Script in Microsoft Sql Server


Running large sql script file, such as database dump of applications already in production is not a trivial endeavor usually because of the size of the sql script files and the time it takes to complete the execution.

sqlcmd Utility

The size of such files usually run into several gigabytes, if not terabytes of data, such files cannot be opened directly in Microsoft Sql Server Management Studio or any readily available text editors because of memory constraint.

Microsoft Sql Server ships with sqlcmd utility, a command line tool for executing Transact-SQL statements as well as executing script files. Sqlcmd can be used to run large script files that Sql Server management studio would not be able to open.

Sqlcmd accepts several command line options to allow easy customization of the process, for the sake of brevity, only the options used in this blog post are explained below.

-S server instance
-d Database name
-I path to the script to be executed
-o path to where the log would be written to
-a packet size

A regular command to execute a script file with sqlcmd would look like this

sqlcmd -S Ayobami-Pc -d Tap -i C:\Backup_production\script.sql -o C:\Db\output.log -a 32767

Where Ayobami-PC is the server instance, C:\Backup_production\script.sql is the path to the script file to be executed, C:\Db\output.log is the path to the log file and 32767 is the packet size.

There might be need to examine the script file and edit the content before executing, conventional text editors would not be able to open the file, there is an open source tool suitable for this, Free Hex Editor, another editor is EmEditor although it is not free, also works fine.

More information on sqlcmd can be found at MSDN.

Share this page on

  0 People Like(s) This Page   Permalink  

 Click  To Like This Page

comments powered by Disqus