Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ayush
Starting Member
1 Post |
 Posted - 2013-09-08 : 13:36:43
|
| Dear experts,I would greatly appreciate your help on my database "refresh" issue.The scenario:I have 12 databases on a QA server that were restored there from a Production server approx. 2 years ago.Now QAs need to sync those 12 databases with databases on a Prod server; all account logins (and corresponding permissions) have to remain unchanged in QA environment - QAs only need to "refresh" databases so that data is as current as it is in Prod environment.For security reasons there's no (and cannot be) connection between two servers (hence it is not possible to import data with append option), so I had to request DBAs on that Prod server to backup databases and to place backup files in a shared folder (already there).My question is - what is the best way to "refresh" 12 databases in QA environment - is it to delete/drop old databases and restore them from backup files (then what would happen to the current QA server logins?) or is it to try to restore databases from backups without dropping 12 old databases and is this possible, would be data just appended to existing data and current logins stay unchanged ?Thank you in advance for any input. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-09-09 : 14:11:46
|
| The database restore will replace the existing database - no appending or updating of the existing databases.The process you want to follow is:1) Script out QA users from each database with appropriate roles/permissions (may need to script out the roles also).2) Restore the databases3) Script in the QA users for each database with appropriate roles/permissions.The logins on the QA server will not be affected. If the users in the databases already exist in production - you may have an issue with orphaned users. If that occurs, then you need to use sp_change_users_login to tie the logins and users back together. |
 |
|
|
|
|
|
Database refresh in isolated environment