I am using the DatabasePublishingWizard to generate a large create script, containing both data and schema. The file it produces is ginormous so opening the script to fix any syntax errors is next to impossible and machines with less than 4gb have a hard time getting it to run! What should I do and how should I do it? Thanks everyone for any help you can provide.
我正在使用DatabasePublishingWizard生成一个包含数据和模式的大型创建脚本。它产生的文件是巨大的,因此打开脚本来修复任何语法错误几乎是不可能的,而且小于4gb的机器很难让它运行!我该怎么办,怎么办呢?感谢大家提供的任何帮助。
5 个解决方案
#1
With the Database Publishing Wizard, you can have all the objects created as separate files instead of one big one. Then you can put all the files in source control and track all changes.
使用数据库发布向导,您可以将所有对象创建为单独的文件而不是一个大文件。然后,您可以将所有文件放在源代码管理中并跟踪所有更改。
My current project uses a script to recreate a database for development. The script deletes all existing objects and then readds them using the following statement for each object file.
我当前的项目使用脚本来重新创建用于开发的数据库。该脚本删除所有现有对象,然后使用以下语句为每个目标文件读取它们。
sqlcmd -S %1 -d THRIVEHQ -E -b -i "../Tables/Schema.sql" if %ERRORLEVEL% NEQ 0 goto errors
sqlcmd -S%1 -d THRIVEHQ -E -b -i“../Tables/Schema.sql”如果%ERRORLEVEL%NEQ 0转到错误
#2
Just want to add to Kevin's comment. Breaking the scripts into separate files THEN write the script to put all the files in order of execution.
只想添加凯文的评论。将脚本分成单独的文件然后编写脚本以按执行顺序放置所有文件。
When dumping a large database that has a lot of inter-dependencies as one large file won't do you much good as in most cases the script won't execute without errors. In my world I use a naming convention that helps me quickly see which, in this case, views are dependent on other views. For example, if I have a view that just produces a dump of data I'd use something like this v_VIEW_NAME_ORIGINATING-TABLE_Dump, then I'd change the suffix to something like _weekly or _weekly_Summary for views that are derived off of the main dump table.
将具有大量相互依赖关系的大型数据库转储为一个大型文件时,对您来说效果不会很好,因为在大多数情况下,脚本不会在没有错误的情况下执行。在我的世界中,我使用命名约定来帮助我快速查看哪些视图依赖于其他视图。例如,如果我有一个只生成数据转储的视图,我会使用类似这样的v_VIEW_NAME_ORIGINATING-TABLE_Dump,那么我会将后缀更改为_weekly或_weekly_Summary,以用于从主转储表派生的视图。
I learned my lesson many years ago and have since follow this naming schema in all my databases.
多年前我吸取了教训,并且在我的所有数据库中都遵循了这个命名模式。
#3
Try DBSourceTools.
http://dbsourcetools.codeplex.com
It will script your entire database to disk, one file per database object.
Using Deployment Targets, you can then re-create any database from file.
It's specifically designed to help developers get their databases under source code control.
试试DBSourceTools。 http://dbsourcetools.codeplex.com它会将整个数据库编写为磁盘,每个数据库对象一个文件。使用部署目标,您可以从文件重新创建任何数据库。它专门用于帮助开发人员在源代码控制下获取数据库。
#4
I would do it in steps.
我会分步进行。
Generate all of your tables and views as 1 script.
将所有表和视图生成为1个脚本。
Generate all of your stored procedures and grants as 1 script.
生成所有存储过程和授权为1个脚本。
Use DTS or SSIS to migrate your data.
使用DTS或SSIS迁移数据。
All of this can be achieved with MS SQL Server Management Studio.
所有这些都可以通过MS SQL Server Management Studio实现。
#5
Off the top of my head, I would say write a script to separate the file into multiple ones with the break occurring after each "GO" statement. You could the write another script to execute each broken out file in order.
在我的脑海中,我会说写一个脚本将文件分成多个,并在每个“GO”语句后发生中断。您可以编写另一个脚本来按顺序执行每个已分解的文件。
#1
With the Database Publishing Wizard, you can have all the objects created as separate files instead of one big one. Then you can put all the files in source control and track all changes.
使用数据库发布向导,您可以将所有对象创建为单独的文件而不是一个大文件。然后,您可以将所有文件放在源代码管理中并跟踪所有更改。
My current project uses a script to recreate a database for development. The script deletes all existing objects and then readds them using the following statement for each object file.
我当前的项目使用脚本来重新创建用于开发的数据库。该脚本删除所有现有对象,然后使用以下语句为每个目标文件读取它们。
sqlcmd -S %1 -d THRIVEHQ -E -b -i "../Tables/Schema.sql" if %ERRORLEVEL% NEQ 0 goto errors
sqlcmd -S%1 -d THRIVEHQ -E -b -i“../Tables/Schema.sql”如果%ERRORLEVEL%NEQ 0转到错误
#2
Just want to add to Kevin's comment. Breaking the scripts into separate files THEN write the script to put all the files in order of execution.
只想添加凯文的评论。将脚本分成单独的文件然后编写脚本以按执行顺序放置所有文件。
When dumping a large database that has a lot of inter-dependencies as one large file won't do you much good as in most cases the script won't execute without errors. In my world I use a naming convention that helps me quickly see which, in this case, views are dependent on other views. For example, if I have a view that just produces a dump of data I'd use something like this v_VIEW_NAME_ORIGINATING-TABLE_Dump, then I'd change the suffix to something like _weekly or _weekly_Summary for views that are derived off of the main dump table.
将具有大量相互依赖关系的大型数据库转储为一个大型文件时,对您来说效果不会很好,因为在大多数情况下,脚本不会在没有错误的情况下执行。在我的世界中,我使用命名约定来帮助我快速查看哪些视图依赖于其他视图。例如,如果我有一个只生成数据转储的视图,我会使用类似这样的v_VIEW_NAME_ORIGINATING-TABLE_Dump,那么我会将后缀更改为_weekly或_weekly_Summary,以用于从主转储表派生的视图。
I learned my lesson many years ago and have since follow this naming schema in all my databases.
多年前我吸取了教训,并且在我的所有数据库中都遵循了这个命名模式。
#3
Try DBSourceTools.
http://dbsourcetools.codeplex.com
It will script your entire database to disk, one file per database object.
Using Deployment Targets, you can then re-create any database from file.
It's specifically designed to help developers get their databases under source code control.
试试DBSourceTools。 http://dbsourcetools.codeplex.com它会将整个数据库编写为磁盘,每个数据库对象一个文件。使用部署目标,您可以从文件重新创建任何数据库。它专门用于帮助开发人员在源代码控制下获取数据库。
#4
I would do it in steps.
我会分步进行。
Generate all of your tables and views as 1 script.
将所有表和视图生成为1个脚本。
Generate all of your stored procedures and grants as 1 script.
生成所有存储过程和授权为1个脚本。
Use DTS or SSIS to migrate your data.
使用DTS或SSIS迁移数据。
All of this can be achieved with MS SQL Server Management Studio.
所有这些都可以通过MS SQL Server Management Studio实现。
#5
Off the top of my head, I would say write a script to separate the file into multiple ones with the break occurring after each "GO" statement. You could the write another script to execute each broken out file in order.
在我的脑海中,我会说写一个脚本将文件分成多个,并在每个“GO”语句后发生中断。您可以编写另一个脚本来按顺序执行每个已分解的文件。