Database backup/restore not using enterprise manager

  • HI. We have a backup that has to be performed before an update during the day to a database but it is not run on a scheduled basis. Currently the users calls me, the DBA, to backup the database before their update during the day. No problem, i can do this with EM very easily. and if a restored is needed, i also do that with EM. I want our operators (in the computer room)to be able to perform this task but they do not have EM installed on their pc and I really didn't want to give them Enterprise Manager. With no enterprise manager on their machine what can i set them up with to do the following: 1) backup the user database, 2) verify the backup, 3) if a restore is necessary, check to see that users are not logged on and 4) restore the database. 

    Maybe I'm on the wrong track and everyone gives the operators in their computer room, enterprise manager and i'm fighting an unnecessasry battle.

    But if that is not the case, I would appreciate any ideas. All other database backups are done through maintenance plans so no user intervention is necessary.

    thanks,

    Juanita

     

  • First production database restores are a DBA job - always; never the operator or anyone else. 

    If there is a need for someone to do a backup just write up the backup statement using OSQL and put it in a BAT file for the operators to execute.  Make sure the operators are using an id that has db_bakupoperator authority in that database (certainly not sysadmin).  They could do it via EM as well.  If they have the appropriate authority then there is no problem.

    Francis

  • Do they have access to Query Analyzer?


    Growing old is mandatory, growing up is optional

  • At this time they do not have query analyzer installed on their pc.

  • I like fhanlon's idea of scripting osql commands into .bat files.  This not only makes it easy for your operators, it allows you to control the process.  Using a .bat file lets you determine the backup location so in the event of a restore (which you should always do) , you know right where the latest backup is located.  The last thing you want to do is rely on someone else to keep all of your backups in the same place.  If you give them EM or even QA, your backup files will be strewn accross the network and their workstations. 

    Create a .bat file for each specific function that you want them to do.  Use Windows security to only give them read/execute rights on the .bat files. 

    I would not script a restore for them.  What if, instead of clicking on the backup.bat you give them, they click on the restore.bat?  Operators are operators, they are not DBA's.  You need to be 100% responsible for your data. 

     

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • thank you, thank you !!!   it's so great to have all you guys out there in sql land!! 

  • I think I would build the restore in a Job, and have the User file call the job.  I (DBA) would want email notification or paged or text'd.  The job would delay the restore for an amount of time for me to stop it or let it go - depending on the what the notification I got - I may let it run.

    Good question though...excellent food for thought.

  • Jus t one more thought ... what about a scheduled task ? You can create one without a schedule and just execute it on demand. I do it all the time for ADHOC Backups/Restores. The added benefit is that you have an execution history (which auditors just love).

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • HI.. Can you do this without enterprise manager? how would that work?

    Juanita 

  • Using either ISQLW or OSQL and invoking sp_start_job.

    From BOL:

    sp_start_job [@job_name =] 'job_name' | [@job_id =] job_id

        [,[@error_flag =] error_flag]

        [,[@server_name =] 'server_name']

        [,[@step_name =] 'step_name']

        [,[@output_flag =] output_flag]

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • thank you very much. These are areas I have not even ventured into.

    Juanita

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply