July 13, 2009 at 11:45 pm
My schedule for backup is already stable. Full backups every sunday night, and differential backups from Monday to Saturday nights.
Now I have problem with performing my regular restore process. I want my backup server to be restored everyday. If I will schedule a restore process from SQL Enterprise Manager, my scheduled job will fail if there are any users currently using the database when my restore process is invoked.
Is there any way I can kill the processes (without my presence) that may cause failure to my scheduled restore job?
Thanks.
July 14, 2009 at 12:10 am
Set the database that you are trying to restore over offline before doing the restore.
alter database MyDatabase set offline with rollback immediate
July 14, 2009 at 12:11 am
isabel (7/13/2009)
My schedule for backup is already stable. Full backups every sunday night, and differential backups from Monday to Saturday nights.Now I have problem with performing my regular restore process. I want my backup server to be restored everyday. If I will schedule a restore process from SQL Enterprise Manager, my scheduled job will fail if there are any users currently using the database when my restore process is invoked.
Is there any way I can kill the processes (without my presence) that may cause failure to my scheduled restore job?
Thanks.
Just add an alter database statement right before your restore database from fullbackup.
e.g.
ALTER DATABASE yourdb Set offline WITH ROLLBACK IMMEDIATE
Restore database yourdb
from yourdbFULL_Prod
with MOVE N'yourdb_Data' TO N'....'
....
, MOVE N'yourdb _Log' TO N'....'
, replace
, norecovery
Now restore your latest DIFF backup.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 14, 2009 at 1:15 am
then after my restore, do i have to set my database to online mode? or is it automatic?
July 18, 2009 at 6:17 am
To bring the database to online mode, run this.
alter database yourDBName set online
July 18, 2009 at 10:03 am
I like Pradeep's solution. If allows you to do other maintenance if you wanted to rebuild indexes/statistics, obfuscate data, etc. while keeping people out. you could also send a message out when the process finished.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply