data refresh automating on MS Sql 2005

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.,

__________________
Shaalini.S
Be the Best of Whatever you are...

Hi,

Here's a few questions I have (and this serves to test out neven's new List hack to YAF):

Do you need to do a full "data refresh" at the test server? In other words, do you basically delete the old database and replace it with a new copy?

Can you change structure of the tables of the production database to add a "Status" type of column?

Hi,

We usually take latest backup copy of production database and then restore it in development/test server, overwriting the existing one. After that, map the users/logins.

__________________
Shaalini.S
Be the Best of Whatever you are...

Hi,

you're having trouble with users not mapping up to logins, eh? Having to resort to using sp_changeuserslogin? Ugggh! There are better ways

In your case, I think that the backup/restore is the perfect choice. Had you only needed certain parts of the database then I would've suggested SSIS and other techniques. Backup/restore is the fastest way and the easiest to manage. I have a few backup-related stored procedures that I use (in my SQL Server scripts) to do this very thing.

You'll also want to check out sp_helprevlogin for SQL Server - it may prevent you from needing to run sp_changeuserslogin ever again