How to Execute a very large SQL file?

If you have a very large Sql file, you probably won’t be able to restore it directly.

We can use sqlcmd to perform this operation. So what is this sqlcmd?

The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. Sqlcmd is a sustainable option, but for such a huge script.

I- sqlcmd Script

-S : [protocol:]server[instance_name][,port]
-d : Db_name
-U : Login_id
-P : Password
-i : Input_file(Sql file location)

See here for more syntax about sqlcmd.

Besides sqlcmd, SQL Server provides the osql utility.The same as sqlcmd, osql is stored in the SQL Server’s installation Tools\Binn subfolder, and is used from the Command Prompt. OSQL is a command line tool that allows you to issue commands to Microsoft SQL Server.

The syntax is:

osql Script

-S : <sq-server-name> - the name of the Sql Server, including instance.
-d : The name of the database on which the operation is to be performed.
-U : The SQL Server user account
-P : Password
-i : Input_file(Sql file location) 

See here for more syntax about osql.

If you want to directly restore the server’s corresponding master database. We use the following syntax:

II- sqlcmd Script

Note: Processing time varies according to file size.

We can also use the query field in sqlcmd mode in Management Studio. To do this, you must follow the procedures below:

1. In Object Explorer, right-click the server, and then click New Query, to open a new Database Engine Query Editor window.
2. On the Query menu, click SQLCMD Mode.

Query Menu for SQLCMD Mode

Difference between sqlcmd and osql:

sqlcmd: The newest, command-line interface to SQL Server.

osql : The elderly, ODBC( Open Database Connectivity)-based way of command-line communication with SQL Server.

I Briefly mentioned. What is sqlcmd, how to use it? We performed the same operations in osql in the same way.

Hopefully, it benefits you..