Peter Yang

I'm not sure if you saw my reply to Moondaddy, but I wrote the following:

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.

I'm sure it has something to do with the additional security included in SQL
2005.

Hope that may help you find out why the problem exists.

HOWEVER,
I have another problem you may be able to answer quickly for me though.
I have created a maintenance plan to back up user databases to a network
share. the share is actual a drive F: of a SAN with .99 TB of space. the
books online say if you back up to disk and the disk is a network share to
use the UNC path which I have. Such as, \\Printman3\BackupF\NOMIENTDB1,
where Ionas4 is the server, Backups is the share, and NOMIENTDB1 is the
folder where I want a subfolder and backup copy of each user database. the
SQL Server Agent service is run with a domain account that has full admin
priviledges on both servers, the SQL server and the Printman3 server. When I
execute the plan, the log states

**********************************************
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "NOMIENTDB1".
Maintenance Plan: ASSL Backup
Duration: 00:00:00
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Full) (NOMIENTDB1)
Backup Database on Local server connection
Databases: ASSL,qmdb,ReportServer,ReportServerTempDB
Type: Full
Append existing
Task start: 9/14/2006 11:30 AM.
Task end: 9/14/2006 11:30 AM.
Failed:(-1073548784) Executing the query "EXECUTE
master.dbo.xp_create_subdir N'\\\\Printman3\\BackupF\\NOMIENTDB1\\ASSL'
" failed with the following error: "xp_create_subdir() returned error 5,
'Access is denied.'". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or
connection not established correctly.

Command:EXECUTE master.dbo.xp_create_subdir
N''\\Printman3\BackupF\NOMIENTDB1\ASSL''
GO
EXECUTE master.dbo.xp_create_subdir N''\\Printman3\BackupF\NOMIENTDB1\qmdb''
GO
EXECUTE master.dbo.xp_create_subdir
N''\\Printman3\BackupF\NOMIENTDB1\ReportServer''
GO
EXECUTE master.dbo.xp_create_subdir
N''\\Printman3\BackupF\NOMIENTDB1\ReportServerTempDB''
GO
BACKUP DATABASE [ASSL] TO DISK =
N''\\Printman3\BackupF\NOMIENTDB1\ASSL\ASSL_backup_200609141130.bak'' WITH
NOFORMAT, NOINIT, NAME = N''ASSL_backup_20060914113052'', SKIP, REWIND,
NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N''ASSL'' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N''ASSL'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information
for database ''''ASSL'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK =
N''\\Printman3\BackupF\NOMIENTDB1\ASSL\ASSL_backup_200609141130.bak'' WITH
FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
BACKUP DATABASE [qmdb] TO DISK =
N''\\Printman3\BackupF\NOMIENTDB1\qmdb\qmdb_backup_200609141130.bak'' WITH
NOFORMAT, NOINIT, NAME = N''qmdb_backup_20060914113052'', SKIP, REWIND,
NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N''qmdb'' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N''qmdb'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information
for database ''''qmdb'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK =
N''\\Printman3\BackupF\NOMIENTDB1\qmdb\qmdb_backup_200609141130.bak'' WITH
FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
BACKUP DATABASE [ReportServer] TO DISK =
N''\\Printman3\BackupF\NOMIENTDB1\ReportServer\ReportServer_backup_200609141130.bak''
WITH NOFORMAT, NOINIT, NAME = N''ReportServer_backup_20060914113052'', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N''ReportServer'' and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N''ReportServer'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information
for database ''''ReportServer'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK =
N''\\Printman3\BackupF\NOMIENTDB1\ReportServer\ReportServer_backup_200609141130.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
BACKUP DATABASE [ReportServerTempDB] TO DISK =
N''\\Printman3\BackupF\NOMIENTDB1\ReportServerTempDB\ReportServerTempDB_backup_200609141130.bak''
WITH NOFORMAT, NOINIT, NAME = N''ReportServerTempDB_backup_20060914113052'',
SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N''ReportServerTempDB'' and backup_set_id=(select
max(backup_set_id) from msdb..backupset where
database_name=N''ReportServerTempDB'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information
for database ''''ReportServerTempDB'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK =
N''\\Printman3\BackupF\NOMIENTDB1\ReportServerTempDB\ReportServerTempDB_backup_200609141130.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
*********************************

Hope you can help. I'm pulling my hair out and there is not much left.

Thanks,
Brian Locke
blocke917@hotmail.com
bjlocke@nomi.med.navy.mil

Hello,

Since there is known issue in sp1 in copy database wizard, you may want to
consider create a snapshot replication between 2 databases so that they
could copy data without dropping identity of columns.

Also, based on my test, you may try the following workaround:

1. In Management studio, right click the DB-> Export-> Write Query to
specify the data to transfer, type select * from sourcetbl

2. Click Edit mapping on "select source tables and view" page, select "drop
and re-create destination table",

3. Click Edit SQL, and add IDENTITY(1,1) or others property to the column

Sorry for any inconvenience brought. If you have further questions, please
feel free to let's know.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

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@hotmail.com" <blocke917@hotmail.com@discussions.microsoft.com>
[quoted text, click to view]