I have an employee working as a data entry operator on Windows 7 PC. I want to create backup of the data and for that I have to stop SQL server while in administrator mode, to move data files to dropbox.

my employee cannot able to stop SQL Server due to limited privileges and hence cannot create backup on its own.

I want to give access to him for only starting and stopping sql server so that he routinely creates backup of the data.

Please suggest ways, I tried Subinacl and that doesn't work on Windows 7.

  • people who vote down the question, please be kind enough and reply and tell me what's wrong with it. – Fawad Aug 27 '15 at 8:19
  • 2
    If I had to take a guess it would be that you're running SQL Server on a desktop OS and using dropbox for backups. – Falcon Momot Aug 27 '15 at 8:44
  • Dropbox is a medium to transfer those .MDF and .LDF files from one office to another and then replace those files with the latest database files. I can only move .MDF and .LDF files when the SQL Server is stopped. – Fawad Aug 27 '15 at 8:51
  • 4
    Aah, then you are probably being downvoted because you aren't doing the backups in a supported way. SQL server supports a better way of doing this that doesn't involve copying the active data files. – Falcon Momot Aug 27 '15 at 8:54
  • 3
    @Fawad We understand what you're doing, and we understand how these ways of working grow over time. But once you ask professionals for help, you'll get an answer that tells you how to do it right. Helping you continue your current practice is something I'm reluctant to do because I understand the inherent flaws in it. I'm sorry if it comes over as condescending, it's not meant to be, we're trying to help. – Reaces Aug 27 '15 at 8:59

Why would you ever want to shut down your database for a backup?
SQL Server has many types of backup, and most of those don't require moving the data files.

What you want to do is create a Copy Only backup of the database, and restore that.
It's a lot less intrusive to your system, and less error prone.

This can be easily done through the SQL Server GUI (SSMS), through the CLI interface, or you can schedule jobs to do it. You could even user PowerShell to do it.

  • because when I move my data file, it say's data file is in use. this is why I have to stop SQL server to move data file, without stopping it does not move. – Fawad Aug 27 '15 at 8:19
  • 2
    @Fawad What you're doing is not a backup. What you're doing is copying your active production database to a different environment by hammering your database server into submission. Look into actual backups such as in the links I provided. – Reaces Aug 27 '15 at 8:20
  • to be more precise, I move those database files to a dropbox, after I go to my second office I take those database files and replace with the ones I have in that office to update as well. I will look into what you have prescribed. – Fawad Aug 27 '15 at 8:23
  • 3
    @Fawad You're being very precise, but you're asking for unusable backups by doing this. Use SSMS or something to make the backup and copy the backup. What you are doing is not supported, and for good reason. – Falcon Momot Aug 27 '15 at 8:55

You haven't said what you're using the second copy of the database for--if you're doing updates in both locations, or whether the second copy is just for reporting, etc. That makes a difference.

If you're just doing reporting in your second office, you might be able to set up log shipping with the database in standby. The copy of the database will be in read-only mode, and you'll get kicked out when it restores, but it'll be automated and won't require any special privileges for your employee.

(I know I haven't addressed the dropbox aspect of the question.)

  • 1
    I'm somewhat embarrassed I didn't think of log shipping first when I read the question! +1 – Reaces Aug 27 '15 at 21:49
  • I gave you an upvote before posting this, @Reaces. :) – Katherine Villyard Aug 28 '15 at 15:36
  • Maybe I incorrectly used the word "Backup" here. but if this thing is possible without stopping SQL Server then please let me know. What I'm trying to do is to copy the database file of our accounting software in my dropbox. I go to my second office, retrieve those files and replace those Datafiles .MDF and .LDF with existing ones on that computer then I start the SQL server at the second office and I get exactly the same copy of data in my accounting software from my first office. – Fawad Aug 29 '15 at 12:11
  • Yes, log shipping doesn't require stopping SQL Server. The disadvantage in this case is that your copy in your second office would be read-only. I don't know if that's a problem for you or not. Log shipping is basically continuously running transaction log backups in one place and restoring them in another, so it's not perfectly synchronous, but neither is what you're doing now. – Katherine Villyard Aug 29 '15 at 13:31
  • A different question inspired this script, but it might suit your needs, too. It's only the restore part; you'd need to take care of the backup part and the set up yourself. katherinevillyard.com/2015/09/23/… – Katherine Villyard Sep 28 '15 at 14:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.