asked on

Copy One Database to Another

I have a database (let's call it XYZ) and want to copy it on a scheduled basis to another database called XYZ_Archive. I've tried using DTS and inevitably it fails, usually because of some PK and FK issues. I've tried both options of copying just 1) tables and views and 2) Copy Objects between SQL Servers.

I'm getting closer having limited the tables that I am copying, but now it errors out and tells me 'COPY SQL OBJECTS ERROR. The login already has an account under a different user name.'

Amy ideas as to why this is occurring?

What is the best way to accomplish this task. Am I going about it the wrong way?

Ray

CRagsdell

Have you considered replication?

CR

nigelrivett

easiest way is to restore a backup.

You can also transfer using sql-dmo.
example at
www.nigelrivett.com
DMO transfer

Your error is probably occurring because you are copying database users and the login ids are different on the target server.

If you can't do the backup-restore I would go for dmo.

arbert

Replication is quite a pain....DTS really sucks at trying to copy objects--hopefully it gets better in Yukon. You might take a look at setting up log-shipping. Really easy to implement and you just have a stored proc setup on the "archive" database that applies the logfiles on a pre-determined interval.

Brett

arbert

What's the status of this question?

DahNight

This error is because of SQL Server's script generation of the logins for a database. You can replicate this in enterprise manager by selecting you database and right clicking under all tasks choose generate sql scripts. On the options tab choose script database users, logins.

The statement like this will cause your error:

if not exists (select * from dbo.sysusers where name = N'user' and uid < 16382)
EXEC sp_grantdbaccess N'user'

if you take a look at the code for sp_grantdbaccess in the master database you will find that the following code is the error generation point:

-- ERROR IF ALREADY EXISTS UNDER DIFFERENT NAME --
if (not user_sid(user_id(@name_in_db)) = @sid)
begin
raiserror(15063,-1,-1)
return (1)
end

--- to solve the problem i usually make sure that i don't have any logins in the destination server and have everything created from scratch by the script generator (this usually works best in DTS on the copy objects)

DahNight

I have also found that if someone else other than the sa is the true database owner then this will cause your error.

try running this on the source database before running dts:

EXEC sp_changedbowner 'sa'

ASKER CERTIFIED SOLUTION

DahNight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial