October 19, 2006 at 7:12 am
I want to refresh a DEV database with Production data and it should be automated so that this happens every 15 days. The problem that i am facing is that my boss does not want the DBA's to support this actvity. If in case the scheduled (DTS package or a Copy database package) job fails, the application team should be able to restart that job.
The other issue is that the application team does not want a couple of tables to be refreshed, so Copy database Wizard does not work. We cannot give sysadmin right to the application team. we can give a database creator role to the application team.
Please give me suggestion on how to handles this problem. Any suggestions are welcome. If you have a script please post it.
Thank you
October 19, 2006 at 9:23 am
Here's how I'd do this, though I'm not a fan of developers maintaining databases.
1. Create a DTS package with steps that disable or drop foreign keys, delete from the destination tables (if you want them to truncate tables, add them to the db_ddladmin role in the destination database) and import data from the production tables.
2. Schedule the package. That creates a job.
3. Grant the application team EXECUTE permissions for sp_help_job, sp_help_jobhistory, sp_help_jobschedule, sp_help_jobstep, sp_sqlagent_refresh_job, sp_startjob in msdb. You might even be able to add them to the TargetServersRole in msdb.
Greg
Greg
October 19, 2006 at 9:28 am
Thank you very much Greg. I also don't believe in developer having access to all of these but have to follow orders. I will try to implement this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply