PDM Standard Database Backups And Maintenance

Modern Server’s tend to have a great deal of resilience built in to them to reduce the risk of data loss. However in the unlikely event of database corruption or complete hardware failure, it is your responsibility to ensure that you have a disaster recovery plan in place should you need to restore your Vault and recreate it from a backup onto replacement hardware.

PDM Standard comprises two key components. The Vault Database, which contains the vault definition, metadata, references and the history of all files, and the Archive, which contains all the files managed by PDM (and their previous versions).

When considering a Disaster recovery plan you should be backing up the Archive Server settings, the Archives and the Vault Database(s). Unless you have a specific SQL Agent for your Backup solution it is likely you will need to manually create the SQL Backups locally then back up externally along with the Archive folders / settings also being backed up at the same time.



Backing up your Archive Server Settings is important as it contains the user details for accessing the Vault plus all the registry keys you would need to migrate / restore the Vault onto new hardware. This is simple to schedule via the Archive Server Configuration Tool. Under Tools select ‘Backup Settings’, then specify the options required (The Vaults whose settings you want to backup, location of the Backup, schedule for the backup and optionally a password on that backup)

Backing up the Archives is a case of using your standard Backup Tool to create a complete copy of all the files within the Vault. The default location for the Archives is in a ‘Data’ Folder under the SOLIDWORKS PDM ‘Program files’ location. In there you should see a Folder named as per your Vault, which contains 16 folders named 0-9, A-F. This is the Archive for your Vault. It is possible however to change this location when the software is installed.

If you are unsure where the Archive folders are stored, the following Registry key will list the location for each of the 16 folders for the listed Vault.



HKEY_LOCAL_MACHINE\SOFTWARE\SolidWorks\Applications\PDMWorks Enterprise\ArchiveServer\Vaults\[vaultname]\ArchiveTable

Backing up the Database(s)

For a PDM Installation with a single Vault there are two databases that need to be backed up.

·         ConisioMasterDB
·         <Vault> Database – typically SWPDM_VaultName

PDM Standard uses SQL 2014 Express Edition, which does not support “Maintenance Plans” nor the SQL Server Agent service that we would use to automate these within SQL Server Standard Edition or above. What this means for you is that it is not possible to automatically schedule backups and maintenance tasks directly within the Management Studio via said Maintenance Plan.

One possible solution to this is documented below. This is based on two parts that create a backup solution for your PDM Standard Database but also allows for the creation of other tasks relating to the health of your Database.

Instead of the maintenance plans:

·         Download and install the stored procedure, “expressmaint” into the Master Database

Instead of the SQL Server Agent:

·         Create individual SQL Scripts for each task ( Backup, Re-index, Re-organise etc)

·         Use Windows Task Scheduler from your SQL Server to run the maintenance tasks at specified intervals

In this Blog we explain how to use these two parts to create your database backup and maintenance solution.

PLEASE NOTE: The ‘expressmaint’ stored procedure was not written by, nor validated by, Solid Solutions or SOLIDWORKS. Although all tests indicate that this is a valid, widely used and safe toolset we can accept no responsibility for any data loss or corruption resulting from its implementation. I would advise creating a dummy vault / vault database to test these tools in your own environment before implementing into your live Vault Database.

NOTE: In order to allow scripts to be ran via a Scheduled Task the following changes need to be made in the SQL Management Studio.

- Right Click over the server name and click Facets



 ·         Under ‘Surface Area Configuration’

- Set OleAutomationEnabled to True
- Set XPCmdShellEnabled values to True
- Click OK

Further changes may also be required based on the success / failure of the Scheduled Task to execute. This step is discussed later as a troubleshooting exercise.

Installing the stored procedure

·         Launch the SQL Management Studio and connect to your PDM Standard Instance using the System   Administrator (sa) User account. 
- Under System Databases, right click over Master and select 'New Query'.



·         Open the sql_express_Maintenance.sql file into a text editor.
·         Select all the text (Ctrl + A) and copy / paste into the Query window. 


Click 'Execute' (F5)

- Verify that the Stored Procedure has been added to the Master Database by expanding Master/Programmability/Stored Procedures



The stored procedure provides the following features:

·         Full Database Backup
·         Differential Database Backup
·         Log Backup
·         Housekeeping of backup files
·         Database Integrity Checks
·         Database Index Rebuilds
·         Database index Reorganization
·         Report Creation

A complete set of parameters for each feature is included in the attached document

With PDM Standard Databases set to ‘Simple’ Recovery mode by default, we are not concerned about the Differential Database Backup or Log Backups so will focus on a basic ‘Full’ backup. The attached Document to this blog also includes separate scripts and schedules for a Database Rebuild and Reorganisation. Both activities crucial to the heath of your database.



Before starting you should create a Folder on your PDM/SQL Server to contain the Scripts, the Backups and the Report files.

Do not retain the backups on the PDM Server however. Ensure that your preferred Backup solution is taking these files offline to an external backup device / media as part of your standard backup procedures. 

Configuration steps: Daily recurring, full database backup

To configuring a full database backup for you PDM Standard database in SQL Server 2014 Express Edition:

NOTE: You will need to know the name of your Vault Database prior to starting. If you do not know this then open the PDM Administration tool. Right Click over your Vault Name and choose Properties (You may be prompted to log in at this stage) 

Step 1 Create the Script: Open a text editor and copy the following code into it. Change the Database name and the paths for the Backup and Report to the folder you just created, then save it to (e.g.) D:\Backups\EXPRESS scripts\Daily_Full_Backup.sql.

When executed, this will create a full database backup of a database called SWPDM_PDMSeminar to D:\Backups\Backup, verify the backup and write a report to D:\Backups\Reports. Backups are retained in this location for 4 days and reports for 1 week:



Step 2 - On the Server, locate and open the Task Scheduler by clicking on Start menu and typing ‘Schedule Tasks’

Step 3 - Click Create Basic Task to start the Scheduled Task Wizard.



Step 4 - Type a name for the task.

Step 5 - Choose Daily from the scheduling options.



Step 6 - Click Next, specify the information about the time to run the task. Set Start time to an appropriate value when the load on the PDM database is low, set the Recur every option to 1 day, and then click Next.



Step 7 - Choose Start a program from the task to perform list, and then click Next.



Step 8 - Click Browse, browse to SQLCMD.exe (for SQL Express 2014 you can find it here - C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE)



Step 9 - Type the following content to the Add arguments text box (changing script path to what you created earlier and including your SQL Instance name (e.g PDMServer\PDMSTD), then click Next: -S <SERVERNAME\INSTANCE> -i  "D:\Backups\EXPRESS Scripts\Daily_Full_Backup.sql"



Step 10 - Select the checkbox to Open the Advanced Properties for this task and then click Finish.

Step 11 - Verify that the user account linked to the task is a Windows User account that is a SysAdmin for your Database.  This should be the User who installed PDM / SQL Express as they will have been added as a SysAdmin automatically.



 Step 12 - Click OK. If prompted, supply the password for the account again.

Step 13 - Test the created task by right-click on it and select Run. A command prompt windows named SQLCMD has to be opened while the task is running. Check the created backup file and the log files.



Assuming no problems (check the attachment for troubleshooting advice) you can now use the scripts in the attachment to define the other two maintenance tasks typical for a PDM Vault Database.

Download the PDF version of the blog here: Scheduling a daily backup and maintenance for SQL Server Express.pdf

Download SQL File: sql_express_maintanance.zip

Thank you for stopping by and taking the time to read this blog.

Wayne Marshall