Put database on website
-
I am used to being able to upload and download Access dbs to web servers, but this is the first time I've used SQL Server.
What is the best way to get the database up to a new website.
I have developed a db on my own computer.
Once on the website, it will be accessible only via a ColdFusion application, so that only I will have direct access to the db.
When I export the db to the shared server, only the basic tables are exported, all the keys, relationships and constraints are lost.
I am still developing further sections of the application, and the db on my computer includes lots of dummy test data that I do not want to lose.
So I need to do this:
Make a new copy of db1 on my computer and strip out the test data.
Transfer this db2 to the web shared server including all keys, relationships, constraints, defaults etc.
Then the user must test the application, so after building the basic data into the db on the server, I need to make a complete copy of this, db3, and save it on my computer.
Then the user can test.
When that is complete, db4 with the test data on the server must be replaced with the db with the base data db3 and then the website can go live.
Any pointers or clues as to the best way to achieve all this?
Doug
-
Not sure if this is "standard" practice, but it works for me. I have a little SQL database with about 10 tables and a dozen or so views.
When I migrating changes to the website I script out all the changed objects using Enterprise manager. Then I run those scripts against the hosted db. Once the database objects have been updated I run a DTS package to transfer any additional data thats needed.
This process works well for me, but then its only a small database.
--------------------
Colt 45 - the original point and click interface
-
Thanks for reply.
I guess that means there's no easy way. What a pity SQL Server can't reverse engineer the database; look at the existing tables and automatically write the code that would recreate it.
Unfortunately, I have a lot more tables than you, but if it has to be done ...
Thanks again,
Doug
-
Maybe I've misinterpreted you question, but why can't you just copy the .mdf and .ldf files from your computer to the web server and then attach the database? Of course, the logins will be messed up, but there are ways to fix that.
-
Unfortunately, on a shared server, you cannot access the directory where the actual database is stored. Otherwise, you could make copies of everyone else's database.
Even so, I've now found that I selected the wrong option in the DTS Export wizard, and that was why I only got the tables. On the third page of the wizard, you need to check the third option not leave it at the default, to get all the table attributes transferred as well. So things are no too bad after all.
Doug
-
Yes there is that option, but I think it still excludes foreign key constraints?
That's the main reason I script out the changes seperate from the data.
--------------------
Colt 45 - the original point and click interface -
As far as I can see all the FK constraints are there. I'm looking in sysobjects. Is that the right place to look?
Doug
-
Doug it propbly has been a year reg. above subject matter question Put database on website, but have you found an answer to your question . If you have found then please put up the answer, I am facing the same problem that you have faced a year ago. So if possible please help me. TankX in advance.
-
The answer was in my message Posted 3/2/2005 6:05:00 PM. That worked for me and included everything.
Doug
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply