DTS Packages for doing backup/restore?

Since you cannot use a true backup/restore from Jodohost I was wondering how most users arte doign this with DTS?

Basically I need a means to easily sync databases back and forth between a local development server running SQL 2000 and the Jodohost copies. This would include the full data, PSs, views, etc, same as if I did a backup and restore.

Any suggestions/tips wopuld be appreciated.
 
Anyone? Can't seem to get DTS working for doign a full transfer of structure and data. Would much prefer to be able to do a resote.
How can I setup a DTS package that will basically allow backups and restores from a local DB to the Jodohost one?
 
Tried the copy datbase wizard but that is a no go as you need sysadmin rights on the destination.

When I try the export wizard I trun into issues with the user login accounts not being the same.

What ecactly needs to be done to get from a new empty database on Jodohost loaded with structure, SPs, and data from a local server?
 
You will need DBO access to do the DTS package, but they do work, I have used them a number of times in that fashion for customers.
 
Here's what I do. Use Enterprise manager to script your databse at jh (all tables, view, stored procs, etc). Recreate the database locally on your machine with that script. Then create your dts and save it on your machine. Use it to transfer all data over.

This of course will break if you make changes to the db on the server. Make sure you keep them in sync by making changes in both places. You can even schedule it to run on your local sql server.
 
jonyah said:
Here's what I do. Use Enterprise manager to script your databse at jh (all tables, view, stored procs, etc). Recreate the database locally on your machine with that script. Then create your dts and save it on your machine. Use it to transfer all data over.

This of course will break if you make changes to the db on the server. Make sure you keep them in sync by making changes in both places. You can even schedule it to run on your local sql server.

What I have is a complete working database locally that I need to get to Jodohost.
 
You can send a dump of that database over to us so we can restore your online database from that. That way you'll get the same structure both online & offline, and you can use DTS to syncrhonise from then on
 
Yash said:
You can send a dump of that database over to us so we can restore your online database from that. That way you'll get the same structure both online & offline, and you can use DTS to syncrhonise from then on

But wha tI am hoping to avoid is that step. I'd like to have the ability to totlaly create an ew database from scratch and upload the data and structure from a local host to Jodohost if possible. At this point we are jsut tryign to determine how easy it would be for doing such tasks under Jodohost.
 
you would just do what I mentioned in reverse. Use EM to script all the objects in your database and then run it at Jodohost (should take less than a minute). From there you can create a dts package to upload all the data.
 
jonyah said:
you would just do what I mentioned in reverse. Use EM to script all the objects in your database and then run it at Jodohost (should take less than a minute). From there you can create a dts package to upload all the data.

Ah so your saying script it out and run it then do the DTS. I was thinking it could be all done via DTS.

So just run the creation scripts direct against the Jodohost DB via EM, and then use a DTS package to syn the data?
 
you can do everything via dts, but it's not perfect. For example, all of your keys defined will be lost. That's why I say to script it out first (and make sure you have it scripts the keys, identities, triggers and indexes).
 
jonyah said:
you can do everything via dts, but it's not perfect. For example, all of your keys defined will be lost. That's why I say to script it out first (and make sure you have it scripts the keys, identities, triggers and indexes).

Ok thanks. Too bad they don't support proper backups and restores. I'm so use to this way for handling deployment and pulling down data for testing. it ensures you have an exact copy. DTS is going to be hit and miss depending on the way you set it up.

Jodohost - Any chance we can see some movement on an online backup/restore process?
 
I've never actually had a host that let me have access to the backups of my database , that kinda requires the SQL Server to be on the same server as the web server, which isn't a good thing to do. Doing the backup/restore procedure I mentioned has become second nature to me and can be done in just a couple of minutes with the same accuracy as attaching the db or restoring from backup. I would be interested in having that ability though.
 
jonyah said:
I've never actually had a host that let me have access to the backups of my database , that kinda requires the SQL Server to be on the same server as the web server, which isn't a good thing to do. Doing the backup/restore procedure I mentioned has become second nature to me and can be done in just a couple of minutes with the same accuracy as attaching the db or restoring from backup. I would be interested in having that ability though.
1nad1 and Webhost4life both provide a means to do it. They have a web interface that lets you do it and it places the files on an ftp folder for you to uplaod/download. They both run on seeperate servers from the webserver as well. I suspect htye jsut have some scripts in place that automate it and move the files as needed. Something Jodo should consider. Make it easy to grab a backup, create a new DB on your test server and restore for testing.
 
ya i could see writing a service that checks for files on the sql server that would move them to the user's webroot upon finding them.
 
jonyah said:
ya i could see writing a service that checks for files on the sql server that would move them to the user's webroot upon finding them.

Ya, even having them make the daily backups available in an ftp folder would be a nice step forward. Then we could jsut grab them as needed, al they would need to do is keep the latest copy on our user folder.
 
schooner, I have been working on this for you, for the last hour or so.

I have installed Web Data Administrator (a microsoft tool) that allows you to make local backups to your disk. i have done so only on mssql5 for now. If its successful, I'll install it on all mssql servers:

http://mssql5.jodoshared.com/webadmin/default.aspx
username: webadmin
password: everyone

be sure to change Authentication Mode to SQL Only
 
seems it is hanging when I try to login. I'll need to get this checked out by Stephen or another admin. We'll probably have this running max by Monday
 
Yash said:
schooner, I have been working on this for you, for the last hour or so.

I have installed Web Data Administrator (a microsoft tool) that allows you to make local backups to your disk. i have done so only on mssql5 for now. If its successful, I'll install it on all mssql servers:

http://mssql5.jodoshared.com/webadmin/default.aspx
username: webadmin
password: everyone

be sure to change Authentication Mode to SQL Only
Yash - wow I really appreciate the effort on this, great customer support even for us trial users causing headaches.

I just triedthe interface however and can't seem to get it to connect, just sits at the login screen.
 
OK i fixed it. asp.net SP1 made some changes to the WebUIValidation.js file.. Webdata administrator was using the older version causing it to hang..

Try now and let me know please
 
Back
Top