0

I have a SQL Sever table with 2GB of data. I can create the script for the file. But the script file is not opening. I want to split that script file into multiple script files.

  • 1
    You need to add more detail about what you are attempting to do. Are you getting an error message? Are you attempting to script the data or the table design? What have you tried? – Max Vernon Jan 10 '14 at 6:15
  • 1
    Are you trying to open the script file with Management Studio? – vonPryz Jan 10 '14 at 10:55
  • See if this answer helps you. – Kin Shah Jan 10 '14 at 14:25
4

As @vonPryz said, I have the same feeling that you're trying to open a huge sql file (and SSMS's generated files are not the best in the world, as in they're pretty verbose by default).

As you most probably saw already, SSMS is not properly working with big files. It fails to properly parse them. I would suggest that you don't generate script files for your data, because this isn't MySQL's dump. Use full backups or, if you really want only one table, use bcp exports.

Here's the link to the documentation of the bcp tool. It's easy to use and you shouldn't have any problem to import it on another server.

4

I wrote some time ago a Powershell script that splits a huge SQL script into more manageable chunks. (Reason for this was to export whole database from 2008R2 to a 2008 system in different an organization.)

Smaller files can be executed with Management Studio or by running sqlcmd -i. The script is below. It uses 25000 rows for limit, which in my test case creates sql files about 6 Mb each. Adjust the $MAX to suit your particular needs.

$src = "c:\temp\myBigScript.sql"
$dst = "c:\temp\mySmallScript-{0}.sql"
$reader = new-object IO.StreamReader($src)
$count = 1
$filename = ($dst -f ($count)) 

$row = 0
$MAX = 25000

$sb = new-object Text.StringBuilder
# Read the source row by row into a buffer
while(($line = $reader.ReadLine()) -ne $null){
    $row++
    [void]$sb.Append($line)
    [void]$sb.Append([Environment]::NewLine)

    # Till $MAX rows are read
    if($row -ge $MAX){
        # Write the buffer contents into a file and reset the buffer
        add-content -path $filename -value $($sb.ToString())
        $sb = new-object Text.StringBuilder
        ++$count
        $filename = ($dst -f ($count))
        $row = 0
    }
}
$reader.Close()

As how input the data, a for /l loop in a cmd session works pretty well. Change the loop upper limit to the actual number of files instead of using 8.

for /l %i in (1 1 8) do sqlcmd -S myServer\myInstance -E -i mySmallScript-%i.sql

You may use -Encoding UTF8 in powrshell script add-content line or any Encoding type according to your data, for example

add-content -Encoding UTF8 -path $filename -value $($sb.ToString())

This will fix Unicode characters to be added correctly.

  • This solution is worked for me, especially if there is an error in some part, can open it in Management Studio, Thanks :) – Mhmd Feb 25 at 9:39
  • vonPryz i have a file with 1.850.276 KB but when i run your code, it's stopped at 19° file and the files have ~50MB of size. Why it's stopping? Isnot alerting any error... – Leonardo Bonetti Feb 26 at 19:15
  • @LeonardoBonetti I just tried the script on 2.6 GB file, although $MAX=50000 and it worked without a hick. Have you got enough disk space? Are the smaller files' contents OK? – vonPryz Feb 27 at 6:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.