Scripting SQL Server into multiple SQL scripts [closed]

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.

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