May 14, 2004 at 9:00 am
Hi,
I have a requirement to fully automate the backup of DB from Production to test server.
The issue I am facing is that, since the web server is connected to DB, before starting the restore I have to manually stop web services. Otherwise, restore will give error "ODBC SQL STATE: 42000".
Is there any solution for this ?
Krishna
May 14, 2004 at 1:18 pm
I don't do this, but have you considered REPLICATION or Log Shipping instead of backups?
You can find out more about those in the Books OnLine, or do a search on this site.
-SQLBill
May 19, 2004 at 8:40 am
Log shipping or replication would be better. But if you must do it this way, the reason this is happening is because the db is still in use.
you can try running this script first, to put the db into single user mode:
EXEC sp_dboption 'yourDatabase', 'single user', 'TRUE'
then run your restore statement, then turn single user back off
EXEC sp_dboption 'yourDatabase', 'single user', 'FALSE'
You can put this in a sql agent job around your restore statement to automate it. However, sometimes this command won't work, if there is current activity at the moment it tries to run. You can either have it retry a specified number of times, or as a backup you could within the same job run an os command (cmdExec) to stop webservices on the server, as long as the web server is on the same network (subnet).
hope that helps,
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply