Need help in restoring table using large .sql data file(4GB)

Question

We have run into a situation where we need to restore the data into a SQL table. We have taken backup of data only using MS SQL Server and the .sql file size is 4 GB. Since we are unable to open this file in SQL server, we are using sqlcmd to execute this file but after restoring 140K records it is throwing some syntax error (Unclosed quotation mark after the character strig 'abc_121223354565 and incorrect syntax near 'abc_121223354565 ). The .sql file has total 240K records in it.

Questions:

1. When the backup was provided by SQL server only, why it is throwing syntax error?

2. How to open this large file to fix the syntax error?

if option 2 is not possible, how do we split the large file into smaller chunks so we can identify the exact place where the issue is?

Thanks,

Srikanth

olafhelper · Answer

Questions:1. When the backup was provided by SQL server only, why it is throwing syntax error?2. How to open this large file to fix the syntax error?1. Which tool (+version) have you used to generate the SQL script for data?2. Use a common text editor, which handle such large files like Notepad++&nbsp;Next time use better the BCP tool for export &amp; import data, much performanter, seebcp utility - SQL Server | Microsoft Learn

srikanthd1340 · Answer

olafhelper&nbsp;&nbsp;Thanks for the responding. Please find below for inline&nbsp;comments.1. Which tool (+version) have you used to generate the SQL script for data?&nbsp; &nbsp; &nbsp; &nbsp; Below screenshot for your reference.&nbsp;2. Use a common text editor, which handle such large files like Notepad++&nbsp; &nbsp; we tried notepad++ but we are getting below error. Pls let me know if you have any other ideas.&nbsp;&nbsp;&nbsp;

rodgerkong · Answer

SrikanthD1340&nbsp;Did you mean that SSMS has a feature can backup table to a SQL script? Interesting... Could you show the operation step by step?&nbsp;BTW, 4G+ file can be opened with UtralEdit.

olafhelper · Answer

SrikanthD1340&nbsp;, sorry to say, but if even Notepad++ complains about file size, then you are screwed.&nbsp;rodgerkong&nbsp;, not really a "backup", but the SSMS script wizard has the option to script data as DML commands, seeSSMS Script objects - SQL Server Management Studio (SSMS) | Microsoft Learn&nbsp;=&gt; Note =&gt; "You can script the data for the database when you select&nbsp;Schema and data&nbsp;for the&nbsp;Types of data to script&nbsp;option."&nbsp;But at the end of the day a bad idea, there are much better options available, like BCP.

rodgerkong · Answer

@olahelper got it, thank you

Forum Discussion

Need help in restoring table using large .sql data file(4GB)