Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Backups and restores Expand / Collapse
Posted Tuesday, December 4, 2012 7:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 31, Visits: 329
I have 2 SQL Server 2008 environments - Production and Test. Both are on different domains and cannot speak to each other. I've been tasked to update some of the databases in the Test environment with Production data on a weekly basis. Since the domains cannot communicate with one another I need to manually copy a backup to a share and then restore the data in test. However, my issue is that the test environment has logins specific to that environment and get overwritten when I restore the procuction data. Then I have to go back and manually add the test logins and remove the orphaned production ones. Is there a way to just restore the data in the test environment without affecting the logins in the test environment. Thanks
Post #1392462
Posted Tuesday, December 4, 2012 7:07 AM



Group: General Forum Members
Last Login: Thursday, September 1, 2016 2:56 AM
Points: 5,969, Visits: 6,067
Using backup and restore commands no. SQL has to restore the database how it was at the time it was backed up, which means you will always get orphaned accounts and need to add in the test accounts again.

I would have a job on Test which does the removing adding of users which you just execute every time you restore from Prod.

If the restore is automated, just need to add this step to the end of the restore command.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1392466
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse