SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Database refresh in isolated environment
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ayush
Starting Member

1 Posts

Posted - 09/08/2013 :  13:36:43  Show Profile  Reply with Quote
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

USA
790 Posts

Posted - 09/09/2013 :  14:11:46  Show Profile  Reply with Quote
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 databases
3) 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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000