Post reply

Database backup/restore not using enterprise manager

  • November 17, 2005 at 8:08 am

    #166061

    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

     

  • November 17, 2005 at 8:32 am

    #604438

    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

  • November 17, 2005 at 8:34 am

    #604439

    Do they have access to Query Analyzer?


    Growing old is mandatory, growing up is optional

  • November 17, 2005 at 8:39 am

    #604442

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

  • November 17, 2005 at 11:33 am

    #604494

    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

  • November 17, 2005 at 12:38 pm

    #604507

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

  • November 18, 2005 at 9:01 am

    #604663

    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.

  • November 18, 2005 at 10:20 am

    #604691

    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."

  • November 18, 2005 at 10:22 am

    #604692

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

    Juanita 

  • November 18, 2005 at 10:33 am

    #604695

    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."

  • November 18, 2005 at 10:36 am

    #604698

    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