Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
OpenUtilities
  • Product Communities
OpenUtilities
OpenUtilities Wiki Backing Up Promis.e Data Automatically
    • Sign In
    • +OpenUtilities
    • Access Service Request Manager
    • +Bentley sisHYD Multi Utility
    • Design Features - promis.e Functionality - Promis.e Standard
    • +OpenUtilities sisNET - English
    • +OpenUtilities Substation
    • Promis.e Paths
    • -Promis.e Wiki
      • +CONNECT Edition - Promis.e
      • About Promis.e
      • +Administration Features - Promis.e
      • +APIs_VBA - Promis.e
      • +Automation Features - Promis.e
      • +Component Features - Promis.e
      • +Component Management - Promis.e
      • +CONNECT Services - Promis.e
      • +Data Manager - Promis.e
      • -Database Platform Support - Promis.e
        • Backing Up Promis.e Data Automatically
        • Converting to an SQL Parts Database
        • +Creating and Updating the Project Database
        • Desired SQL server Not Listed in the available Servers List
        • Disable Auto Shrink to avoid performance degradation
        • Error - Could not find SQL Server instance named BENTLEYECAD
        • Error - Could not find stored procedure
        • Error - Database version does not match the application
        • Error - Index was outside the bounds of the array (Microsoft.SqlServer.smo)
        • Error - Invalid database name - on Project Database Utility
        • Error - Invalid object name
        • Error - Login failed for Ecaduser: Reason: The Password of the account Expired
        • Error - No version resource could be located in database
        • Error - OraOLEDB.Oracle provider is not registered
        • Error - T-SQL execution command line utility stopped working
        • Error - The database is for product... you cannot use it
        • Error at Startup - Error 0 No connection could be made because the target machine actively refused it.
        • Error at Startup - Error 40 Could Not Open a Connection
        • Error at startup - The Type initializer for ECT.ECAD.API.SystemSetup threw an exception
        • Error at Startup - Unrecognized database format, catalog.mdb
        • Error deleting project - transaction log full
        • Error messages encountered due to case sensitive collation of SQL Server
        • Error on backup - Invalid database object reference
        • Error on Project Database Utility - Cannot find the file specified
        • Error running SQL scripts - Incorrect syntax
        • Exception thrown when creating a project due to SQL permissions
        • Exception thrown when opening a page, transaction log is full
        • Finding the SQL Data folder
        • Installing SQL Server
        • Minimum SQL user permissions
        • Project Database Version - Promis.e
        • Rebuild indexes in the SQL project database
        • Rules for Regular Identifiers
        • SQL Server FAQ
        • Troubleshooting Project Database Connection Problems
      • +Display - Promis.e
      • +Drawing Management - Promis.e
      • +Engineering Design Considerations - Promis.e
      • Error - Object reference...General
      • +Find_Replace - Promis.e
      • Finding the Tutorial
      • +Installation_Configuration - Promis.e
      • +Interoperability - Promis.e
      • +Licensing - Promis.e
      • +Maintenance Features - Promis.e
      • +Migration - Promis.e
      • +OS Support - Promis.e
      • +Panel Layout - Promis.e
      • +Performance - Promis.e
      • +PowerPlatform Support - Promis.e
      • +Print_Publish Features - Promis.e
      • +ProjectWise Integration - Promis.e
      • Promis.e Help file
      • +Reports_Output Features - Promis.e
      • +Template Management - Promis.e
      • +Text Tools - Promis.e
      • Training FAQs
      • +Wire Numbering - Promis.e
      • +Wiring Features - Promis.e
      • +Project Management - Promis.e
      • Promis.e Title
      • Working from home with Promise.e
    • Replace Family - Find_Replace - Promis.e
    • SQL Server Login

     
     Questions about this article, topic, or product? Click here. 

    Backing Up Promis.e Data Automatically

      Product(s): Promis.e, Bentley Substation
      Version(s): through 08.11.13.140
      Environment: N/A
      Area: Database Platform Support
      Subarea: N/A

    Background

    It goes without saying that backing up critical data is always a good practice. Promis.e and Bentley Substation data can be protected by creating project backups on demand and by copying the data folder or critical parts of it (catalogs, parts databases, etc.) to a safe place periodically.

    The techniques described by this document are ways to backup the project folders and the SQL project database outside of Promis.e and Bentley Substation (referred to as "the software" for the remainder of this article). If the project folders and the database are backed up at the same time, they both can be restored with no loss of synchronization (no errors). This approach is best achieved by backing up the data automatically at a time when the data is not otherwise being accessed. That is, nobody is using the software or other means to access and manipulate the data. Windows Task Scheduler makes it easy to schedule this task to be performed during non-work hours.

    Note: This guide describes functions and commands in operating systems and software not developed by Bentley Systems, and is provided as reference only. Be sure to test the operation of your backup system before needing to rely on it.

    Project Manager should still be used to create project backup files. Among other things it is useful for:

    • Creating backups of projects between backups of the SQL database and project folders.
    • As a means of creating a final archive of a completed project.
    • Users who cannot or do not want to implement the techniques described in this article.

    Steps to Accomplish

    Microsoft SQL Server

    If non-Express versions of SQL Server are used, SQL Server Management Studio can be used to create and schedule jobs, such as backing up the database. Consult the SQL Server documentation. See also:http://msdn.microsoft.com/en-us/library/ms191239.aspx Schedule the backups of the database to occur at the same time that the project folders are backed up to prevent discrepancies between the drawings and the database.

    Jobs cannot be scheduled with SQL Server Management Studio for SQL Server Express, however.  This section describes how to achieve automatic, periodic database backups for SQL Server Express using an SQL script, a batch file (.bat) file and the Windows Task Scheduler.

    Create the SQL Script

    Using a text editor such as Notepad, create a SQL script file named "backup.sql" containing the following text:

    declare @filename varchar(255)
    set @filename = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.BENTLEYECAD\MSSQL\Backup\Promise' + datename(yy,getdate()) + datename(mm,getdate()) + datename(d,getdate()) + '.bak'
    backup database promise to disk = @filename


    Line 1: declares a variable that will be used in the script.

    Line 2: builds a path and file name for the actual SQL backup file from the string "C::\...\Promise" and the date when the script is run and assigns the file name to the variable declared in the first line. The backup file will be named something like "Promise2018June10.bak" and it will be placed in specified path. Change the path and file name to suit your needs.

    Note: Backing up the data to a different machine than the machine hosting the "live" data is recommended as further protection against data loss, however, this example backs up the database locally and then copies it to a remote machine. This is often easier than the steps necessary to allow SQL Server to write to a remote machine.

    Line 3: backs up a database named "promise" to disk with the file name that was stored in the variable @filename in line 2. Change the name of the database to be backed up to match the name of the database the software is using. Check the Project Database area of the Setup dialog for this information.

    Create the Batch File

    Again using a text editor such as Notepad, create file that contains the following lines and save it with a .bat extension, for example RUN_SQL_BACKUP.BAT:

    sqlcmd -S LOCALHOST\BENTLEYECAD -i c:\backup\backup.sql -o c:\backup\output.txt
    xcopy /e /h /v /y "C:\promis-e Data\My Projects" X:\Backup\projects
    xcopy /d /e /v /y "C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.BENTLEYECAD\MSSQL\Backup" X:\Backup\database


    Line 1: Runs the script created in the previous section. It connects to the BENTLEYECAD instance of the SQL Server running on the local machine, inputs the backup.sql file and performs the instructions it contains, and outputs a result file containing details of the process and if it was successful.

    "BENTLEYECAD" may need to be changed to another instance such as "ECTECAD" or to no instance instead. Check the Setup dialog to verify the instance name.

    Also modify the first line to match the path and filename of the script and the output file.

    Line 2:  copies the contents of the My Projects folder to the remote backup location. This line is optional but ensures the drawing files are backed up at the same time as the SQL database to keep the drawings and database synchronized.  Modify the My Projects path to reflect the actual path where the projects are located. Modify "X:\Backup\projects" to reflect the actual backup path.

    Line 3: copies the contents of the SQL backup folder to the remote backup location. Modify the SQL backup folder path and the remote path to suit your needs.

    To find out more about the XCOPY command, select Start > Run and Type "CMD" to open a DOS command prompt window. Then type "xcopy /?" without the quotes to see the available switches and their uses, or search the internet for "xcopy switches"

    Note: If the paths contain spaces in either line of the script, use quotes around them.

    Test the Files

    Test the operation of the script and batch file and perform a restore before relying on them. Run the .bat file and observe what happens. A .bak file should be created in the path specified in the sql script.

    SQL Server Management Studio (SSMS) can be used to restore the database. SSMS is installed by default when some versions of SQL Server are installed.  Otherwise see the Install SQL Server Management Studio article. Restore the database to a different name with different file names for the database and transaction log files so the original, working database is not disturbed.

    Schedule the Task

    Use Windows Task Scheduler to run the .bat file periodically. Ideally this would be done during non-work hours, perhaps nightly.

    Oracle

    Periodic backups can be scheduled within the Enterprise Manager without manually creating a script. Please consult the Oracle documentation. See also the following link: 
    http://www.oracle.com/technology/obe/10gr2_2day_dba/backup/backup.htm

    Schedule the backups of the database to occur at the same time that the project folders are backed up to prevent discrepancies between the drawings and the database.

    See Also

    Backup Project

    Backup Your Data

    External Links

    How to: Create a New Database From an Existing Database Backup (SQL Server Management Studio)
    http://technet.microsoft.com/en-us/library/ms186390%28v=sql.105%29.aspx

    Using the sqlcmd Utility: 
    http://msdn.microsoft.com/en-us/library/ms180944(SQL.90).aspx

    Download Microsoft SQL Server Management Studio Express 2005:
    http://www.microsoft.com/downloadS/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

    Backing up an Oracle database:
    http://www.oracle.com/technology/obe/10gr2_2day_dba/backup/backup.htm

    Introduction to Backup and Restore Strategies in SQL Server: 
    http://msdn.microsoft.com/en-us/library/ms191239.aspx

      Original Author: Matt_P, Xia Zhang
    • Oracle
    • Backup and Restore Project
    • Database Platform Support
    • promis.e
    • Administration
    • SQL Server
    • how to
    • Bentley Substation
    • en
    • Project Management
    • Share
    • History
    • More
    • Cancel
    • Jordan Stierly Created by Jordan Stierly
    • When: Thu, Jun 3 2010 10:10 AM
    • Matt_P Last revision by Communities MVP Matt_P
    • When: Wed, Mar 14 2018 5:31 PM
    • Revisions: 14
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies