Others · February 16, 2015

Running Large Sql Script in Microsoft Sql Server

TL;DR

Use sqlcmd, the command-line tool that ships with SQL Server, to run script files too big to open in Management Studio. Pass the server, database, script path, log file and packet size, and use a hex editor to inspect the file.

Introduction

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.

Frequently asked questions

How do I run a multi-gigabyte SQL script file?

Use sqlcmd. The file is too large to open in SQL Server Management Studio or a normal text editor because of memory constraints, but sqlcmd executes script files directly from the command line.

What does a sqlcmd command for a large script look like?

sqlcmd -S Ayobami-Pc -d Tap -i C:\Backup_production\script.sql -o C:\Db\output.log -a 32767, where -S is the server instance, -d the database, -i the script file, -o the log file and -a the packet size.

How can I view or edit a script that large?

Conventional text editors cannot open it. The post suggests Free Hex Editor, which is open source, or EmEditor, which works well but is not free.