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.
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