6

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

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.

How can I execute it?

migrated from stackoverflow.com Dec 4 '13 at 19:01

This question came from our site for professional and enthusiast programmers.

8

Depends on db flavor, most provide command line tools to load sql files. Looks like you may be using ms sql, reference here http://technet.microsoft.com/en-us/library/ms165702(v=sql.105).aspx, more specific http://technet.microsoft.com/en-us/library/ms170572.aspx

sqlcmd -S myServer\instanceName -i C:\myScript.sql
  • It's a shared hosting enviroment so I don't have access to the server, only through SQL management studio, also can't browse to the sql file's location in the file area – PeteTheGreek Dec 4 '13 at 15:38
  • sqlcmd allows you to connect to remote server and load file from your local box – kostyan Dec 4 '13 at 15:39
  • 1
    You may run into the batch size limit (64K * network packet size) as documented at technet.microsoft.com/en-us/library/ms143432.aspx so you might still need to split that large a file into smaller chunks. – David Spillett Dec 5 '13 at 11:57
7

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)
3

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

The syntax is

osql -H <computer_name> -S <instance_name[\instance_name]> -U <username> -P <password> -i <full path to the script> 
  1. Start the Command Prompt
  2. Go to the Tools\Binn folder
  3. Run code such as

    osql -H TestServer -S Fujitsu\SQL2012 -i D:\Test\sync.sql -U sa -P sqladmin

Note: this feature will be removed in future SQL Server versions

You can fine all arguments available for the utility here: osql Utility

  • As you pointed out osql will be deprecated and should not be used. SQLCMD is the way to go. Thought I would not even recommend to use it for running such large scripts. The method that I mentioned is more viable option. – Kin Dec 6 '13 at 18:21
1

There are tools for executing large script files. I just found this one: http://bigsqlrunner.codeplex.com/ there are probably dozens of similar applications. I have not used this one yet, but if I were to write one it would read through the huge script, and parse out smaller SQL scripts using 'GO' as the separator, executing one at a time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy