Promis.e Auto Backup

Hi,

I have tried to set up automatically backups as written at https://communities.bentley.com/products/electrical___instrumentation/w/electrical_and_instrumentation__wiki/3591/backing-up-promis-e-data-automatically.

While I am able to make database backup on local hdd (C:\temp), I am unable to make it on network drive.

1) If UNC is used, I got an error message:

Cannot open backup device '\\192.168.22.100\Backupy_Promise\DatabaseAutoBackup\Promise2018March13.bak'. Operating system error 1326(The user name or password is incorrect.).
Msg 3

2) If mapped drive is used:

Cannot open backup device 'R:\X-Promise\DatabaseAutoBackup\Promise2018March13.bak'. Operating system error 3(The system cannot find the path specified.).

I guess there might be a credential problem, but I can access these folders from Windows Explorer without any issue (Credential Manager saves the user/password for that location). I have even tried creating a public share, but with no success.

My promis.e database uses windows credentials.

I have found: https://blog.sqlauthority.com/2017/02/18/sql-server-cannot-open-backup-device-operating-system-error-1326-logon-failure-unknown-user-name-bad-password/ - which states, that “For a network share to be visible to SQL Server, the share must be mapped as a network drive in the session in which SQL Server is running”. OK. SQL Server is running in my session, Credential manager stores password, I can acess files through Explorer, so where's the problem?

I do not think that storing passwords in bat file is a good idea...

The source above suggest to use mapped drive, while https://sqlbackupandftp.com/blog/how-to-solve-operating-system-error-3 suggest UNC. Where the truth lies?

Best regards, Wojtek

PS - promis.e should offer something like "all projects backup", accesible from command line to allow scheduling.

  • This is a Microsoft SQL issue about using Microsoft tools to backup a SQL database outside of Promis.e, so there will be limited information on this forum.  You will have better luck looking in Microsoft SQL forums for assistance.  I read through several SQL forum posts on this topic to see if I could find an easy solution, and most of them suggest that it is not a good practice to try to run database backups to a network location as apparently Microsoft SQL is rather unforgiving of any possible network glitches, and can corrupt your SQL backup if there is any issue during the backup process.  It is instead recommended that you create the backup file to a local drive and then use a batch file to copy that backup file to your network location  to avoid any possible network glitch during the backup command.  I saw this in numerous posts about this topic on various SQL forums.

    Having said that, I seemed that this error is caused because the SQL Server Account must have the permissions, not just the Windows account.  You might peruse these threads: 

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9e3bcf70-7d47-4ac4-a29f-b61a3bc173a4/error-1326-logon-failure-when-attempting-to-backup-to-remote-server?forum=sqlsecurity

    www.sqlservercentral.com/.../Topic470328-357-1.aspx

  • Hi Tani,

    thanks for your suggestion. It works with creating SQL backup locally and then moving to mapped network drive.

    Regarding Wiki article https://communities.bentley.com/products/electrical___instrumentation/w/electrical_and_instrumentation__wiki/3591/backing-up-promis-e-data-automatically (I can't write a comment there even if signed in):

    - that problem with SQL permissions should be mentioned there,
    - another with xcopy, a "/h" switch should be added to copy system files (coincidentally discussed in another thread today).

    Finally I have managed to make it with scripts below. It's far from perfect, doesn't replace Project Manager backups but maybe somebody will find it useful as a solution as another backup.

    BAT file works that way:
    - deletes local and remote database backup
    - creates new database backup locally and copies it to network drive
    - creates current snapshot of all promis.e files (copy and replace only changed files)

    and leaves a small log after.

    Assuming that my network drive gets nightly incremental backups, I should get everyday's copy of database & files.

    ------------------------------------------------------

    SQL script, backup of two databases:

    ------------------------------------------------------

    declare @filename1 varchar(255)
    set @filename1 = 'C:\PromiseBackup\Database\Promise-akt-' + datename(yy,getdate()) + datename(mm,getdate()) + datename(d,getdate()) + '.bak'
    backup database akt to disk = @filename1

    declare @filename2 varchar(255)
    set @filename2 = 'C:\PromiseBackup\Database\Promise-archive-' + datename(yy,getdate()) + datename(mm,getdate()) + datename(d,getdate()) + '.bak'
    backup database archive to disk = @filename2

    ------------------------------------------------------

    BAT file:

    ------------------------------------------------------

    @echo off

    REM Tool to create current snapshot of promis.e database & files

    SET LocalDir=C:\PromiseBackup
    SET RemoteDir=R:\X-Promise
    SET LogFileName=Archive_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~0,2%%time:~3,2%%time:~6,2%.log

    REM create log file
    ECHO -------------------------------------------------------------------------------------------- >> %LocalDir%\Logs\%LogFileName%
    ECHO LOG FILE FOR AUTO PROMISE BACKUPS                                                               >> %LocalDir%\Logs\%LogFileName%
    ECHO -------------------------------------------------------------------------------------------- >> %LocalDir%\Logs\%LogFileName%

    echo delete current database backups >> %LocalDir%\Logs\%LogFileName%
    del %LocalDir%\Database\* /F /Q >> %LocalDir%\Logs\%LogFileName%
    del %RemoteDir%\DatabaseAutoBackup\* /F /Q >> %LocalDir%\Logs\%LogFileName%


    echo create today's database backup >> %LocalDir%\Logs\%LogFileName%
    sqlcmd -S LOCALHOST\BENTLEYECAD -i C:\PromiseBackup\backup.sql -o C:\PromiseBackup\Database\output.txt

    echo copy database >> %LocalDir%\Logs\%LogFileName%
    xcopy /d /e /v /y /h %LocalDir%\Database %RemoteDir%\DatabaseAutoBackup >> %LocalDir%\Logs\%LogFileName%

    echo copy files (Archive) >> %LocalDir%\Logs\%LogFileName%
    xcopy /d /e /v /y /h "C:\Users\Wojtek\CloudStation\Documents\Promise_Archive" %RemoteDir%\Promise_Archive >> %LocalDir%\Logs\%LogFileName%

    echo copy files (Current) >> %LocalDir%\Logs\%LogFileName%
    xcopy /d /e /v /y /h "C:\Users\Wojtek\CloudStation\Documents\Promise_Current" %RemoteDir%\Promise_Current >> %LocalDir%\Logs\%LogFileName%

    --

    Best regards, Wojtek

    SW: Promis.e 08.11.13.96/23.00.00.46

    Answer Verified By: WojtekM 

  • Nice, Wojtek. I was updating the Backing Up Promis.e Data Automatically  when you replied, and I included some of your suggestions. Thank you!