RE: Need help with copy database

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@xxxxxxxxxxx
bjlocke@xxxxxxxxxxxxxxxxx


"Peter Yang [MSFT]" wrote:

Hello Arnie,

I understand that you encounter lots of different errors when you try to
use copy database method to copy database to another server. this issue
occurs after you attached the database from a SQL 2k server to this 2005
server.

From the error descritption, it seems that you have resolved the issue by
droping/recreating all views and sp2. However, you'd like to know how this
issue occurs in the first place and how should to it be handled. If I'm
off-base, please let's know.

To know the issue better, plase provide us the following information:

1. Did you select "Use the detach and attach method" or "Use the SQL
managment Object method"?

2. You may send the appliation event log on to me at petery@xxxxxxxxxxxxx
for reviewing.

3. If you create a new blank database, and some new tables/views on it,
does the issue occur with the new database?

4. We can use SQL Server Profiler to help determine what is happening. If
you can start a profiler trae on both the source and destination and then
start the Copy Database Wizard we can capture the commands that are running
and see where error is occurring. Please capture the following counters:

SQL Server 2005
Stored Procedures
SP:Completed
SP:Starting
SP:StmtCompleted
SP:StmtStarting
TSQL
SQL:BatchCompleted
SQL:BatchStarting
SQL:StmtCompleted
SQL:StmtStarting
Errors and Warnings
Attention
Eventlog
Exception

You will need to check the Show All Events check box on the Events
Selection tab to see these events

I suspect the original views/sp2 error is related to schema/permissions
issue. Since the database was detached from a SQL 2000 server, the objects
might be owned by some orphaned database users which do not exist on the
new server and it is not connectedd to the default schema on the new
server. Therefore, the object referenced may not match the actual name. You
may want to check if the dbo is mapped to a proper login on the database.
Also, a new database test may give use more clues to narrow down the issue.


Also, please make sure SQL Server agent on the destination is using a
domain user account since it is necessary to copy files from source on
shared folders.

If you have any update, please let's know. We look forward to your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.


.

Relevant Pages