How to execute large SQL file in SQL Server

I’m working in one project where I need to execute large SQL script in SQL server, file size near about 2.5 GB. While I’m trying to open that script file in SSMS it doesn’t allow me to open that file it throws an error File is too large to open.

I have tried to open script file with another editors i.e. notepad, notepad ++ as well but getting same error. We can open large file in Glogg editor  with any file size WordPad also allow same, but it will take some time.

Now question is How we can execute large SQL script? It is very sample

Open Common Promat in Admin mode and type following command and Press enter key.

sqlcmd -S \ -U -P -d -i

Example:

sqlcmd -S MyComputer-PC\MSSQLSERVER16 -U sa -P teamwork@1 -i "C:/Users/Amit /Downloads/dbScripts/script.sql"

Output:

SQLCMD is a viable option, but for such a huge script, I have never tested to confirm if it actually is capable of handling it or not. Also, I recommend to have -o logfile.txt as if something goes wrong, you will atleast have a log file to see what went wrong.

Below is what I would recommend :

I have a 830mb .sql file which is a scripted DB.

I guess, you have scripted your entire database along with schema and data and hence this mammoth size.

I wish to execute this SQL so it will create and insert data into a database on a hosted server. I cannot get it to open in SQL Server Management Studio or EMS SQL Manager.

You wont be able to run it from SSMS or any GUI tools.

How can I execute it?

Never script out the entire database with data and schema. Instead -

1. Script out the database SCHEMA_ONLY and recreate an empty database on the destination server.
2. Use BCP OUT and BULK INSERT to insert data.

Below is the script that will help you with Part 2.

/************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another*********************************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)                         



--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' 
        +  QUOTENAME(name) 
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','') 
        +'.dat'' 
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',  
        TABLOCK
        )'  + char(10) 
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go' 
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema 
order by schema_name(schema_id)

How to import large SQL file in SQL Server?

How do I run a SQL file in SSMS?

How to run big SQL script in MySQL?

How to import .SQL file in SQL Server using command prompt?