How do you guys deploy databases to shared servers?

I'm heartily sick of the tedious way I have to spend half a day or more EACH WEEK uploading and downloading databases from my SQLServer2005 web sites. I'm going to have another go at learning the best way to do this . . Can someone please give me some help...    IN the old days I used to have a DTS task running overnight automatically, copying databases to and from my dev environment to the production web sites. Since SQLServer2005 there has been no way that I can find,  to create a DTS task and reuse it  EACH TIME I have to create it, and regardless of whether I save the DTS task or not,  it's not available next time for me to reuse. I have ot create it all over again.    Needless to say it's really PISSING ME OFF! Can someone please tell me how I can create a method of copying entire databases which might have 40tables or more with the indexes, and key relationships etc from remote servers to my local database server,  or conversely from my dev environment to my production server. And also how I can do this in a way that the task is repeatable without having to be created every time. I've tried using Visual Studio 2005 but that has finally defeated me. I can never find what I want to use,  and the MS Help files are total gobbldegook to me.   I cant make head nor tail of it.  After 3 years of wrestling with Visual Studio I have had to give up.  There's no way to save any file from that and reuse it.   Not that I've found anyway.    The best I've ever managed to do is create a solution,  and run debug every time.   And that takes 30 minutes to an hour to run a simple job of copying 3 tables, and ties up my machine completely for the duration. I'm sure my problem is that I'm trying to use the wrong tools,  or that I just don't know how to use the tools correctly,  but I'm despairing of ever getting back to the neat and tidy way I had to do this in the old SQLServer2000 days. Incidentally, just as an additional comment to my plea ... I cant use the copy database wizard, because it's a shared server and I don't have admin rights.    So I cant create a database on the remote server using SQLServer management studio. For the same reason I cant create a backup and then restore it on the other server.. For the same reason I cant use replication in any way. For the same reason, detaching, FTP then attach to the other server is out as well. I think Microsoft forgot entirely about the thousands of people using shared hosting servers when they developed SQLServer2005.   And I'm told there's no provision for it in the new 2008 version either. <sigh> So what do the rest of you people on shared hosts do?? -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month

check out sql litespeed for very quick and powerful backup and   restore.  Can be a handy way to move them about - although I must   admit I'm out of date now as I tend to stick to postgresql and mysql   which have very simple porting methods. Toby On 05/01/2009, at 11:30 AM, Mike Kear wrote: ----- Excess quoted text cut - see Original Post for more -----

Thanks Toby.    Does it allow you to back up and restore if you dont have administrator priveleges?   That's the problem with shared hosting - Microsoft dont seem to have made any provision for database owners to do any of these tasks,  only server administrators. Obviously, on a shared system where you might have hundreds of people creating and managing databases, you cant allow them all to have admin rights.    A web site owner usually only has dbo rights. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month > check out sql litespeed for very quick and powerful backup and > restore.  Can be a handy way to move them about - although I must > admit I'm out of date now as I tend to stick to postgresql and mysql > which have very simple porting methods. > > Toby > --

actually that may be a problem - sql litespeed works via a number of   stored procedures you install, but I'm afraid I can't remember the   permission requirements either for install or use.  There's a free   demo though, so you can give it a try. Toby On 05/01/2009, at 11:48 AM, Mike Kear wrote: ----- Excess quoted text cut - see Original Post for more -----

>I'm heartily sick of the tedious way I have to spend half a day or >more EACH WEEK uploading and downloading databases from my >SQLServer2005 web sites. Not that it helps you any, but this is exactly the reason I've moved most of my websites to MySQL. Navicat makes this kinda thing completely brainless for me. Will

I'll second that on  navicat - worth the very small fee for the pro   version even.  The ability to just select a source and destination db   and copy al structure and or data automatically is wonderful.  Doesn't   matter whether the source or destination are local or remote or both. Toby On 05/01/2009, at 2:15 PM, Will Tomlinson wrote: ----- Excess quoted text cut - see Original Post for more -----

> Can someone please give me some help...    IN the old days I used to have a > DTS task running overnight automatically, copying databases to and > from my dev environment to the production web sites. Have you tried this? http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information!

No I havent, Dave,  and I'm surprised that in all the whinging I've done to Microsoft in the last 3 years, no one there has mentioned it. I'ave gone at them over this issue so many times,  i have had to ease off in the last few months lest i be classed as a "vexatious support caller" and get myself banned. Perhaps my pestering over the years resulted in an outcome, but no one there figured it was worth telling me. I dont know but i'll give it a try.   On the face of it,   this looks like the solution for me.   Thanks! Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ----- Excess quoted text cut - see Original Post for more -----

What a big disappointment, Dave.  It wont connect to a remote database to update, only to a web service.  Who the hell inserts 40,000 records into a database using a web service??? So instead i have to have the tool create a big SQLscript.  I have to run a 85MB SQLscript.   by cutting and pasting it into my Query Analyser.   It's been running 8 minutes now,  and i hope it isnt going to timeout on me.        Not fillng me with confidence, but at least it's a move forward.     Thanks for suggesting it. Cheers Mike Kear ----- Excess quoted text cut - see Original Post for more -----

