|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Copy db to webhost serverIs there any way to use Management Studio to copy a database from my local SQL2005 server to the webhost's? Back up/restore or Copy Database requires root access, which of course I don't have on the webhost's server. In Enterprise Manager with SQL2000, I could use the Export/Import wizard and choose Copy Objects, so all keys, relations etc were copied, but that option seems to be missing. My webhost says the only way is to e-mail him a backup so he can manually restore it... I shudder at the very thought of applying such a suboptimal pattern. Have I missed something in Management Studio, or are there other tools I should be using besides it? Thanks, Jonas Jonas (l***@jobe.nu) writes:
> Is there any way to use Management Studio to copy a database from my Well, it's probably the safest way. But of course for a database of> local SQL2005 server to the webhost's? > > Back up/restore or Copy Database requires root access, which of course > I don't have on the webhost's server. > > In Enterprise Manager with SQL2000, I could use the Export/Import > wizard and choose Copy Objects, so all keys, relations etc were copied, > but that option seems to be missing. > > My webhost says the only way is to e-mail him a backup so he can > manually restore it... I shudder at the very thought of applying such a > suboptimal pattern. any size, not really appealing. First your webhost has to create an empty database for you. Then you use Generate Scripts to generate a script for the database, and run that in the empty database. Then you use Export/Import data. You find all these options by right-clicking the database in Object Explorer and then selecting the Tasks submenu. I will need to add disclaimer that I have not looked much at Import/Export data, so I can't say for sure that will do what you are looking for. But, of course, for a litle more work, you could use BCP to extract and import data. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > First your webhost has to create an empty database for you. Then you Thanks - that's what I've been doing so far, but it's very cumbersome.> use Generate Scripts to generate a script for the database, and run > that in the empty database. Then you use Export/Import data. You > find all these options by right-clicking the database in Object Explorer > and then selecting the Tasks submenu. Since my db has constraints that don't allow tables to be copied in any order, I have to write my own queries for copying. And recheck them manually everytime I need to do a new copy when the structure has changed. And to top it off, write my own drop script, since the IDE won't let me delete multiple tables at once. Extremely tedious; I think I'd be better off writing a C# app that analyzes the db and does the job for me (isn't that the kind of work a management tool should do for me?) It must be a very common situation. I don't think MS has taken a conscious decision to make life hard on webhosts with SQL2005, but either me and my host have missed something, or MS has... Jonas (l***@jobe.nu) writes:
Show quote >> First your webhost has to create an empty database for you. Then you Indeed, if the SMO method of the Copy Database Wizard permitted you>> use Generate Scripts to generate a script for the database, and run >> that in the empty database. Then you use Export/Import data. You >> find all these options by right-clicking the database in Object Explorer >> and then selecting the Tasks submenu. > > Thanks - that's what I've been doing so far, but it's very cumbersome. > Since my db has constraints that don't allow tables to be copied in any > order, I have to write my own queries for copying. And recheck them > manually everytime I need to do a new copy when the structure has > changed. And to top it off, write my own drop script, since the IDE > won't let me delete multiple tables at once. > > Extremely tedious; I think I'd be better off writing a C# app that > analyzes the db and does the job for me (isn't that the kind of work a > management tool should do for me?) > > It must be a very common situation. I don't think MS has taken a > conscious decision to make life hard on webhosts with SQL2005, but > either me and my host have missed something, or MS has... to specify an existing database that was assumed to be empty, your web host could create the database, and CDW do the rest. (Well, it may not be that simple. CDW runs an agent job, on the receiving side, so you need permissions for this.) You could submit a suggestion for this on http://connect.microsoft.com/feedback/default.aspx?SiteID=68. There are plenty of issues submitted about CDW already, but I don't recall if anyone has made this suggestion. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks for the link, I'll submit a report.
In the meantime, I think I can use MyGeneration to automatically create correct and complete copy scripts. Erland Sommarskog wrote: Show quote > Indeed, if the SMO method of the Copy Database Wizard permitted you > to specify an existing database that was assumed to be empty, your web > host could create the database, and CDW do the rest. (Well, it may > not be that simple. CDW runs an agent job, on the receiving side, > so you need permissions for this.) > > You could submit a suggestion for this on > http://connect.microsoft.com/feedback/default.aspx?SiteID=68. There > are plenty of issues submitted about CDW already, but I don't recall > if anyone has made this suggestion. > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics