March 7, 2006 at 10:07 am
I have spent a week trying to get this done and reading every forum and whitepaper I could find, but still can't make this work.
I need to move a database from one server to another. Both servers are hosted meaning that I do not have permissions for some of the tasks that some suggest. (backup and restore, for instance). Because of hosting constraints, the two databases cannot have the same name and the database owner must be specified differently. I do not need to transfer users only tables and stored procedures.
I have been able to construct a DTS package to transfer the tables, but am getting errors transferring the stored procedures.
Can anyone point me to an article that might cover this, or help me through this process. Both are SQL Server 2000.
March 7, 2006 at 9:19 pm
Wht exactly the error is and can you tell me how you are implementing/calling your DTS package?
March 8, 2006 at 3:45 pm
Instead of using DTS, you may have more success just scripting the stored procedures. I would script all database objects, including tables, and just use DTS to copy the data. A quick and easy way to generate the scripts is to use Enterprise Manager, right click the database, and select All Tasks ... Generate SQL Scripts.
Either way, options are available for (a) copying database users and roles, and (b) copying object-level permissions. The problem with the first option is that while the roles should copy sucessfully, the users may have problems because they probably won't match an existing login and you probably don't have rights to create new logins. So if your permissions are all granted to roles you'll be ok; if you granted roles to users directly you may have problems.
You may have another problem if all your objects are owned by dbo, but you aren't dbo and aren't granted db_owner permissions. In this case you may need the hosting DBA for the target server to run the script or DTS package for you.
Remember that you will probably have the same problems in the future when you try to do maintenance and enhancements. So you may want to figure out the best method now and set up your process to continue to use the same method in the future.
David Lathrop
DBA
WA Dept of Health
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply