Copy One Database to Another

Posted on 2003-03-17
Medium Priority
Last Modified: 2007-12-19
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?

Question by:ONYX
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8156948
Have you considered replication?

LVL 18

Expert Comment

ID: 8157010
easiest way is to restore a backup.

You can also transfer using sql-dmo.
example at
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.
LVL 34

Expert Comment

ID: 8157230
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.

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

LVL 34

Expert Comment

ID: 8718255
What's the status of this question?

Expert Comment

ID: 8824040
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:

        if (not user_sid(user_id(@name_in_db)) = @sid)
                return (1)

--- 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)  


Expert Comment

ID: 8824095
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'

Accepted Solution

DahNight earned 200 total points
ID: 8824203
I also managed to reproduce it with one other scenerio....

SQL server automatically assigns the logged in user to the database alias dbo when you do a create database.

See if the login that your using to create the database in the destination (this would be the login you use in the second page of the Export Data dialog) already exists in the database your trying to copy as a user.

If so, copy all objects would see that login name as assigned rights, it will try to add that user again to the destination.  Since this user is already "dbo" you get the error that the user is already in the database with a differnet name.  Which is true.  If you open the SYSUsers table you will see the entry for dbo is not ID 1.  Try creating a new user on the destination server with sys admin rights and use this login for DTS when doing your copy.  

Ideally the script that is autogenerated by sql scripts should be this.. so if you want to fix it yourself you could...:

if not exists (select * from master.dbo.sysxlogins ml inner join dbo.sysusers su on ml.sid = su.sid where ml.name = N'user' and su.uid < 16382)

   EXEC sp_grantdbaccess N'user'

The key is that SQL server has built in functionality that inserts a user's SID into the sysusers table on creation -- the logged in user get the dbo spot, and the script generator doesn't take this into account, and will generate an error.  

I usually have a policy of having all DDL created by a seperate user who exists only as a sysadmin specifically for adminning the server and creating databases etc, and did not run into this problem until tonight.    Hopefully this covers enough scenerios to help anyone else looking for an answer.  

--going to sleep now - DahNight



Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question