automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You ca
n
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or tes
t
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytim
e
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pa
ss
> various parameters to SP to tell where is source, destination, domain, etc
.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>