January 28, 2008 at 9:24 am
I have a production database running SQL 2000 (soon to be upgraded to 2005), and a development copy of that database running on SQL 2005. They are on separate instances, on separate machines. The development instance is an exact copy of the production database - I originally set it up by recreating the security logins from the production server and restoring a full backup of the production database. Every so often I "refresh" the development database by restoring a recent full backup of the production database.
If it's possible, I would like to automate this somehow, but I'm not sure how to go about doing it. I don't want a scheduled job, but I job I can kick off one-off style would be great. I think my process would be first to delete the security logins and development database from the development instance. I would then like to run the script on the production database to recreate the users on another instance.
I would like to run the output of that script on the development database, and then run a restore on the most recent full backup (that happens weekly) of the production database.
Is something like this even possible? Is there any tools (free/open source even?) out there that makes managing different SQL environments/instances/prod vs development easier? Thanks for any help or advice!!
January 28, 2008 at 9:32 am
The automation is the same whether it's a scheduled job or an unscheduled job. You still would create the scripts to perform the restore.
If you have the logins, you can ignore that part (though I'd keep that script handy in case something changed).
I'd do separate job steps for each step you take. Copy the file, restore the backup, fix the users, obfuscate the data, etc. Get each one working manually and then just put them into a job.
There are scripts here on the site that help with automated restores you can look at.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply