Post reply

How to allow a regular user to run a backup

  • May 25, 2006 at 6:43 am

    #168502

    First, I know the barest minimum about SQL, so please bear with me, and thanks in advance!!!

    Here's what I'm trying to do: I have a SQL 2005 Express server running a financial database. I'm use OSQL scripts as scheduled tasks to run nightly backups, and they work great. I need the accountant to be able to manually run an OSQL script from their workstation, that will create a backup as well. I've written the script, but can't get it to run from a workstation.

    1) Do I need to make an ODBC connection for this? How do I do that?

    2) Do I need to install OSQL on the accountants workstation to be able to run a script on the remote server?

    Both machines are in the same domain and the accountant has full admin rights & NTFS permissions to the target backup folder, and the folder where the scripts reside.

    HELP PLEASE!!! THANX!!!

  • May 26, 2006 at 5:10 am

    #640268

    I think you can just put osql.exe on the workstation and then use the osql switches to have it connect to the SQL Server..

    For example you'll specify the following (can do osql /? from cmd line to see all the switches or lookup online):

    -S ServerName

    -U UserName (to use to connect to SQL)

    -P Password

    or instead of the -U -P you can use -E for windows authentication (trusted connection) if you have SQL setup for it.

    then I imagine you using the -q and specifying the command line query to execute the backups or you could maybe do -i for an input file.. you probably get the idea.

    With no SQL Agent in Express, the Scheduled task method has been a good one for me too - although I am not using osql- I created a little VB app that runs things for me by using SQLDMO to connect to the SQL server (have to use the new backward compatible sqldmo files for this) and then it runs the appropriate sqlmaint.exe command (as the old SQL/MSDE 2000 Agent did.)  Incidentally, sqlmaint is depricated I think but it still works great.

    Oh and speaking of osql, its replacement in SQL 2005 is sqlcmd.exe and seems to accept the same switches (although may have more too).  Osql still works but is depricated too I think.

    And should you need to create an ODBC connection to SQL (don't think you need here)- you can just do that on the workstation in Administrative Tools, OSBC data source.  On the User or System DSN tabs you can add a new one, browse to the SQL driver, specify the SQL server and connection info and that's pretty much it - then you can use that ODBC connection string where needed.

    Good luck.

  • May 26, 2006 at 5:53 am

    #640277

    Per definition, a 'regular user' aren't allowed to take a backup of any database. This is logical, since if anyone could do it, then anyone could snitch a copy of your db and carry it home.

    Your accountant need to login to the server with credentials that are any of:

    -- snip from BOL --

    BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

    -- end snip BOL --

    The method could be script, osql, sqlcmd or whatever, but the thing for the accountant to succeed, is that he uses a login that also has permissions to execute the backup command on the particular database.

    -- edit

    Also note that there is a Copy-Only option for the backup command. This is intended for backups that shouldn't interfere with the normal log chain. If the accountant is taking a normal backup and places this somewhere else than your 'normal' place, then you'll have your backups scattered on several places, so perhaps this option could be of use as well..

    /Kenneth

Viewing 3 posts - 1 through 2 (of 2 total)

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