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.