April 28, 2008 at 2:50 pm
Hello All,
I have a SQL 2k SP4 server. I have a user that wants to refresh her development database with a copy of the production database on a weekly bases during off hours so that they can do testing.
I have a process setup that involves several steps that works half of the time. My process is as follows:
Production Server
1) Separate SQL DB backup job
2) Windows Scheduled Task – Script to map a drive to the dev server
3) Windows Scheduled Task – Script to copy the DB backup to dev server via mapped drive
Development Server
1) Separate SQL DB restore job
2) Job to remove DB users (DB reflects users from prod server)
3) Job to add DB users (adding users from dev server)
My question is – does anybody know an easier way to achieve this using SQL to refresh the development server with a copy of the production database?
Thanks,
Ronnie
April 28, 2008 at 3:13 pm
Since you have to do your work across servers, you may consider to create a device and backup your database directly to your target server.
April 28, 2008 at 3:20 pm
So you are saying that I can backup the production database to my dev (target) server through SQL?
Do I have to create the mapped drive first in order to schedule the backup or is there some other way it can be done?
April 30, 2008 at 11:13 am
Ronnie,
Yes, You can create a job that makes a backup file directly on your target server which is your colleague's server or computer, she/he can use that backup file to refresh her database. You can do so by mapping but not nessessary. You do so many times at work.
May 1, 2008 at 7:34 pm
If network is not a problem it can be done but sometimes backing up direclty to another server/pc may result to a corrupt backup file. So what you can do is to backup first the database to its own server and copy the backup file later. You can script the backup procedure and also the copy procedure for automation.
"-=Still Learning=-"
Lester Policarpio
May 2, 2008 at 1:45 am
Hi,
I would try to avoid backing up across servers...depending on the size of the database of course.
I done something like this is the past where I backup the db, zip it up and then copy the zip and then unzip and restore. All the standard user checks can then be done through automated scripts.
Regards
Graeme
May 2, 2008 at 7:54 am
Was that all those step automated or manual. I used to do pretty much the same steps except for zipping. I had a SQL job to back up the database, a Windows Scheduled Task to map a network drive and copy the database to the remote server. The problem is if any one of those jobs failed, the whole process failed. And that seemed to have failed more than it worked. Once I got the backup of the database over to the remote server, the restore job I had setup worked everytime. My problem was getting it over to the server.
I thought there may be a better way to do it where my success rate was in the 90 percentile.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply