SQL Server Performance Forum – Threads Archive

refreshing Database – emergency

hi,
i have to refresh DB2 in server2 , with DB1 in server1 . Both are different Physical servers. anyone have any idea how to do it ??? its an emergency situation .pls help me

---

use backup/restore. ensure you have adequate disk space on server2 first! use the with move clause if file locations are going to be different. ensure security is sorted on db2 – ensure sql server logins are fixed using sp_changeuserslogin ‘auto_fix’, ‘username’. ensure database recovery mode is set correctly afterwards, ensure backups, dbcc checks etc are scheduled and still working on newly restored copy of DB. Tom Pullen
DBA, Oxfam GB

---

(i.e. backup on server1, copy backup file to server2, then restore onto server2.) Tom Pullen
DBA, Oxfam GB

---

So i had no idea abt wht to do. i though using DTC is a best way .wht u say abt DTC?
thnx a lot for ur advices. pls reply me soon

---

The simplest and fastest way is one recommended bey thomas IMO. It is possible use dts but it is much slower.

---

Hi I have refreshed the DB from Production.
There are 79 Database users((SQLlogins)) for this DB.So do i need to run sp_changeuserslogin ‘auto_fix’, ‘username’ 79 times in this Database?? Please advice . Thanks in advance.

---

yes you do – and preferably ensure the logins already exist before running it. Tom Pullen
DBA, Oxfam GB

---

hi Restoration was successfull.I have one query. 1)EXEC sp_change_users_login ‘Report’
gives name of 4 users
2)select * from sysusers where issqluser=1
gives 79 users Do i need to use sp_chagne_users_login ‘autofix’ with only 4 or with all 79 users.This wil be valuble info i can ever get. Please waitin for any suggestions.

---

In your restored db, run select name from sysusers and check all these are fixed [not bothering with built-in users and groups like dbo, fixed db roles etc]. make this your list to work from – these are the users that need to be associated to logins. If the logins don’t exist .. you need to create them. There’s plenty of documentation on this in Books online, if you care to do some research in there for yourself alongside this diamond advice you’re getting… Tom Pullen
DBA, Oxfam GB

---

Thomas/All<br /><br />I have finished refreshing and fixed al issues with access of users. here are som tips .<br /><br />after refreshing Database,<br /><br />1)<br /> use db<br /> sp_change_users_login ‘report’ <br />&gt;&gt; this will give list al inconsistent users in the refreshed database.<br /><br />2)for each inconsistent users execute…<br /> sp_change_users_login ‘auto_fix’ ,’username'<br /><br />This will solve al the inconsistencies of SQL users.<br /><br />3)use db<br /> select * from sysusers where isntuser=1<br />&gt;&gt;thsi will give al NT users in refreshed database<br /> check if al exist in the SQL server logins .else recreate these logins .<br /><br />Note:this case is for refreshing of DB from one server to other .Use same logic for simple DB refresh within same servers .<br /><br />Thomas special thanks for ur first tip tht u gave me. i really did some R&Ds <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />) .<br /><br />Once again thnks to all.<br /><br /><br /><br /><br /> <br /><br /><br />Rajiv<br />SQL-DBA

---

]]>