expressmaint.exe unc backup solution?

May 19, 2009 at 5:48 PM

I am trying to schedule a backup using expressmaint to write to a unc path. I don't think it matters but the server we are backing up to is a recent flavor of gentoo with samba.

I had this working as two steps but would prefer it be one command. These 2 lines worked correctly using version 1.4.

expressmaint -S (local) -D ALL -T DB -R E:\MRI\MRIBACKUP -RU WEEKS -RV 1 -B E:\MRI\MRIBACKUP -BU DAYS -BV 1 -V

"C:\Program Files\SyncToy 2.0 Beta\SyncToyCmd.exe" -R"Mribkup"

I upgraded to version 1.8 and tried using this command

expressmaint -S (local) -D ALL -T DB -R \\10.0.20.1\mribackup -RU WEEKS -RV 1 -B \\10.0.20.1\mribackup -BU DAYS -BV 1 -V

I use a script to set a timestamp whenever it completes successfully, and scheduled it in task manager running as local administrator.

@ECHO OFF
E:
:All DB Backup
expressmaint -S (local) -D ALL -T DB -R \\10.0.20.1\mribackup -RU WEEKS -RV 1 -B \\10.0.20.1\mribackup -BU DAYS -BV 1 -V
IF ERRORLEVEL 1 GOTO End
E:\wget --no-check-certificate "https://10.0.20.1/pub-cgi/timestamp-manager.pl?mode=set-timestamp&timestamp=mri_db_dump"
:End

It is successfully writing the logs to the server so I know it has write access, but it is reporting errors in the logs and no data is being dumped.

Expressmaint utility v1.8.0.0 , Logged on to SQL Server [DBSERVER] as [DBSERVER\Administrator]
Created by Jasper Smith (www.sqldbatips.com)

Starting backup on 5/19/2009 1:14:51 PM

[1] Database equity2: Backup starting at 5/19/2009 1:14:51 PM
 An exception occurred while executing a Transact-SQL statement or batch.
 Cannot open backup device '\\10.0.20.1\mribackup\equity2\equity2_FullBackup_20090519_1314.bak'. Operating system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.
 Backup failed for Server 'DBSERVER'.
 An exception occurred while executing a Transact-SQL statement or batch.
 Cannot open backup device '\\10.0.20.1\mribackup\equity2\equity2_FullBackup_20090519_1314.bak'. Operating system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.


Expressmaint finished unsuccessfully at 5/19/2009 1:14:51 PM with Return Code(-5)

I have created the file by hand, just to make sure it was not a windows path to long error or something to that effect.

Any help would be appreciated.

 

 

 

 

Coordinator
May 19, 2009 at 6:10 PM

What account is the SQL service running under? This is the account used to generate the backup file and thus the account that needs permission to the remote share. To test the accounts permissions you can connect to the instance using Management Studio and in a new query window run the following

exec xp_cmdshell 'dir \\10.0.20.1\mribackup'

Note that you may need to enable xp_cmdshell first using

exec sp_configure 'xp_cmdshell',1

reconfigure

go

You can switch it off again after the test by setting it to 0

If you get an Access is denied error running the dir cmd then your SQL service account does not have access to the remote folder

May 19, 2009 at 6:44 PM

Sql Server is running as

NT AUTHORITY\NetworkService

I tried to check the permissions but got this error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

I then ran

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

I tested and it is giving an Access is denied error. Is there anyway to specify the user this runs as so that it runs as the local administrator.

The other solution is to set sqlserver to run as local administrator but that may break the MRI program we are using.

Coordinator
May 19, 2009 at 6:58 PM

The SQL Service account doesn't have to be a local admin but it does need to have access rights to the remote share. If the server is in a domain then you can use a domain account (use SQL Configuration Manager tool to change the service account as this set's up all the right permissions etc) or if you want to use the current account then grant access to the remote share (if possible) to DOMAIN\SERVERNAME$ replacing with your domain and server name. Network Service uses the computer account to authenticate to remote shares.

May 19, 2009 at 10:50 PM

Ok, I used the SQL Configuration Manager tool to change it to the local administrator, and (fingers crossed) everything appears fine.

 

Thanks for all the help.