Data sync and data loss problems

[ericrep]

Dan -

Now I'm running into all kinds of Windows security/permissions issues that I haven't dealt with before. Can you help?

1) I'm using Second Copy for my backups. It has an option to run a program before running a backup profile and an option to run a program after running a backup profile. So I created two batch files, one with the net stop command and one with the net start command, and entered those in Second Copy. But when I tried to run the backup, I got error messages that I eventually figured out meant that the batch files needed administrator permissions to run.

2) My next step in testing was to run the two batch files from Windows Explorer using the "Run as Administrator" option. That worked...I was able to stop the service and restart it.

3) So I edited my backup profile to remove the before and after programs. Then I manually stopped the service, and tried to run the backup. I still got error messages that some of the files were in use.

4) Still trying to figure out what was going on, I next tried manually stopping the service, then manually copying the data folder in Windows Explorer. Even with this approach, I got File Access Denied messages for four of the files (tempdb.mdf, templog.ldf, VueMinder.mdf, and VueMinder_log.ldf) saying that "You'll need to provide administrator permission to copy this file".

Using the manual method, I was able to click OK and finally back up the files. But I'm really hoping there is a way to automate this. (I only have one user account on my computer so it is an administrator account.)

Thanks,
Eric

[VueSoft]

Hi Eric, I think a better approach would be to use SQL commands to perform the backup, which is the "correct" way of doing it. Here's a link that might help (see the section titled "Automating backups using sqlcmd").
http://www.sqldbatips.com/showarticle.asp?ID=27

Basically, you'd create a SQL script containing the following (replace the backup destination folder with where ever you want to store the backup):

Code: Select all

BACKUP DATABASE [VueMinder] TO  DISK = N'G:\Backup\VueMinder.bak' WITH NOFORMAT, NOINIT,  NAME = N'VueMinder-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

The script can be passed to sqlcmd, like this:
sqlcmd -S.\SQLExpress -i"c:\backup scripts\userfullbackup.sql"

If that sounds too involved, there's a free tool that can do this for you. Note, I've never used this tool, so I can't vouch for it.
http://sqlbackupandftp.com/

[ericrep]

Dan -
The sqlbackupandftp software seems to be working. Thank you!
Eric