How to open a huge .sql file
SQL editor can open a file upto 500 mb without very very good specs, this seems to be going something wrong, if you want to insert the data from one database to another, try to use import/export wizard, SSIS package or command line utility creating a script of table and then insert is not a good approach.
How to open and work with a very large .SQL file that was generated in a dump?
2 things that might be helpful:
- Use
pv
to see how much of the .sql file has already been read. This can give you a progress bar which at least tells you it's not suck. - Log into MySQL and use
SHOW PROCESSLIST
to see what MySQL currently is executing. If it's still running, just let it run to completion.
If turned on, it might really help to turn off the binlog for the duration of the restore. Another thing that may or may not be helpful... if you have the choice, try to use the fastest disks available. You may have this kind of option if you're running on hosters like Amazon. You're going to really feel the pain if you're (for example) doing this on a standard EC2 host.
How do you import a large MS SQL .sql file?
From the command prompt, start up sqlcmd
:
sqlcmd -S <server> -i C:\<your file here>.sql
Just replace <server>
with the location of your SQL box and <your file here>
with the name of your script. Don't forget, if you're using a SQL instance the syntax is:
sqlcmd -S <server>\instance.
Here is the list of all arguments you can pass sqlcmd:
Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f | i:[,o:]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, environment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]
Syntax error in huge SQL file: How to find it?
if you have big file with small batches you will launch SQL Server Profiler (SSMS Menu Tools) choose Events about Errors and Profiler catch text of batch with error. And you can find bad batch.
Importing larger SQL files into MySQL
You can import large files this command line way:
mysql -h yourhostname -u username -p databasename < yoursqlfile.sql
Related Topics
Hive Select Data into an Array of Structs
What Would Be a SQL Query to Remove \N\R from the Text
How to Tell If a Value Is Not Numeric in Oracle
Error Code 1292 Incorrect Date Value MySQL
Why Postgresql Does Not Like Uppercase Table Names
How to Split a Record Based on Dates in SQL Server
Find Out Count of Employees Joined in January Month
Removing Null Value in SQL Join and Union Operators in SQL
How to Import a Large (14 Gb) MySQL Dump File into a New MySQL Database
How to Select the Top 3 Salaries of the Department
Selecting Only One Duplicate Record
Mysql Function to Find the Number of Working Days Between Two Dates
Selecting First and Last Values in a Group
How to Get the Month and Day With Leading 0'S in SQL (E.G. 9 => 09)
Could Not Load File or Assembly Error in .Net Standard 2.0 Class Library