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 |
|
efelito
Constraint Violating Yak Guru
478 Posts |
 Posted - 2003-06-06 : 12:16:17
|
If you have a development database that needs refreshed with production data every month or so how do you handle it? Backup/Restore, DTS transfer, other? If you do Backup/Restore, how do you manage developer security and their rights in the development database? Just curious how everyone maintains their development environment in general... Jeff Banschbach, MCDBA |
|
|
Shastryv
Posting Yak Master
145 Posts |
Posted - 2003-06-06 : 12:21:54
|
| I would go for Backup/Restore and take care the logins with sp_change_users_loginEdited by - shastryv on 06/06/2003 12:22:13 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-06 : 12:48:28
|
| I always use BACKUP/RESTORE especially at this company because the development environment and all of the other environments that we have cannot connect to production. I have to go through a Citrix server to connect to production or walk two buildings over to the server room just to be able to use production. ToI usually create a script that adds in whatever else that I need in development. This script and any other scripts needed runs after the RESTORE completes.Tara |
|
|
|
debug
Starting Member
29 Posts |
Posted - 2003-06-08 : 18:48:19
|
| HI,I rekon if u try this by :1. TAking full backup of the database.2. Restoring it on the production server for the first time3. Creating Schedule tasks( NT or 2000) for Zpping the Full backup and diff backup files. (This job will zipp the full and Diff backup files). Schedule these jobs on monthly basis.4. After the files are zipped , create a NT or 2000 schedule task(Monthly basis)to copy the zip files to the production server.5. On the Production server create a NT or 2000 schdule task for upzipping the files.Please keep a reasonable gap between the schedule time of copying the files from dev server.6. Simultaneously, create a SQL SERVER Schedule Task to restore the Diff and Full backup on the production server.This task should have three steps :1. Restoration of Full Backup2. Restoration of Diff Backup3. sp_change_users_loginThis will take time but would make ur life comfortable and make this task streamlined.Please let me know if there is any confusion.REgards,Deepak UniyalDU |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-06-09 : 06:58:33
|
| Doing the Backup & Restore has a hidden benefit. It pretty much forces source control and management. If you restore the prod backup onto dev, all of the new/in-progress objects on dev will get lost, so your developers will be forced to have those changes scripted out. When it comes time to roll changes to production, you won't (shouldn't) have to spend 3 days trying to figure out what changed ...On the other hand, you could also make sure any insert/update dml gets executed in prod and dev. In a transactional system, this is probably a poor choice. In a reporting type system, where all of your data is updated, say, once a day based on some data feeds (csv's for example) from an external source, you could simply run the feeds into both prod and dev.Jay White{0}Edited by - Page47 on 06/09/2003 07:00:56 |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-06-09 : 11:03:47
|
Thanks for the input guys/gals. I'd say more times than not I end up using Backup/Restore, but there have been a few instances where just transfering data has worked out a little better. I like the idea of keeping all security differences scripted and then running the script immediately after the restore. Thanks Tara. Jeff Banschbach, MCDBA |
|
|
|
|
|
|
|
|
Development Database Administration