MSDeploy dbFullSql provider to create a package works, but fails on deployment

Question

I am currently using MSDeploy to migrate a set of websites from II6 to another machine running IIS7.5. The actual website migration works as expected, but I'm also trying to migrate the associated databases for each website.

So far I am able to successfully move a set of our test sites, but I am getting deployment failures (and EXTREMELY long running times - 1 hour+ with a 400MB db) against our live sites. The failures I am seeing seems to be related to some javascript that is stored the sql database (as data). It looks like when the SMO BatchParser reads in the sql script that it generated it dies on the first line that tries to insert a row with some javascript in the data.

Here is the MSDeploy command I am using:

msdeploy.exe -source:source-manifest.xml -dest:manifest=dest-manifest.xml

source-manifest.xml:
<sitemanifest>
  <dbFullSql path="Data Source=srv_source;Initial Catalog=db;User ID=usr;Password=pwd" Transacted="True" CopyAllLogins="False" CopyAllUsers="False" />
</sitemanifest>

dest-manifest.xml:
<sitemanifest>
  <dbFullSql path="Data Source=srv_dest;Initial Catalog=db;Integrated Security=true" DropDestinationDatabase="True" />
</sitemanifest>

The MSDeploy error is:

Error: An error occurred during execution of the database script. The approximate location of the error was between lines '17009' and '17400' of the script. The verbose log may have more information about the error. The command started with:
INSERT [dbo].[Pages] ([PathID], [PageID], [PageKey

Error:
Error count: 1.

An excerpt from the sql file that is causing the error:

INSERT [dbo].[Pages] ([PathID], [PageID], [PageKey], [DeveloperID], [FrameworkID], [MasterPageID], [FormID], [Label], [Description], [Status], [AdminPosition], [AdminIndent], [Created], [LastEdited], [PageScript], [PageScriptHead], [PageScriptForm], [PageOnLoad], [PageOnUnload], [PageOnSubmit], [HtmlAttributes], [HasThumbnail], [QueryStringVars], [FriendlyPageURL], [CanonicalURL], [MvtStatus], [MvtAutoOptimize], [MvtStartDate], [MvtEndDate], [MvtExperimentID], [DeepLinkOk]) VALUES (72, 580, N'zz', N'zz', N'zzz', N'zzz', NULL, N'zzz', N'zzz', N'U', 0, 0, CAST(0x00009D3D00A0C296 AS DateTime), CAST(0x00009D4C00C99FA8 AS DateTime), NULL, N'  <script language="javascript" type="text/javascript">
function javascript_stuff()
{
    this.$("panel").style.display = "block";
    ...
lots more snipped out code in here  
}

var regex = /^[A-Za-z0-9](([_\.\-]?[a-zA-Z0-9]+)*)@([A-Za-z0-9]+)(([\.\-]?[a-zA-Z0-9]+)*)\.([A-Za-z]{2,})$/;

var jsobj = new jsobj();
</script>', NULL, NULL, NULL, NULL, NULL, 1, NULL, N'welcome', NULL, N'N', N'N', NULL, NULL, 0, 0)
GO

Also if I try to import the package through the IIS UI error:

An error occurred during execution of the database script. The approximate location of the error was between lines '17003' and '17394' of the script. The verbose log may have more information about the error. The command started with : 
INSERT [dbo].[Pages] ([PathID], [PageID], [PageKey

[Expanded Information]
Microsoft.Web.Deployment.DeploymentException: An error occurred during execution of the database script. The approximate location of the error was between lines '17003' and '17394' of the script. The verbose log may have more information about the error. The command started with : 
INSERT [dbo].[Pages] ([PathID], [PageID], [PageKey
 ---> ManagedBatchParser.ParserException
at ManagedBatchParser.Parser.Parse()
at Microsoft.Web.Deployment.SqlBatchParser.ProcessSqlCmdScript(String sqlCmdScript)
at Microsoft.Web.Deployment.SqlScriptToDBProvider.AddHelper(DeploymentObject source, Boolean whatIf)
--- End of inner exception stack trace ---
at Microsoft.Web.Deployment.SqlScriptToDBProvider.AddHelper(DeploymentObject source, Boolean whatIf)
at Microsoft.Web.Deployment.DeploymentObject.AddChild(DeploymentObject source, Int32 position, DeploymentSyncContext syncContext)
at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAddChild(DeploymentObject destParent, DeploymentObject sourceObject, Int32 position)
at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)
at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)
at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)
at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable)
at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)
at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentWellKnownProvider provider, String path, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)
at Microsoft.Web.Deployment.UI.InstallProgressWizardPage.OnWorkerDoWork(Object sender, DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

Anyone have any idea's on how I could get around this? If I run the sql file from the package directly using SQL Management Studio it runs perfectly fine. So unless there is a bug in the SMO BatchParser, there must be some sort of SMO option I could set to get around this issue.

Any help would be greatly appreciated!

Answer 1

Web Deploy team here, we're looking at the issue. Stay tuned!