SQL Server Performance Forum – Threads Archive

Which is a better option

Hi!
Which is a better option among these(a & b) while moving databases from one machine to another.
a)detach-copy to destination-attach
b)Backup-restoring on destination
Performance should be best. Both the machine have SQL Server 2000 with SP3.Only the OS is different.The source has win 2000 and dest has win2003.

Both options will work with no issues.
Option A will have the downtime on source server to detach. Option B is ideal as the backup can be easily restored to the destination server when the SQL versions are identical. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Even i prefer OptionB taking backup and restoring it.
but as said by satya both will work with no performane issues.

But in the option (b) Backup-restoring on destination iam getting login problem how to over come the problem?? Raj

after refreshing Database, 1)
use db
sp_change_users_login ‘report’
>> this will give list al inconsistent users in the refreshed database. 2)for each inconsistent users execute…
sp_change_users_login ‘auto_fix’ ,’username’ This will solve al the inconsistencies of SQL users. 3)use db
select * from sysusers where isntuser=1
>>thsi will give al NT users in refreshed database
check if al exist in the SQL server logins .else recreate these logins . Note:this case is for refreshing of DB from one server to other .Use same logic for simple DB refresh within same servers .

]]>