April 4, 2003 at 9:15 am
Hi,
I am trying to move database from SQL7 to SQL2K using Copy Wizard. I got the message as follows:
Successfully created the share OMWWIZE
… …
Successfully detached the database
Failed to copy file E:\SQL7\Data\abc.mdf
Successfully attached the database
Successfully deleted the share OMWWIZE
I tried couple times but still got same message. Could you please help me on this?
Thank you
April 4, 2003 at 10:21 am
Probably a permissions issue. As you can see from the results, what it does is detach the file, copy it to the target server, then reattach on original server, attach the copied over mdf to the target server. The copy is failing, so nothing to attach. Another possibility could be insufficient space.
Andy
April 4, 2003 at 10:31 am
I am administrator of local and domain on both the source and destination server.
I didn't shut down SQL7 When I copied the DB.
So maybe some users entered data while I made copy. Does it matter?
April 4, 2003 at 10:33 am
Just shutdown the SQL server service in the SQL 7 copy the mdf, ndf and ldf files to the SQL 2000 server and attach the DB in the SQL2K server, then restart the service in the SQL 7
April 5, 2003 at 1:12 pm
I still have problems to copy DB to SQL2K.
The view package log error description is as follows:
Step Error Source: Microsoft Data Transformation Services [DTS] Package
Step Error Description: Unspecified error
Step Error code: 80004005
Step Error Help File: sqldts80.hlp
Step Error Help Context ID: 1100
Do you have any ideas?
Thanks
April 5, 2003 at 6:37 pm
That doesn't help much. If you just want to get it done, there are a couple options. One is to a backup and and restore on the new server. The other is to detach the file manually (sp_detach_db) and copy the file over, reattach with sp_reattach_db.
While not likely, being a domain admin doesn't guarantee access, in some cases you'd have to take ownership and grant yourself permissions.
Andy
April 10, 2003 at 8:06 am
Using either backup/restore or detach/attach, the orphaned users on the destination database will need to be dealt with in some way. Is that correct?
Thanks...
April 18, 2003 at 10:17 am
Correct you will have orphaned users if you do the attach or restore options and the logins are not in Master, if there are few users, you can set the allow changes to be made directly to system catalog, then remove all users in syslogins and start over w permissions.
Can you check to see if the mdf was even created on the destination server? If it is not even created then try to manually create a file of some sort in the directory on the destination server - if you can't you have a permission problem. Also try connecting to the destination server from the old server and see if you can connect to the share using a unc (\\server\share) and try creating a file within the share. You may also want to check the sql server agent account and ensure that it is a domain account that exists on both machines and has permissions to both directories - I belive the agent gets involved in the copy process. You should shut down the sql service on either machine. If the database detaches then it is not an issue of connected users.
April 22, 2003 at 12:17 am
This is how I resync users.
-- login sync
-- print db_name()
use userdb -- User-db
go
print 'print @@servername + '' / '' + db_name()'
print 'go'
go
declare @username varchar(128)
declare @Musername varchar(128)
declare @IsNtName bit
declare @sql_stmt varchar(500)
--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.isntname
FROM sysusers su
left join master.dbo.sysxlogins msu
on upper(su.name) = upper(msu.name)
WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'list of names you want to avoid') --
BEGIN
if @Musername is null
begin
if @IsNtName = 1
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\' + @username + ''')'
print ' begin '
print 'exec sp_grantlogin N''NtDomein**\' + @username + ''''
print 'exec sp_defaultdb N''NtDomein**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account used'
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''''
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''
end
PRINT @sql_stmt
print 'go'
print '--*** Look here: exec stmt in comment !!! ***'
--EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
END --of table-cursor loop
--clean up
CLOSE user_cursor
DEALLOCATE user_cursor
Print '** end User-sync **'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply