Chaning all table names of ASP.NET 2.0 PW Stater kit and moving it to Godaddy.com (1)
Intro:
I created a personal website based on MS ASP.NET 2.0 personal website starter kit on my local PC. It runs well. Once time I moved it to a free asp.net hosting provided the same environment like MS Visual studio 2005 development such as SQL Server 2005 Express. So It was very easy to migrate from local. For the database, I just copied the two .mdf files (aspnet.mdf and personal.mdf) to that hosting server and click "attach" buttons that they provided. then I got all data as same as my local's; and then uploaded all relevant files , then, done for everything.
However, The free is not always convenient for you. some days I have not managed my site, it gone! I lost all my personal website content! So, I bought a asp.net space from Godaddy.com and tried to move my local personal website based on starter kit to my godaddy.com space.
The following is my migrating process:
1.install MS Sqlserver management studio express;
download: http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en
Detach ASPNETDB.mdf and Personal.mdf on your local PC if you are using MS visual studio 2005 to open them;
Run Sqlserver management studio express. connnect to the sqlserver that you have intalled in ur pc.
in the "Object Exploer" windows, right click "Databases", select "Attach...", then open your database .MDF file (sql server 2005 express file);
5: right click the database file that u just now attached. select "Tasks" > "generate Scripts". then generate sql file you want (create or drop);
Note1: I changed the "Script for Server version" to "Sql server 2000" on "Choose Script options" page since godaddy.com just provides sqlserver 2000 but not 2005) Note2: I selected tables and store processes, create SQl to a file NOTE3: sqlserver management studio express can not generate data for you ,just generate empty table stucture!! so I have to add data manually! but you can use the 3rd part script to backup and insert your part of data (see following content), then if I have lots of data, the sqlserver management studio express is no useful!
(Sure you can get your data if you use backup and restore option. But for me, the tables name changed, so I can not use backup and restore ways )
5: u can use these sql file to build your database on remote hosting for ur asp.net project. attention the sqlserver version on the remote hosting, u can select sql server version when u generate script file;
6: u can change the tables name through these sql file. just change the tables name manually in .sql file, then run sql script file on remote hosting;(right click database name and select "new query", then copy the content of sql file and then excute)
7: detach the database file from sql server management express;
8: open visual studio 2005, u need to change the asp.net code for the changing of table and store precedures' name
9: sqlserver management express can not generate sql file for data, just backup, so if you changed the table name , u have to copy data from original tables to new tables one by one manual; OR,
you can use this script , it is a very good script: http://vyaskn.tripod.com/code.htm#inserts How ever, it can not copy image field, so for image fields, I had to download from old project and then upload to new project one by one manually.
more notes about use the above script:
1: after download the script, copy to notepad, should turn off notepad's word wrap; 2: copy the script to SQL Server Management Express' SQL Query window; 3: open a new SQL query windows and copy the following codes, this is to generate INSERT statements for all the tables in your database, execute the following query in that database, which will output the commands, that you need to execute for the same:
SELECT 'EXEC sp_generate_inserts ' + '[' + name + ']' + ',@owner = ' + '[' + RTRIM(USER_NAME(uid)) + '],' + '@ommit_images = 1, @disable_constraints = 1' FROM sysobjects WHERE type = 'U' AND OBJECTPROPERTY(id,'ismsshipped') = 0
4: excute the above script and I will get the result like the following in the result window of SQL Server Management Express:
EXEC sp_generate_inserts [sysdiagrams],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1 EXEC sp_generate_inserts [PWS_SiteContent],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1 EXEC sp_generate_inserts [PWS_Albums_Special],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1 EXEC sp_generate_inserts [PWS_Photos_Special],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1 EXEC sp_generate_inserts [PWS_Projects],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1 EXEC sp_generate_inserts [PWS_Albums4FileSys],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1 EXEC sp_generate_inserts [PWS_Photos4FileSys],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1 EXEC sp_generate_inserts [PWS_Albums],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1 EXEC sp_generate_inserts [PWS_Photos],@owner = [dbo],@ommit_images = 1, @disable_constraints = 1
5: copy the above script and paste into a new SQL Query script window and excute them;
I would get the insert script of my personal site database (no include ASPNETDB database) for data insertions
6: Copy the final script and paste into SQL Query window of Godaddy account....
when I tried to submit and execute them , I got errors because the following reason, then I deleted those script cause these errors , just let good scripts run firtly.
Those fields are about images, I have to insert image by manual when my website run, Don't try to insert default or others values like the following way , just let the PWS_Photos and PWS_Photos_Special tables empty firtly, later when website run I insert images one by one manually :
I have to modified the above data insert script manually because in my database there are some images fields are not nullable, but the above script do not include this un_nullable fields such as thoes images field in photos tables,
I added default values ('') for those images fields (ie: largeimage), for example:
INSERT [dbo].[PWS_Photos_Special] ([id],[title],[albumid],[notes],[largeimage])VALUES(1,'Indigo Projects Management',1,NULL,'') INSERT [dbo].[PWS_Photos_Special] ([id],[title],[albumid],[notes],[largeimage])VALUES(2,'ALE',1,NULL,'') INSERT [dbo].[PWS_Photos_Special] ([id],[title],[albumid],[notes],[largeimage])VALUES(3,'GMLC',1,NULL,'') INSERT [dbo].[PWS_Photos_Special] ([id],[title],[albumid],[notes],[largeimage])VALUES(4,'I3DVR',1,NULL,'') INSERT [dbo].[PWS_Photos_Special] ([id],[title],[albumid],[notes],[largeimage])VALUES(5,'GSM',1,NULL,'') INSERT [dbo].[PWS_Photos_Special] ([id],[title],[albumid],[notes],[largeimage])VALUES(6,'NetEase',1,NULL,'') INSERT [dbo].[PWS_Photos_Special] ([id],[title],[albumid],[notes],[largeimage])VALUES(7,'Railway Military',1,NULL,'') INSERT [dbo].[PWS_Photos_Special] ([id],[title],[albumid],[notes],[largeimage])VALUES(8,'SCP',1,NULL,'')
Here is another good article for how to move sqlserver database to remote hosting: http://blogs.msdn.com/robburke/archive/2006/05/30/610803.aspx
(用sqlserver management express时，可以同时attach旧的和新的mdf文件，但是旧的在attach时要修改一下attach as到新的路径，即和新的mdf文件不要一样的路径就行了。)
for the tables' prefix string changing, just add my prefix string for all tables one by one, and change my asp.net codes in files.
login to godaddy.com and access to your asp.net database, open it , use Qurery Analyst tool, copy all your .sql content and paste into Qurery Analyst, run it.。
But I got an error:
CREATE TABLE [dbo].[PWS_SiteContent]( [TextID] [int] NOT NULL, [Homepage_Welcome] varchar NULL, [Homepage_WhatsNew] varchar NULL, [Homepage_WhatsUpLately] varchar NULL, CONSTRAINT [PK_PWS_SiteContent] PRIMARY KEY CLUSTERED
Line 11: Incorrect syntax near 'max'.
later I will write how I resovle this problem.
Finally I found SQL Server doesn't support (max), I changed (max) to (4000), then works!