| Author |
Topic |
|
Daniel Israel
Starting Member
15 Posts |
 Posted - 2006-11-07 : 20:07:26
|
| I'm moving a website that's using SQL Server 2000. Since it's on the ISP, I don't have physical access to the computer and can't access it through the manager.I can do backup and restore to move the database, but I actually want to change some things in the database (notably, the content in the database will have to reference a new URL)When I use MySQL, I can do a mysqldump which will create a text file of SQL statements that will recreate the database from scratch. I can then edit the text file before applying all the SQL statements to a blank database creating the tables, rows, etc.Is there such a creature in for SQL Server 2000? Failing that, is there another way to do it?Thanks for any pointers.-D. Israel |
|
|
monty
Posting Yak Master
130 Posts |
Posted - 2006-11-08 : 01:06:37
|
| Hi,If iam not mistaken you can have a look at the below option :1)go to enterprise manager2)hightlight the database for which u want to make changes.3)right click on the database and seclect all tasks4)click genreate transact sql script and select all objects5)and the text file would be created which will have t-sql commands for respective objects of your database 6)to this script you can make changes according to your requirement.7)and run the script in query analyzer.but make sure you have a full backup with you and also test the backup set before further going ahead.its me monty |
 |
|
|
Daniel Israel
Starting Member
15 Posts |
Posted - 2006-11-08 : 01:41:10
|
| As noted in my post above... I don't have access to enterprise manager...However, this is a good idea, even if I have to load the backup file on my own computer and make changes. Thanks.-D. Israel |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-08 : 10:44:23
|
quote:
Originally posted by monty
Hi,If iam not mistaken you can have a look at the below option :1)go to enterprise manager2)hightlight the database for which u want to make changes.3)right click on the database and seclect all tasks4)click genreate transact sql script and select all objects5)and the text file would be created which will have t-sql commands for respective objects of your database 6)to this script you can make changes according to your requirement.7)and run the script in query analyzer.but make sure you have a full backup with you and also test the backup set before further going ahead.its me monty
This will script the database structure only, not the data. To get the data as well, you'll need the SQL Server Database Publishing Wizard that Peter gave a link to. |
|
|
|
Daniel Israel
Starting Member
15 Posts |
Posted - 2006-11-08 : 14:00:10
|
| Thanks guys, I will check out that tool.Much appreciated.-D. Israel |
|
|
|
Daniel Israel
Starting Member
15 Posts |
Posted - 2006-11-09 : 12:15:26
|
| OK guys, just to follow up.I downloaded the tool from MS. It worked great on a small database (backup size about 4MB). However, my production database will backup to about 850MB and it went for a few hours, then just stopped!I don't know if there is a size limit, or what, but that is what happened.-D. Israel |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-09 : 13:01:31
|
| I'd say generating INSERT statements for an 850MB database is probably not a good idea - that will be over a GB of text! |
|
|
|
Daniel Israel
Starting Member
15 Posts |
Posted - 2006-11-09 : 13:19:17
|
| LOL. Yea.Ah well, live and learn. I'll figure out a way to do it programmatically I suppose.-D. Israel |
|
|
|
|
Dump Utility for SQL Server 2000?