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 |
|
kowani1
Starting Member
42 Posts |
 Posted - 2006-12-03 : 20:57:45
|
| Hi All,I need to refresh a copy of my test database with production data every night via a scheduled job, and need to know what the most efficient way to do it is - without impacting sql logins etc.I did create a DTS package to copy the data and objects, however that returned an error and I seemed to also have lost all my views.Please help!!Jungle DBA |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-12-04 : 00:09:41
|
| if it's only data, just dts the data thenbut why do you want to do this? is the data not confidential?--------------------keeping it simple... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-04 : 07:34:56
|
| I think the easiest way of doing this is BACKUP PRODUCTION and RESTORE to TESTYou will have to reinstate the logins, but that only requires a small script. (Do you actually need all the production Logins available on Test? I find it more common that the Test population is either a subset of production, or completely different!)Kristen |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 2006-12-05 : 11:01:13
|
| I have a different set of users on the test database , so when I refresh the test database from the production how can I restore the test users. Is there any script .Thanks |
|
|
|
monty
Posting Yak Master
130 Posts |
Posted - 2006-12-05 : 22:29:09
|
| you dont need a script, as there wont be any orphaned users becoz test db has a diffrent set of users so master db is in sync as it was earlier so why need a script?:-)its me monty |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 2006-12-06 : 01:11:14
|
| But the user logins are becoming invalid. When I try to test the user login throught ODBC , it says the user doesnot exist. So I have to go to the sysuser , delete the user and add him again. The NT user is also dropped when I refresh the Db and have to add him again.Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-06 : 01:16:04
|
| The users in the database will be lost if you restore a different database.If you set up the users just how you want them you can script them with Enterprise Manager, and then re-run that script after you restore the database. (You only need the Database User stuff, not the Server Logins)Kristen |
|
|
|
|
|
|
|
|
Refreshing Test Data With Prod Overnightly