Elite Applications Engineer Wayne Marshall explains 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