DTS Packages for doing backup/restore?

yep, it allows you to import/export your database quite easily it seems to me. I'd appreciate everyone's feedback
 
if this is put on the other servers will it be a reseller thing also or just for jodoshared customers.

i would try but dont have Databases on that server..:D
 
it would be a reseller thing too

mssql5.yourhost.com. I'll have that implemented once I get some feedback from mssql5 users.
 
Just tried it and it allowed me to export my full database to a SQL script that I could save locally.

Couple words of caution, I can change user rights with this tool, my rights as well as other users.
 
jonyah said:
I'm unable to log in. What should the server be set to?
Click to expand...
Just leave as local host but you need to have your SQL account on that server to test it. Use your SQL ID and password to login once past the first password prompt from the link Yash posted.
 
schooner said:
Couple words of caution, I can change user rights with this tool, my rights as well as other users.
Click to expand...

This shouldn't be possible. I attempted to do the same and it gave me a permission denied error although I can see all users and click on the edit link.

I have requested your username/password in a PM so I can check this out. If there is an issue, it has to do with your permissions rather than webdata administrator
 
Yash said:
This shouldn't be possible. I attempted to do the same and it gave me a permission denied error although I can see all users and click on the edit link.

I have requested your username/password in a PM so I can check this out. If there is an issue, it has to do with your permissions rather than webdata administrator
Click to expand...

Sent you a PM. I didn't actually try and change anyone elses but I seemed to be able to change my own. Perhaps I was mistaken.

As for this import/export process I still don't think it is that great. It just creates a SQL script which then needs to be editted to even work, ie when I tried to run it on my local server I got all sorts of script errors. Whereas with a restore it just works but I'll play with it and DTS more when I can get time.
 
well, if you use it to do restores directly on the server you wouldn't get any of those errors

I suspect the errors are due to the fact that you didnt create the database users locally with the exact same permissions
 
Yash said:
well, if you use it to do restores directly on the server you wouldn't get any of those errors

I suspect the errors are due to the fact that you didnt create the database users locally with the exact same permissions
Click to expand...

I suspect that is the case as well due to the different users and permisssions, dbo, etc.

I'll keep playing with it. I am sure with this, DTS, etc that I cna get it to meet our needs, Ill', just more use to handling it with direct backups/restores as that is how I have handled it with other web hosts and also with client for internal systems.
 
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.
Click to expand...

Jonya, you must have some great luck, because I have found it difficult to get this to work. I basically have a DotNetNuke database i need to get transferred from one host to another host. Here is what I have attempted to do.

1) I load up Enterprise manager and have it script the source database. I basically script everything but the physical database and users. I don't script those two because of the next step.

2) At the destination I create the database and users via the HSPHERE Control panel and request dbo rights from Jodo.

3) I then take the resulting script from step 1 and run it in query analyzer for the destination database. This goes without any problems.

4) I then need to get the data over so i start a DTS import wizard and select the copy all objects option which actually duplicates the exact same object name in the destination accept instead of dbo as being owner the sql login name is set as owner.

So I start all over again and on step 4 above I this time select the last option where i have more control over what it transfers. I only select copy the data for all objects. It fails and looking at the error it says that it fails to truncate a table due to a foreign key constraint. I assume this is the case because it is replacing the data (even though it is empty) instead of doing an append.

So far I have not successfully been able to get the data transferred over. due to one reason or another. I am looking to use one of my other tools from RedGate and i am sure it will work, but my customers don't have the luxury of such a tool.

I have to say doing this via dts is kind of a pain and isn't really working out. Any thoughts on this?

Also... even if I could get a backup of the database from the host and try to restore it at the destination... wouldn't that whack out HSPHERE or is the key in a true backup / restore situation is to make sure the destination HSPHERE system has already created the database and users so that HSPHERE has it recorded in their system?

I look forward to any feedback... I really need to get this DotNetNuke database transfered over.

Thanks
 
I agree that using the DTS way is painful.
If JH had backup/restore I would most likely signup today but this is the main reason I am still sticking with Webhost4life.
 
foreign keys can cause problems. as far as duplicating the objects, when you do the data transfer, you need to specify the user "dbo" that will own the table that data is being transferred to. That way it goes into the tables your script created instead of creating new tables and putting the data there.
 
jonyah said:
foreign keys can cause problems. as far as duplicating the objects, when you do the data transfer, you need to specify the user "dbo" that will own the table that data is being transferred to. That way it goes into the tables your script created instead of creating new tables and putting the data there.
Click to expand...

So do we really want to do step one if dts has the ability to create all of the objects? If DTS is going to create the objects why bother with scripting the objects via step one above?

Also... where do you specify the user during the DTS wizard?

And if the foreign keys are going to cause problems then it seems there is no way to do this without using a tool like RedGate that disables the foreign keys before transferring data.

I would like to get the EM to work at least once or find a way where I know it will work every time so I can make a document for this.
 
jonyah said:
foreign keys can cause problems. as far as duplicating the objects, when you do the data transfer, you need to specify the user "dbo" that will own the table that data is being transferred to. That way it goes into the tables your script created instead of creating new tables and putting the data there.
Click to expand...

How do you specify user "dbo"? The only user I have rights to is user "??????_????" which is the user name I had hsphere create and was given dbo rights to the database with this name.

The only place I can specify any users is the actual username and password to authenticate to the source and destination database.
 
when setting up the dts transfer you select the tables you want to transfer the data from/to. In the second column is the name of the table it's going to. You may have to click on that value and modify the username that owns the table.
 
BluJag said:
Wondering how things are going with the trial - I'm on SQL4 so can't try Yash's new gizmo out.
Click to expand...
I tried it but wasn't all that impressed. It basically just scripts the entire database structure and data to an sql file. I'd much prefer a means to do a real backup and restore from SQL Server.
 
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
Click to expand...

Yash, what if anything, has happened with your attempts to get this working for backups? I'm on msql4 and I'm looking for the best backup method too. Thanks - datagrid
 
You must log in or register to reply here.
Back
Top