Re: Need help with copy database

I found the problem. After spending a long day going over and over
everything I decided that sol server was not doing what it should be doing
as I had completely cleaned all possible issues out of the db and was not
even coping logins or permissions to the target db. additionally, I was
coping a 2005 db to a 2005 db. I opened up a support call with MS and after
several hours with the sql engineers, they found that this is a bug in sol
2005 sp1 which had just been written up about 1 week ago. they said it will
be fixed in sp2. I went to a machine that had sql 2005 with OUT sp1 and the
copy db wizard ran fine. Here's my problem now.

I need to copy databases to a remote server. I used to do this using dts in
sql 2k. in every case a new (empty) db is sitting on the remote server and
dts would fill it with tables, data, SPs, etc.

Now the copy db wizard wont work because it wants to delete the target db
and create a new one. I don't have permissions to delete and create a new
db on the target server. So my work around was this:

I created scripts to create everything in the db. these scripts create a
perfect copy of the db. Now I tried to export data to the target db and all
the data went in OK with one little problem. ALL the indentities re-created
themselves so I loose all relationships and data integrity between the
tables. I tried to use entity insert to allow the original PK values to be
inserted, but that doesn't work.

the reason I don't just let the export wizard create the target tables and
then export the data (which works for maintaining data integrity) is because
it doesn't create any keys or identities. I have over 200 tables and I cant
recreate all this stuff everytime I setup a new db.

any good recommendations on how to get a perfect copy of the db on the
remote server?


"blocke917@xxxxxxxxxxx" <blocke917@xxxxxxxxxxx@discussions.microsoft.com>
wrote in message news:8483A446-A8B1-490F-8CD2-A63D12983FA9@xxxxxxxxxxxxxxxx
Moondaddy/Peter Yang

I had a similiar problem when trying to copy a SQL2000 DB to SQL2005 as
well. I wanted to make sure I copied all the logins belonging to the DB
as
well as register the logins in the master under the Security/Login for the
overall SQL server. The first time I ran the copy I got an error and
looked
in the event log for the server SQL was running on. It gave an error
message
stating that the username was invalid. This was a username from the
database
I was copying. Once you run it the first time, you need to refress the
SQL
server instance and you will see that the database you were trying to copy
is
listed but no tables were inserted. Also, the login usernames from the DB
you were copying also got transferred. If you try and run it again
without
deleting the failed copy of the DB and all the username accounts that it
created, you just keep getting additional errors. By the way I was made
sure
the SQL Management Object selection was checked to the database I was
copying
didn't go offline and I had selected for it to copy the login objects too.
I
finally found a work around by running it once and letting it get the
error.
Then the next time I ran it, I didn't select the option to copy the login
objects. Low and behold all the tables, indexes, stored procedures and
specific DB user accounts were copied and it completed in success. I'm
not
sure why this happens but it took me a while to figure out the work around
by
trial and error.

"moondaddy" wrote:

I need to be able to copy a database to a new database. yes I know I can
use backup and restore or detach-attach so please don't recommend those.
I
have a db which was a sql 2k db and I attached it into sql 2005. then I
ran
maintenance plans on it to clean it up. Now I want to copy it to a
remote
server. For reasons, its best in this case to use copy database rather
than
restore or attach. so to start with I am practicing coping it to a new
db
on the same (dev) server to make sure it will work. At first I got lots
of
different errors for views and SPs were it thought objects were not valid
or
didn't exists such as a table even though the table was just fine. I ran
a
script to drop and re-create all views and sps and this problem went
away.
the error log is full of useless text. the one line that says something
understandable is:

The Execution method succeeded, but the number of errors raised (1)
reached
the maximum allowed (1); resulting in failure. This occurs when the
number
of errors reaches the number specified in MaximumErrorCount. Change the
MaximumErrorCount or fix the errors.

but this is of no help. There's NOTHING that tells me what the problem
is.
How can I successfully use the copy database function to do this simple
task? I have seen tons of postings where people are having the same
trouble
and I have seen NO post with a useful answer. Will MS Please step up to
the
plate and deal with this?

--
moondaddy@xxxxxxxxxxxxxxx





.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)