How do I split a .sql script into multiple files?

Question

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.

Answer 1

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.

Answer 2

I would do it in steps.

Generate all of your tables and views as 1 script.

Generate all of your stored procedures and grants as 1 script.

Use DTS or SSIS to migrate your data.

All of this can be achieved with MS SQL Server Management Studio.

Answer 3

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

Answer 4

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.

I learned my lesson many years ago and have since follow this naming schema in all my databases.

Answer 5

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.