We are excited to announce that the IIS.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

Problem with dbFullSql [Answered]RSS

15 replies

Last post Feb 22, 2012 03:40 PM by jeremyh

  • Problem with dbFullSql

    Feb 17, 2012 07:47 AM|shanedhorlock|LINK

    I am getting an error trying to publish the database element of my DNN website to a new hosting service. All files copy ok, but database script generated / used by dbFullSql function of Web Deploy contains references to the old hosting service user id.

    Where do I find the script that was generated / used? How can I change it and execute it correctly?

    Webmatrix publish log extract follows: 

    Adding child sqlScript (MSDeploy.Continous/dbFullSql[@path='data source=ASPHOST75\SQL2008,777;initial catalog=Shanedhorloc_WLHG;user id=Shanedhorloc_host']/sqlScript).
    11:08:49: Opening SQL Connection with connection string 'data source=ASPHOST75\SQL2008,777;initial catalog=Shanedhorloc_WLHG;user id=Shanedhorloc_host;pooling=False'. The 'transacted' setting for this connection is 'True'.
    11:11:55: Unable to publish the database. For more information, see "http://go.microsoft.com/fwlink/?LinkId=205387"
    11:11:55: Unable to publish. Unable to publish the database. For more information, see "http://go.microsoft.com/fwlink/?LinkId=205387"
    11:11:55: Error detail:
    11:11:55: An error occurred during execution of the database script. The error occurred between the following lines of the script: "3856" and "3858". The verbose log might have more information about the error. The command started with the following:
    11:11:55: "CREATE SCHEMA [prvcsdvy_host] AUTHORIZATION [prvcs"

  • Re: Problem with dbFullSql

    Feb 17, 2012 08:11 AM|jeremyh|LINK

    Can we back up a little please and are you using v1 or v2 Beta WebMatrix?

    Also the files you are trying to redeploy where have they come from?

    Maybe an ftp from old installed site and what about the db?
    or
    A working clean install on your file system?

    Regards
    Jeremy
    If this has helped Please: Don't forget to click "Mark as Answer" on the post that helped you.
    That way future readers will know which post solved your issue.
  • Re: Problem with dbFullSql

    Feb 17, 2012 08:51 AM|shanedhorlock|LINK

    Hi Jeremyh,

    V2 Beta version of Webmatrix

    Website is DNN community edition v 6.1.3 based.

    Old hosting site is Netcetera.co.uk and contains the DNN based customised website that I have developed.

    Local PC has copy of old hosting site having synched using Webmatrix.

    New hosting site is hostforlife.eu - webmatrix publish function (having set up new parameters, checked connection and done the tests for database / html etc) published correctly all of the files - but failed at the database publication stage.

    I had thought about doing a new clean installation, but not sure how to do that given all the work involved in customising DNN (have asked for guidance on that from DNN forums).

    Regards, Shane

  • Re: Problem with dbFullSql

    Feb 17, 2012 09:17 AM|jeremyh|LINK

    Many thanks for the details.

    I would like to just try this out for myself as some of the issues around the database sync puzzle me
    in this case but I cant build up a test until later on toda,y then I will try and post back asap.

    Regards
    Jeremy
    If this has helped Please: Don't forget to click "Mark as Answer" on the post that helped you.
    That way future readers will know which post solved your issue.
  • Re: Problem with dbFullSql

    Feb 17, 2012 09:27 AM|shanedhorlock|LINK

    Thanks Jeremy, I apreciate the help and await what you find out. I must confess to having lost the rest of my hair trying to understand what is happening here :)

    Regards, Shane

  • Re: Problem with dbFullSql

    Feb 17, 2012 10:11 AM|jeremyh|LINK

    Shane,

    Sorry one more question please,

    Did you first install dnn with the host's control panel or local machine then deploy?

    Thanks  

    Regards
    Jeremy
    If this has helped Please: Don't forget to click "Mark as Answer" on the post that helped you.
    That way future readers will know which post solved your issue.
  • Re: Problem with dbFullSql

    Feb 17, 2012 10:35 AM|shanedhorlock|LINK

    Hi Jeremyh,

    I installed / deployed DNN 6.1.2 to my pc.

    Began developing my website.

    Later obtained hosting service, create an empty database and published from my pc to that service using Webmatrix (i think initially was version 1)

    Upgraded to DNN 6.1.3 on my PC at beginning of Feb when it was released and deployed to hosting service.

    Upgraded to Webmatrix 2 at some point - not sure of date / position in sequence of other things but think it was this month.

    Then used synch to local before publishing to new hosting service.

     Regards, Shane

  • Re: Problem with dbFullSql

    Feb 18, 2012 10:00 AM|jeremyh|LINK

    Sorry to not have come back to you before, had a few problems to solve first.

    I have another question for you if i may.

    Did you do a Sync Remote to Local to get your copy of the hosted site
    locally or did you just get a copy with ftp?

    If you did do a Sync Remote to local, did the database sync?

    Regards
    Jeremy
    If this has helped Please: Don't forget to click "Mark as Answer" on the post that helped you.
    That way future readers will know which post solved your issue.
  • Re: Problem with dbFullSql

    Feb 18, 2012 12:41 PM|shanedhorlock|LINK

    I did the sync remote to local function. 

    I have today downloaded Microsoft SQL Server Database Publishing Wizard. I have used it to create a script from my local PC copy of the website database - I presume it is the same technology base as dbFullSql - or will at least produce comparable results - though having found the culprit statements - I don't know what steps to take next. The script produced has the following statements (from three different sections):

    GO
    /****** Object:  Schema [prvcsdvy_host]    Script Date: 02/18/2012 14:25:57 ******/
    IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'prvcsdvy_host')
    DROP SCHEMA [prvcsdvy_host]
    GO

    ......

    GO
    /****** Object:  Role [prvcsdvy_host]    Script Date: 02/18/2012 14:25:56 ******/
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'prvcsdvy_host')
    BEGIN
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'prvcsdvy_host' AND type = 'R')
    CREATE ROLE [prvcsdvy_host]

    END

    ........

    GO
    /****** Object:  Schema [prvcsdvy_host]    Script Date: 02/18/2012 14:25:57 ******/
    IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'prvcsdvy_host')
    EXEC sys.sp_executesql N'CREATE SCHEMA [prvcsdvy_host] AUTHORIZATION [prvcsdvy_host]'
    GO

  • Re: Problem with dbFullSql

    Feb 19, 2012 04:29 AM|jeremyh|LINK

    Shane,

    I am still looking at if you can use WebMatrix fully todo this move,
    but if you just want to move on now this is what I would do and have done in the past

    1) Backup the Database using the control panel tools
    at Netcetera and download the zip file over http.

    2) Ftp a copy of the server files to your local machine
    from Netcetera.

    3) Setup a new blank database with the same name if you can at new host.

    4) Unzip and Upload the backup database to a folder on the new host.

    5) Run a DB restore from the control panel of the new host.

    6) Update the web.config with the new connectionstring and ftp
    the files to the new host.

    However with this method you need to watch the machinename
    and appname are the same or you can run into problem with membership.

    Also you can sometimes run into issues if the SQL server's are
    not quite the same, more about this at
    http://www.dotnetnuke.com/Resources/Blogs/EntryId/1293/HOW-TO-Move-a-DotNetNuke-installation.aspx
    If you want to go this route then the tools shown can be download on a 30 day trail I think.

    Also if not using control panel tools todo the restore you can follow
    this but its a little old
    http://www.mitchelsellers.com/blogs/2007/03/11/restoring-or-moving-a-dnn-installation-(from-backup-files).aspx

    Should you have any problems with these steps just post them here
    or ask support at the new host.

    Good luck

    Regards
    Jeremy
    If this has helped Please: Don't forget to click "Mark as Answer" on the post that helped you.
    That way future readers will know which post solved your issue.
  • Re: Problem with dbFullSql

    Feb 19, 2012 05:20 PM|shanedhorlock|LINK

    I did gat a backup downloaded of my database before I terminated the Netcetera hosting agreement.

    However, I have edited the three bits of the database DML and DDL script that referred to the old hosting account to refer instead to the new hosting account.

    I am trying to get the SQL Server Management Studio tool to connect to the new Hosting Service database. I'm finding the three tabs to connect the object explorer to the remote database to be a bit difficult - even though all of the information required has been provided to webmatrix and that works for the database connection test.

    I have asked my hosting provider for guidance on connecting the management studio, so I expect I will get there.

    Once connected I hope to be able to run my modified script and essentially do what webmatrix would have done if I could have modified its publication script on the fly.

    From reading blogs on the subject, I suspect a restore or FTP copy of the database to the new hosting service would lead to potential problems. There is nothing to guarantee that internal user id reference numbers would be identical in the two databases for example. Some blogs think possible collation differences may occur too.

    Regards, Shane

  • Re: Problem with dbFullSql

    Feb 20, 2012 01:16 AM|necro_mancer|LINK

    I completely agree with jeremyh,

    you just have to make a full backup of your database and restored it over to your database on the new host

  • Re: Problem with dbFullSql

    Feb 20, 2012 07:09 AM|jeremyh|LINK

    Shane,

    Ok hadn't realised before that the account was closed already. 

    The new host should give you the connection details you need, I've never used them before so I can't offer any details sorry.

    Another option you could think about then is todo a fresh install using the the Web Application Install at the new host
    to get everything up and running correctly then add your customer modules, code and sync any page data & images.

    Let me know how you get on.

    Regards
    Jeremy
    If this has helped Please: Don't forget to click "Mark as Answer" on the post that helped you.
    That way future readers will know which post solved your issue.
  • Re: Problem with dbFullSql

    Feb 21, 2012 01:24 PM|shanedhorlock|LINK

    Hi Jeremyh,

    Managed to get SQL Server removed from my system and the donloaded and installed SQL Server 2008 R2 Express with Tools (had installed the version without tools before)

    Then was able to use the Sql Management Studio with the connection details provided by my new hosting service.

    At long last could run the script generated by the database publishing wizard, with the three occurences of the old hosting service user id changed to the new hosting service user ID.

    Publish the application from  my PC to the remote host - but not the database - and can now run the application on the proper website. Not done an exhaustive test yet but will.

    However, wanted to get the local PC database to match the remote hosting database so I can use Webmatrix for future website management / backups etc. So tried to sync remote to local from within Webmatrix.

    Got the following failure which I have not had time to investigate yet (working day today and history group tonight)::

    21/02/2012 08:07
    08:08:59: Using ID '37bf5152-246b-45e4-b7db-33c32104e503' for connections to the remote server.
    08:09:16: Adding MSDeploy.Continous (MSDeploy.Continous).
    08:09:21: Adding dbFullSql (data source=.\SQLExpress;attachdbfilename="DotNetNuke® Community Edition2/App_Data\Database.mdf";integrated security=True;user instance=True).
    08:09:26: Cannot connect to the database 'C:\Documents and Settings\Duane Horlock\My Documents\My Web Sites\DotNetNuke® Community Edition2\App_Data\Database.mdf'. 
    08:09:26: Retrying operation 'Add' on object dbFullSql (data source=.\SQLExpress;attachdbfilename="DotNetNuke® Community Edition2/App_Data\Database.mdf";integrated security=True;user instance=True). Attempt 1 of 5.
    08:09:28: The database 'C:\Documents and Settings\Duane Horlock\My Documents\My Web Sites\DotNetNuke® Community Edition2\App_Data\Database.mdf' could not be created. 
    08:09:28: Retrying operation 'Add' on object dbFullSql (data source=.\SQLExpress;attachdbfilename="DotNetNuke® Community Edition2/App_Data\Database.mdf";integrated security=True;user instance=True). Attempt 2 of 5.
    08:09:29: The database 'C:\Documents and Settings\Duane Horlock\My Documents\My Web Sites\DotNetNuke® Community Edition2\App_Data\Database.mdf' could not be created. 
    08:09:29: Retrying operation 'Add' on object dbFullSql (data source=.\SQLExpress;attachdbfilename="DotNetNuke® Community Edition2/App_Data\Database.mdf";integrated security=True;user instance=True). Attempt 3 of 5.
    08:09:30: The database 'C:\Documents and Settings\Duane Horlock\My Documents\My Web Sites\DotNetNuke® Community Edition2\App_Data\Database.mdf' could not be created. 
    08:09:30: Retrying operation 'Add' on object dbFullSql (data source=.\SQLExpress;attachdbfilename="DotNetNuke® Community Edition2/App_Data\Database.mdf";integrated security=True;user instance=True). Attempt 4 of 5.
    08:09:31: The database 'C:\Documents and Settings\Duane Horlock\My Documents\My Web Sites\DotNetNuke® Community Edition2\App_Data\Database.mdf' could not be created. 
    08:09:31: Retrying operation 'Add' on object dbFullSql (data source=.\SQLExpress;attachdbfilename="DotNetNuke® Community Edition2/App_Data\Database.mdf";integrated security=True;user instance=True). Attempt 5 of 5.
    08:09:31: Unable to download published site.
    08:09:31: Unable to download published site. Unable to download published site.
    08:09:31: Error detail:
    08:09:31: The database 'C:\Documents and Settings\Duane Horlock\My Documents\My Web Sites\DotNetNuke® Community Edition2\App_Data\Database.mdf' could not be created.
    08:09:31:    at Microsoft.Web.Deployment.DeploymentObject.Add(DeploymentObject source, DeploymentSyncContext syncContext)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAdd(DeploymentObject destObject, DeploymentObject sourceObject)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentSyncContext.HandleUpdate(DeploymentObject destObject, DeploymentObject sourceObject)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildren(DeploymentObject dest, DeploymentObject source)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentObject.SyncTo(String provider, String path, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)
    08:09:31:    at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentWellKnownProvider provider, String path, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)
    08:09:31:    at Microsoft.WebMatrix.Deployment.MsDeployWorker.Execute(CommandManager commandManager, CertificateValidationService certificateValidationService, Boolean skipHiddenFiles)
    08:09:31: Database 'C:\Documents and Settings\Duane Horlock\My Documents\My Web Sites\DotNetNuke® Community Edition2\App_Data\Database.mdf' already exists. Choose a different database name.
    08:09:31:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    08:09:31:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    08:09:31:    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
    08:09:31:    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    08:09:31:    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
    08:09:31:    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    08:09:31:    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    08:09:31:    at Microsoft.Web.Deployment.SqlServerDatabaseProvider.AddHelper(DeploymentObject source)

  • Re: Problem with dbFullSql

    Feb 22, 2012 12:50 PM|shanedhorlock|LINK

    Hi Jeremyh,

    Had a chance to look into it and discovered I had left SQL Server Management Studio connected to my local PC database - so of course Webmatrix couldn't get the access that it needed.

    Disconnected and then sync remote to local worked a dream.

    Finally all issues resolved.

    Would be nice to understand why Webmatrix got things screwed up doing the sync remote to local from my first hosting service and then couldn't publish to the new hosting service, but at least we can pinpoint the problem references to the old hosting user id in the database as the immediate issue that prevented the publishing even if we don't know why the sync function transfered them to the PC copy in the first place.

    Thanks for your help and guidance on this problem. Having someone to discuss things with makes a real difference.

    Regards, Shane

  • Re: Problem with dbFullSql

    Feb 22, 2012 03:40 PM|jeremyh|LINK

    Shane,

    Great to hear you got everything working, Thanks for the update.

    Sorry I didn't reply before but been busy at work.

    Regards
    Jeremy
    If this has helped Please: Don't forget to click "Mark as Answer" on the post that helped you.
    That way future readers will know which post solved your issue.