> What a big disappointment, Dave. Since I started receiving all the "enlarging" spam, I haven't heard that a lot lately. Oh well. > So instead i have to have the tool create a big SQLscript.  I have to > run a 85MB SQLscript.   by cutting and pasting it into my Query > Analyser.   It's been running 8 minutes now,  and i hope it isnt going > to timeout on me.        Not fillng me with confidence, but at least > it's a move forward.     Thanks for suggesting it. It shouldn't time out, but I suspect it will fail if your connection fails. > I have had the 85MB SQL script running now for 28 minutes (and > counting), giving my users database errors all that time. You should take the database and/or application offline before doing this sort of thing. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information!

We recently got Red gates toolbelt SQL tools.  Like you upgrading/ moving databases was time consuming and difficult.  Now its just so   much easier.  http://www.red-gate.com/products/ SQL_Professional_Toolbelt/index.htm Paul Kukiel On 04/01/2009, at 7:30 PM, Mike Kear wrote: ----- Excess quoted text cut - see Original Post for more -----

Thanks Paul.  I see those Red gates packages are a series of $395 applications.  My tiny company isn't going to afford $5000 worth of tools,  or even the bundle price around $1000. Which of them do you use to copy/duplicate/synchronise databases between two servers? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ----- Excess quoted text cut - see Original Post for more -----

How long does it take to publish a database using that database publishing wizard??   I have had the 85MB SQL script running now for 28 minutes (and counting), giving my users database errors all that time. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ----- Excess quoted text cut - see Original Post for more -----

I can't live without Red-Gate... > Which of them do you use to copy/duplicate/synchronise http://www.red-gate.com/products/SQL_Compare/ for DB structure. http://www.red-gate.com/products/SQL_Data_Compare/ for DB contents. ----- Excess quoted text cut - see Original Post for more -----

huh?  the COMPARE product is the one that copies data and structure between two servers???   Really?? Cheers MIke Kear ----- Excess quoted text cut - see Original Post for more -----

I didn't name them.  Just love them.  :-) ----- Excess quoted text cut - see Original Post for more -----

+1 for Red Gate's SQL Compare and SQL Data Compare.  Used to use them whenever a developer would get a production DB out of sync with the staging DB: very quick to figure out what key got missed, and it will gen the script to make A like B or B like A.

Yes these are the 2 that I use.  I haven't even used the rest of them. Regards, Paul ----- Excess quoted text cut - see Original Post for more -----

I think it's called SSIS or something, but awfully complicated and I never got it to work.... I copy production to dev by simply restoring a production backup on the dev server. For db alterations I do it all in TSQL scripts tested and known to work on the dev server, and then run on the production one. Richard Yes these are the 2 that I use.  I haven't even used the rest of them. Regards, Paul ----- Excess quoted text cut - see Original Post for more ----- regardless ----- Excess quoted text cut - see Original Post for more ----- entire ----- Excess quoted text cut - see Original Post for more ----- or ----- Excess quoted text cut - see Original Post for more ----- ever ----- Excess quoted text cut - see Original Post for more ----- is ----- Excess quoted text cut - see Original Post for more -----

An unhelpful observation: I just migrated a guy off of SQL Server and onto mySQL so we could handle this sort of thing so much more smoothly.  SQLYog has a command-line utility you can schedule that will do as many one-way or two-way synchs as desired and don't require the db to be taken offline. -- --m@Robertson-- Janitor, The Robertson Team mysecretbase.com

Red-Gate's answer to this (for SQL Server) is http://www.red-gate.com/products/SQL_Comparison_SDK/ (I think) No, I do not work for Red-Gate.  :-) On Mon, Jan 5, 2009 at 4:00 PM, Matt Robertson <websitemaker@gmail.com>wrote: ----- Excess quoted text cut - see Original Post for more -----

FWIW, I'm in the process of writing (well, it's probably more accurately one of those things that "works, but will never really be done") a CF8 script that rips through a database, creates insert statements (option to include truncate statements and also sets identity insert on/off), and saves the results as an SQL script. Requires CF8 and so far only works with MS SQL Server, but if you're interested, email me and I'll send you the cf code when I get home. This, combined with the generate scripts option in the tasks menu in MS SQL Server Management Studio, should get you where you need to go. I just wish there was a system stored procedure that would create the table structure, which would allow me to do this all in one script. Pete

One more tool to offer -- might be the one for you because it's free. EMS SQL Manager Lite (http://sqlmanager.net/en/products/mssql/manager/download) has an "extract database" function (Tools > Extract Database) which creates the database tables, dependent objects, and inserts for the data. Pete

DTS functions were moved to SSIS (SQL Server Integration Services).  There is a wizard in there which will migrate your old DTS package into SSIS.  The SSIS language and format is different than DTS so they need to be imported or re-written.  That's as far as I can take you.  Our company is migrating to SQL Server 2005 later this year so I haven't had to deal with it yet....

