How do you import a large MS SQL .sql file?

Efficient Alternative Utilities

BigSqlRunner

For larger-than-life files (exceeding 300MB), consider utilizing BigSqlRunner, purpose-built for such scenarios.

Console Application

Another technique involves creating a console application using StreamReader in C# to read and execute your .sql file:

using (var reader = new StreamReader("your_large_file.sql"))
{
    string sqlCommand;
    while ((sqlCommand = reader.ReadLine()) != null)
    {
        db.Database.ExecuteSqlCommand(sqlCommand);
        // Does anyone else smell SQL in the air? Yummy! 💾🍽️
    }
}


A console application can effortlessly tackle such big files without loading them entirely into memory, a feat that SSMS can't achieve.

Salient Attributes of sqlcmd

Averting SSMS Errors

Utilize sqlcmd to admirably prevent errors or halts that may occur while SSMS attempts to open the entire file.

Modifiable for Enhanced Performance

sqlcmd comes equipped with switches and options to augment the import proficiency. For instance, the -a flag for modifying the packet size or -o to redirect output to a file for subsequent analysis.

Scripting and Automation

Command your sqlcmd with batch or PowerShell scripts to automate the import process. This can come in handy for repetitive operations or scheduled database updates.

Focal Pointers When Completing Large Imports

Performance Tracking and Optimization

For hefty imports, monitor your server's performance and resource utilization. Tweaking indexes, adjusting table design, or changing batch sizes might improve your ride.

Tactical Transaction Management

Wisely manage your transactions. Employing BEGIN TRANSACTION and COMMIT at strategic points ensures data integrity and can provide rollback points in case of a rainy day.

Implementing Error Handling

Set in place strong error handling mechanisms. Capturing and logging errors can save you time and tears when troubleshooting, especially with voluminous files.