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 |
|
RMH1972
Starting Member
11 Posts |
 Posted - 2010-09-02 : 05:47:32
|
Hello,I have a question about an production and test database.At the moment we backup the production database and restore it in the test environment, than we change all security settings and users so that we can use the production database in the test environment.This is a lot of manual work, I like to know if there is a way to do this with a script or another way so it go’s automatically.The users in production and test are not the same , but there is already a database in the test environment that has all the correct security settings so we only have to copy data and store procedures and those things. I hope someone can help me! Greetings,RobertGreetings,Robert |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-02 : 07:04:40
|
| We just copy data, and then only from relevant columns, from PRODUCTION to TEST. As part of that we change EMail addresses - just so we don't accidentally EMail customers from TEST! - although, in the main, we do not copy Customers / Orders / Logging data from PRODUCTION to TEST.We have a script that DelUpSerts the data - so pre-deletes rows that no longer exist in the source, Updates rows that exist in both which have at least one column value that is different, and finally Insert any rows into Destination that only exist in Source.We mechanically generate the scripts to do this, and they are generated with DELETES in Child-Parent order and Updates/Inserts in Parent-Child order. Not foolproof - so the scripts tend to be hand-adjusted to disable FKeys and/or Disable Triggers where necessary - and then re-enable them at the end. |
 |
|
|
RMH1972
Starting Member
11 Posts |
Posted - 2010-09-03 : 05:15:05
|
| Hello Kristen,Thanx for your replay, is is still a lot of work and i was hoping that i there was a button or a program that dit this for me, so i could deligate this to the helpdesk.but this is something i need to do myself.GreetingsRobertGreetings |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-03 : 07:16:54
|
| Trouble is ... it is unlikely that you want to just copy the whole database. In your case you want different logins. In my case I want the data from many tables, but not all - and I want Email addresses obfuscating ... so probably no one solution that suits everyone I'm afraid. |
|
|
|
|
|
|
|
|
copy the production database to a test database