> I'm heartily sick of the tedious way I have to spend half a day or > more EACH WEEK uploading and downloading databases from my > SQLServer2005 web sites. The thing I don't understand is why you would do all that uploading and downloading in the first place. Shouldn't you have some architecture where one of the two is leading and you just sync the other one to it? Or is the data on production leading and do you have weekly releases with schema changes that you push from development to production? Jochem

@jochem,   the reason you have to do the copy is because without system administrator privileges, you cant use any of the features built in to SQLServer - you can use backup/restore,   you cant use the copy database wizard, you cant use the synchronise functionality.  In a shared hosting environment,  you cant have every user with system administrator access.   The most they get is DBO (database owner). Hosting companies (including mine) take daily backups,  but I'm not certain it's a brilliant idea to rely 100% on that  - Murphy's law says that if  anything goes pear shaped and i need to go back to backup, that'll be the day that something went wrong with the backup process and there's no backup available.  (I'm a belt and braces man - i like to have a backup plan to my backup plan) And also,   I develop locally, and like to have my development environment operating on an identical database to my production one. So i take a regular copy of the production databases to the dev environment. Then as i develop things,   frequently there are database changes,  so I change the dev version,   build my code and test it,  then copy the database to the production. So if you havent got database server administrator privileges, the only way to do all that is to copy databases,   both data and schema between one server and the other. @Richard,  thanks  - i have experimented with SSIS too, and never manged to be able to construct a solution that i could save in a way that it's reusable.  The only way i ever got it to do anything for me is to build the solution then run debug.    There was no way i ever got it to compile into a job i could run.    At one point i got excited because i did manage to get it to compile.  I ran that compiled job, it seemed like it was doing something but not a single byte of data was transferred after an hour of ticking over So I still have to use DTS,  go though every table one by one, and set the specs of that,  then run the DTS package,  and next time i need it, i have to do the whole thing all over again because there is no way i've discovered to recall a previously run DTS job and run it again.  That is something i used to do all teh time ( like every night) in the old SQLServer2000 but doesnt seem to exist in SQLServer2005. @Matt:   i think you might have a good point.   Perhaps i should start migrating all my clients over to MySQL.    But that's not going to happen overnight though. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ----- Excess quoted text cut - see Original Post for more -----

> @jochem,   the reason you have to do the copy I understand the technical challenge in implementing the workflow. My question was more related to why you would want the workflow to work the way it does. > Hosting companies (including mine) take daily backups Hourly in our case :) > certain it's a brilliant idea to rely 100% on that  - Murphy's law > says that if  anything goes pear shaped and i need to go back to > backup, that'll be the day that something went wrong with the backup > process and there's no backup available.  (I'm a belt and braces man - > i like to have a backup plan to my backup plan) What you could do for that scenario is create a linked database from your local MS SQL Server installation. Then you can simple write a stored procedure that does: INSERT INTO backup..table SELECT * FROM linkedserver..table Put in a loop over all records in INFORMATION_SCHEMA.TABLES and you have your backup solution. > Then as i develop things,   frequently there are database changes,  so > I change the dev version,   build my code and test it,  then copy the > database to the production. We always put a hook in onApplicationStart() to read the schema version number from the database and if the database schema is out of date it will run the SQL scripts to update the schema. Jochem

> We always put a hook in onApplicationStart() to read the schema > version number from the database and if the database schema is out of > date it will run the SQL scripts to update the schema. > That's a slick idea Jochem, I like that. Would you mind sharing the code for that?

   I hate to ask - but if you copy the data from the live server to your testing server, make a change to the database on your testing server then upload the database structure and data back to the live server, what happens to any new data collected on the live server in the time it takes you to make the change and upload the table? I run my own server so I don't have that problem, but if I did, I would think a better way might be just use  cfquery to modify the database (see http://tutorial28.easycfm.com/) . You can test it locally then run it on the server to make the database changes.. then ask the administrator of the server to backup the database to a password protected directory on your server, and run an automated task to zip it and download it to your local machine every night.. >Then as i develop things,   frequently there are database changes,  so >I change the dev version,   build my code and test it,  then copy the >database to the production. > >So if you havent got database server administrator privileges, the >only way to do all that is to copy databases,   both data and schema >between one server and the other.

Quite right, Al, on sites that collect user data on the production database.   Other sites just have a CMS system that is build on a database and there is no change to teh production database from one week to the next. It all depends on the applications that are running on the site and how they make changes to the database.   I am managing 40+ web sites adn they are mostly small but varied in the apps that are running there. On one, for example,  the client makes many changes each week in the CMS on the production database.  On that site, I always take a current copy of the database to my dev machine before i do any development work on their site.   On another, i run the CMS on my dev machine because it's my own site,  and when i have completed al my changes, i upload the database to teh productoin server to publish the week's changes to the site. As i said, it depends. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ----- Excess quoted text cut - see Original Post for more -----