| Author |
Topic |
|
u4cast
Starting Member
16 Posts |
 Posted - 2004-04-29 : 16:15:05
|
| Hi guys,I've just finished building a development SQL 2000 db and I want to move the whole thing to another machine at our hosting site. This includes DTS packages and all. What's the best way of getting everything there? Backup/Restore? Detach/Attach? Copying files? combination of all the above maybe? As you can gather this is not something I've done too often. In fact never.Any takers?Thanks in advance. |
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2004-04-29 : 16:26:15
|
| Only ever done it once myself so far. I scripted all my tables, and I exported the initial data that the app needed (types and intial keys etc..) to csv files. You can save DTS packages as files and open them on the Live server.This was ok but I was only dealing with 80 tables and 30 files and no DTS packages.If you have more objects than that you will probably find the Backup/restore option easier, you'll need to bear in mind the paths to the physical database files MDF and LDF as they are likely to be different between your Dev box and the hosted one, use the MOVE option with RESTORE. Your DTS stuff will be in the MSDB. Is the hosted box a clean SQL server ? if it isn't you're not going to be able to restore MSDB to it, you'll have to save the packages as files.;-]... Quack Waddle |
 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-04-29 : 16:29:55
|
| Backup and restore, Be sure to back up msdb as it contains your DTS packagesJimUsers <> Logic |
|
|
|
u4cast
Starting Member
16 Posts |
Posted - 2004-04-29 : 16:34:27
|
| Thanks guys - I think I'll do a bit of both. The scripts will be useful to have I think.Really appreciate the quick answer - we go live tomorrow night! One other question though - do I need to move master across? Am I right in thinking it's specific to the installation so I don't need to take it with me?Thanks again. |
|
|
|
u4cast
Starting Member
16 Posts |
Posted - 2004-04-29 : 16:56:24
|
| As an extra note I've just discovered - you can't develop on Enterprise then restore msdb on a Standard edition! No big deal for me but it might be to someone who reads this. |
|
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2004-04-29 : 16:56:45
|
| To the best of my knowledge, your schema will be added to the systables in the master database on the live box as part of the RESTORE procedure.Maybe someone could back me up on that.Certainly running create database and create table statements do in scripts.;-]... Quack Waddle |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-29 : 17:05:49
|
| Here is what we have done for the initial deployment of our database:Create empty databaseCreate tablesCreate other objects such as stored procedures, views, and UDFsCreate usersCreate roleGrant permissionsCreate DTS packagesLoad default data using T-SQL or bcpThis is all done with T-SQL (except for DTS where we do a save as in dev to a dts file and then load the package in prod using EM). We do not use BACKUP/RESTORE or sp_detach_db/sp_attach_db methods as we don't want to move dev data over, we want to start from scratch with table lookup data/default data only.cas_o, I don't understand what you are asking. Schema stuff goes into the user database, not the master database. master database contains basic information about the user database. The user database is where specific information is stored in.Tara |
|
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2004-04-29 : 17:09:12
|
| T, you're absolutely right, ignore my ramblings on schema DOH!it's 2200 here in UK and I'm possibly on the too many side of a bottle of Hardys Crest.;-]... Quack Waddle |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
|
Moving Dev to Live?