Scripting whole DB, then running script on a new DB fails to create objects because of dependencies



I was trying to create a script to duplicate the DB schema on different DB.

The steps I followed were:
1) Created a source database for the exisitng database to be scripted
2) Created a new database that will be the target DB
3) Compare both databases and generated the script
4) Copied the script and ran it on the new DB to create the objects. It ran til the end, but there were many errors shown.

I researched the issues and found the problems were caused because of dependencies. ie. A view depending on another view failed because the dependency was not there when the script tried to create the main view. The dependency view was in the script but lines below the main one.

The current version I am using is:

Hope this helps.


rozentalsn wrote Apr 21, 2011 at 2:16 AM

Hi Carloso,
You need to create a "Deployment Target" to replicate your source database to your target database, NOT the schema compare.
Have a look at the Quick Start Guide : http://dbsourcetools.codeplex.com/wikipage?title=Quick%20Start%20Guide&referringTitle=Home
By using this method, all dependencies will be correctly picked up and processed in the correct order.
The schema compare functionality has been included through another Codeplex project ( DBDiff : http://opendbiff.codeplex.com ) , and is only there as a useful toolset.
DBSourceTools has been built to replicate databases throught the "Deployment Target" process.
Let me know how you go.
  • Nathan

carloslo wrote Apr 26, 2011 at 10:54 PM

Hi Nathan,

I created a target database for an empty database, but had issues with foreign constraints. See below sample message:
sqlcmd -S MSSSVR02 -d RGBC_Test -E -i "RGBC\Tables\tblServicesCTO.sql"
Msg 1767, Level 16, State 1, Server MSSSVR02, Line 1
Foreign key 'FK_tblServicesCTO_tblServices' references invalid table 'dbo.tblServices'.
Msg 1750, Level 16, State 1, Server MSSSVR02, Line 1

It tried to create the constraints for tables that were there yet.

and another type of error ..
sqlcmd -S MSSSVR02 -d RGBC_Test -E -i "RGBC\Procs\sp_AgreementReminder_Update.sql"
The module 'sp_AgreementReminder_Update' depends on the missing object 'dbo.sp_tblAgreementCharges_LeaseReminder_Update'. The module will still be created; however, it cannot run successfully until the object exists.

This one is not critical as the object will still be created.

Seems all other dependencies were applied correctly.

I used latest version.

rozentalsn wrote Jun 2, 2011 at 4:43 AM

Hi Carloso,
In regards to your foreign key constraint, you may find that you have circular references. Create a patch to apply these constraints once all tables have been correctly created.