I have a big SQL file that does not fit into memory and needs to be executed against Microsoft SQL Server 2008. It seems that the sqlcmd.exe tool always loads it into memory first which is impossible in this case. Any ideas?
我有一个大的SQL文件,不适合内存,需要针对Microsoft SQL Server 2008执行。似乎sqlcmd.exe工具始终首先将其加载到内存中,这在这种情况下是不可能的。有任何想法吗?
Unfortunately, I can't split the script because it is generated by Red Gate's excellent SQL Data Compare. The entire script is one big transaction and I want to leave it that way. I had never thought that having a gigantic script is unusual because having a lot of data is common in the database world. The script is 3gb in size.
不幸的是,我无法拆分脚本,因为它是由Red Gate出色的SQL数据比较生成的。整个脚本是一个大事务,我想这样离开。我从未想过拥有一个巨大的脚本是不寻常的,因为在数据库世界中拥有大量数据是很常见的。该脚本的大小为3GB。
8 个解决方案
#1
3
RedGate's SQL Compare has an option to execute the statements directly, instead of generating a SQL script and executing it later. Is there a reason this wouldn't work - in other words, is there a reason you require a SQL script and can't use the application's "synchronize now" functionality?
RedGate的SQL Compare有一个直接执行语句的选项,而不是生成SQL脚本并在以后执行它。有没有理由这不起作用 - 换句话说,是否有理由需要SQL脚本并且无法使用应用程序的“立即同步”功能?
#2
1
What/who created the SQL script? Get whatever created the file to split the script up into logic chunks, by either transaction or statement (depending on how the file is structured). If the source can't do this, then whip up a script to split the file up logically.
什么/谁创建了SQL脚本?获取创建文件的任何内容,通过事务或语句将脚本拆分为逻辑块(取决于文件的结构)。如果源无法执行此操作,则启动脚本以逻辑方式拆分文件。
#3
1
If it is that big, the script is either too complex or is repetitive. In either case, as others have suggested, the only sensible thing is to break it down into manageable chunks.
如果它很大,脚本要么太复杂要么重复。在任何一种情况下,正如其他人所建议的那样,唯一明智的做法是将其分解为可管理的块。
Is this a one-off exercise or a regular event?
这是一次性演习还是常规演出?
#4
1
I've had this problem before where the script had an enormous XML String that was being used with OpenXML. The actual SQL was rather minimal, updating some values in a table.
在脚本有一个巨大的XML字符串与OpenXML一起使用之前,我遇到过这个问题。实际的SQL相当小,更新了表中的一些值。
I ended up inserting the data (in chunks) into a temporary table until all the info that was in the XML was stored. Then I ran my update statement.
我最终将数据(以块为单位)插入到临时表中,直到存储了XML中的所有信息。然后我运行了我的更新声明。
Added later after more data got posted:
在发布更多数据后添加:
You may want to select large chunks in the tool and have SQL Data compare generate the scripts in chunks. That way you get the transactions. You can select large sections by simply highlighting a range and hitting the space bar.
您可能希望在工具中选择大块,并让SQL Data compare以块的形式生成脚本。这样你就得到了交易。您只需突出显示一个范围并点击空格键即可选择较大的部分。
#5
1
I ran into this problem a few months ago. I generate sync scripts with SQLDataCompare on a weekly and monthly basis for several of our catalog databases and they are routinely larger than 500MB. My solution was writing a VBscript that chops the update script into 50 to 1000 command batches. The problem with this approach is losing the ability to roll back all changes if something breaks halfway into your database update.
几个月前我遇到了这个问题。我为每个目录数据库每周和每月生成带有SQLDataCompare的同步脚本,它们通常大于500MB。我的解决方案是编写一个VBscript,将更新脚本分成50到1000个命令批处理。如果某些内容在数据库更新中途中断,则此方法的问题是无法回滚所有更改。
#6
0
1-800-redgate-support.....
or
- break up transaction script into smaller files
- set database in single user mode
- fullbackup of database
- run each smaller script file; if there is a failure: restore backup, fix script, try again
- back out of single user mode, all done
将事务脚本分解为较小的文件
在单用户模式下设置数据库
数据库的完整备份
运行每个较小的脚本文件;如果出现故障:恢复备份,修复脚本,再试一次
退出单用户模式,全部完成
#7
0
USE sqlcmd command
使用sqlcmd命令
Example sqlcmd -S myServer\instanceName -i C:\myScript.sql
示例sqlcmd -S myServer \ instanceName -i C:\ myScript.sql
#8
0
The way I understand it, SSMS is 32-bit so it can't load a script over 1.5-2 GB. You can run the script in SQLCMD.exe, but then you may run into problems because of the transaction size -- SqlCmd will keep a whole transaction in memory. So what you can do then in SQL Data Compare is go into the options and use "split transactions", which may help.
我理解它的方式,SSMS是32位,因此它无法加载超过1.5-2 GB的脚本。您可以在SQLCMD.exe中运行该脚本,但由于事务大小,您可能会遇到问题 - SqlCmd会将整个事务保留在内存中。那么你在SQL数据比较中可以做的就是进入选项并使用“拆分事务”,这可能有所帮助。
SQL Data Compare will also do partial updates to BLOBs, which will solve the "enormous BLOB" issue.
SQL Data Compare还将对BLOB进行部分更新,这将解决“巨大的BLOB”问题。
This of course if based on the latest version of Data Compare. Some versions may not have these features.
这当然是基于最新版本的Data Compare。某些版本可能没有这些功能。
Another option may be to use SQL Compare to create a schema script to a folder, then use SDC to sync the data into that folder. Then you have a file for each table rather than one massive file.
另一个选项可能是使用SQL Compare为文件夹创建架构脚本,然后使用SDC将数据同步到该文件夹。然后你有一个文件为每个表而不是一个大型文件。
Hope this helps.
希望这可以帮助。
#1
3
RedGate's SQL Compare has an option to execute the statements directly, instead of generating a SQL script and executing it later. Is there a reason this wouldn't work - in other words, is there a reason you require a SQL script and can't use the application's "synchronize now" functionality?
RedGate的SQL Compare有一个直接执行语句的选项,而不是生成SQL脚本并在以后执行它。有没有理由这不起作用 - 换句话说,是否有理由需要SQL脚本并且无法使用应用程序的“立即同步”功能?
#2
1
What/who created the SQL script? Get whatever created the file to split the script up into logic chunks, by either transaction or statement (depending on how the file is structured). If the source can't do this, then whip up a script to split the file up logically.
什么/谁创建了SQL脚本?获取创建文件的任何内容,通过事务或语句将脚本拆分为逻辑块(取决于文件的结构)。如果源无法执行此操作,则启动脚本以逻辑方式拆分文件。
#3
1
If it is that big, the script is either too complex or is repetitive. In either case, as others have suggested, the only sensible thing is to break it down into manageable chunks.
如果它很大,脚本要么太复杂要么重复。在任何一种情况下,正如其他人所建议的那样,唯一明智的做法是将其分解为可管理的块。
Is this a one-off exercise or a regular event?
这是一次性演习还是常规演出?
#4
1
I've had this problem before where the script had an enormous XML String that was being used with OpenXML. The actual SQL was rather minimal, updating some values in a table.
在脚本有一个巨大的XML字符串与OpenXML一起使用之前,我遇到过这个问题。实际的SQL相当小,更新了表中的一些值。
I ended up inserting the data (in chunks) into a temporary table until all the info that was in the XML was stored. Then I ran my update statement.
我最终将数据(以块为单位)插入到临时表中,直到存储了XML中的所有信息。然后我运行了我的更新声明。
Added later after more data got posted:
在发布更多数据后添加:
You may want to select large chunks in the tool and have SQL Data compare generate the scripts in chunks. That way you get the transactions. You can select large sections by simply highlighting a range and hitting the space bar.
您可能希望在工具中选择大块,并让SQL Data compare以块的形式生成脚本。这样你就得到了交易。您只需突出显示一个范围并点击空格键即可选择较大的部分。
#5
1
I ran into this problem a few months ago. I generate sync scripts with SQLDataCompare on a weekly and monthly basis for several of our catalog databases and they are routinely larger than 500MB. My solution was writing a VBscript that chops the update script into 50 to 1000 command batches. The problem with this approach is losing the ability to roll back all changes if something breaks halfway into your database update.
几个月前我遇到了这个问题。我为每个目录数据库每周和每月生成带有SQLDataCompare的同步脚本,它们通常大于500MB。我的解决方案是编写一个VBscript,将更新脚本分成50到1000个命令批处理。如果某些内容在数据库更新中途中断,则此方法的问题是无法回滚所有更改。
#6
0
1-800-redgate-support.....
or
- break up transaction script into smaller files
- set database in single user mode
- fullbackup of database
- run each smaller script file; if there is a failure: restore backup, fix script, try again
- back out of single user mode, all done
将事务脚本分解为较小的文件
在单用户模式下设置数据库
数据库的完整备份
运行每个较小的脚本文件;如果出现故障:恢复备份,修复脚本,再试一次
退出单用户模式,全部完成
#7
0
USE sqlcmd command
使用sqlcmd命令
Example sqlcmd -S myServer\instanceName -i C:\myScript.sql
示例sqlcmd -S myServer \ instanceName -i C:\ myScript.sql
#8
0
The way I understand it, SSMS is 32-bit so it can't load a script over 1.5-2 GB. You can run the script in SQLCMD.exe, but then you may run into problems because of the transaction size -- SqlCmd will keep a whole transaction in memory. So what you can do then in SQL Data Compare is go into the options and use "split transactions", which may help.
我理解它的方式,SSMS是32位,因此它无法加载超过1.5-2 GB的脚本。您可以在SQLCMD.exe中运行该脚本,但由于事务大小,您可能会遇到问题 - SqlCmd会将整个事务保留在内存中。那么你在SQL数据比较中可以做的就是进入选项并使用“拆分事务”,这可能有所帮助。
SQL Data Compare will also do partial updates to BLOBs, which will solve the "enormous BLOB" issue.
SQL Data Compare还将对BLOB进行部分更新,这将解决“巨大的BLOB”问题。
This of course if based on the latest version of Data Compare. Some versions may not have these features.
这当然是基于最新版本的Data Compare。某些版本可能没有这些功能。
Another option may be to use SQL Compare to create a schema script to a folder, then use SDC to sync the data into that folder. Then you have a file for each table rather than one massive file.
另一个选项可能是使用SQL Compare为文件夹创建架构脚本,然后使用SDC将数据同步到该文件夹。然后你有一个文件为每个表而不是一个大型文件。
Hope this helps.
希望这可以帮助。