I'm moving an Umbraco installation from one shared hosting provider to another. I have a back-up of the SQL 2008 Express database and asked the new host to restore. They did, but they are unable to change the database schema ownership (why? I don't know - Titan support is usually good but this time infuriatingly bad) so I just get the error when trying to load the site on the new server:
Invalid object name 'umbracoDomains'.
Although I have VPN access to the database on the new server, I don't have permissions to change the schema ownership myself, so frustratingly I'm looking for other ways to transfer the database.
I could do the 'Tasks > Genearate scripts' procedure in Management Studio, but that won't set the tables up with the correct key contraints and primary keys when the script is run on the new server. So now I'm looking at generating a SQL script using SQL Server Database Publishing Wizard, which historically (I last used it 3 or 4 years ago) was always good at preserving key contstraints.
I wondered if anyone has had any success in using Publishing Wizard for Umbraco database transfers, or if there are any other routes I could try? I just need to ensure that as well as the table structre and data, the relationships and keys are transferred intact too, which always seems to be the tricky part.
Thanks Jay, I'll do that - try it on a local DB first. I thought SQL Pub Wizard had been abandoned until I read somewhere that it actually comes with SQL Server and is typically here: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4\SqlPubWiz.exe.
So eventually, after 5 days, Titan found someone who could change the schema, so it's sorted! However, I did try out SQL Database Publishing Wizard just to tidy up the thread here and found that it stumbled whilst restoring some records in the umbracoLog table, causing the script to terminate before completing and resulting in a very much incomplete database (fewer than half of the tables even created). I truncated the source log table and tried again and that seemed to work, so it does seem to do the trick albeit with a bit of 'bending'.
I could probably have helped you out if I'd seen this thread sooner. I had a similar experience (the evening of the last umbristol meet actually!) with a host who wouldn't restore from a backup and I had some joy with the wizard you mentioned above. I had to do a little editing of the script generated locally to match the user I had created for the remote db but could then run that script on management studio attached to the remote db server.
Things i also tried which didn't work were:
Publishing from management studio using the import/export feature into a blank remote database - similar results to you where no keys/relations set up correctly.
Publishing from management studio using import/export feature into freshly installed umbraco remote database - this resulted in entries being appended into all of the umbraco tables with all of the wrong identities because there was already data in the tables.
I needed something which scripted schema & data and the publishing wizard made this all really easy. I'm pretty sure if i'd had more time i could have figured out how to script the entire database (with all of the right settings) from management studio and run that script on the remote sql box but like i said, the publishing wizard just worked.
moral of the story is: make sure when you pick a host host, they will enable you to restore a backup!!!
Using SQL Database publishing wizard to transfer database - will it work?
Hi,
I'm moving an Umbraco installation from one shared hosting provider to another. I have a back-up of the SQL 2008 Express database and asked the new host to restore. They did, but they are unable to change the database schema ownership (why? I don't know - Titan support is usually good but this time infuriatingly bad) so I just get the error when trying to load the site on the new server:
Invalid object name 'umbracoDomains'.
Although I have VPN access to the database on the new server, I don't have permissions to change the schema ownership myself, so frustratingly I'm looking for other ways to transfer the database.
I could do the 'Tasks > Genearate scripts' procedure in Management Studio, but that won't set the tables up with the correct key contraints and primary keys when the script is run on the new server. So now I'm looking at generating a SQL script using SQL Server Database Publishing Wizard, which historically (I last used it 3 or 4 years ago) was always good at preserving key contstraints.
I wondered if anyone has had any success in using Publishing Wizard for Umbraco database transfers, or if there are any other routes I could try? I just need to ensure that as well as the table structre and data, the relationships and keys are transferred intact too, which always seems to be the tricky part.
Thanks for any recommendations.
Hey Dan
I'm pretty sure others use it (and I think it's the tech behind Webmatrix publishing but don't quote me on that)
I think I or Richie Green has got a link to the sqlpubwiz installer somewhere on the forum. Maybe try just transferring it to a second local db first.
Hope that helps
Jay
Thanks Jay, I'll do that - try it on a local DB first. I thought SQL Pub Wizard had been abandoned until I read somewhere that it actually comes with SQL Server and is typically here: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4\SqlPubWiz.exe.
Interesting that it may be what powers WebMatrix.
I'll report back, hopefully with good news :)
Cheers
So eventually, after 5 days, Titan found someone who could change the schema, so it's sorted! However, I did try out SQL Database Publishing Wizard just to tidy up the thread here and found that it stumbled whilst restoring some records in the umbracoLog table, causing the script to terminate before completing and resulting in a very much incomplete database (fewer than half of the tables even created). I truncated the source log table and tried again and that seemed to work, so it does seem to do the trick albeit with a bit of 'bending'.
I could probably have helped you out if I'd seen this thread sooner. I had a similar experience (the evening of the last umbristol meet actually!) with a host who wouldn't restore from a backup and I had some joy with the wizard you mentioned above. I had to do a little editing of the script generated locally to match the user I had created for the remote db but could then run that script on management studio attached to the remote db server.
Things i also tried which didn't work were:
Publishing from management studio using the import/export feature into a blank remote database - similar results to you where no keys/relations set up correctly.
Publishing from management studio using import/export feature into freshly installed umbraco remote database - this resulted in entries being appended into all of the umbraco tables with all of the wrong identities because there was already data in the tables.
I needed something which scripted schema & data and the publishing wizard made this all really easy. I'm pretty sure if i'd had more time i could have figured out how to script the entire database (with all of the right settings) from management studio and run that script on the remote sql box but like i said, the publishing wizard just worked.
moral of the story is: make sure when you pick a host host, they will enable you to restore a backup!!!
is working on a reply...