Scheduled backup for the PDXpert PLM database and file library

Last update 2020-06-26

Topic contents

Applies to PDXpert PLM server software release 7.0 and later.

• How PDXpert stores your product data
• Preparing your server & backup tools
• Identifying the ShadowSpawn arguments
• Scheduling the primary backup
• Archiving your backup files

Using an automated backup process

To ensure that your data is safe, use an automated backup procedure that saves the PDXpert database and the library.

The PDXpert PLM system maintains product data within a Microsoft SQL Server database, and design file attachments within a separate library folder in the Windows file system. The PDXpert Application Server console provides administrators with single-click backup of both database and library into one .PDXZ backup file.

As your database and especially your file library grows, relying on PDXpert's compressed .PDXZ backup file becomes less practical. A compressed file increases your data's vulnerability because the backup file itself may become corrupted, thereby making its contents no longer accessible. Moreover, the backup time becomes longer, and the manual backup process requires consistent attention.

Your best choice is usually commercial backup software. It's professionally developed and thoroughly tested for a wide range of situations. It often includes transaction logging, error handling, email notifications, user documentation, and a community of professional users. Commercial backup software is worth the price. Really.

However, if you choose not to use a commercial backup tool, you can apply the methods in this application note to develop your own automated backups.

This procedure describes a general method that must be assessed and possibly modified by an IT specialist to accommodate your specific hardware and software environment. Code examples are provided for use at your sole risk. Backup files produced using this, or any other, procedure should be regularly validated to ensure that your data can be correctly restored in the event of a computer failure.

You must be logged into a Windows Administrator account on your server computer to apply this procedure.

How PDXpert stores your product data§

PDXpert saves product data in a Microsoft SQL Server database. The database includes all document, part and change form data; collection members and system rules; software license key; user log-in account credentials; and certain file attributes such as filename and attachment object. Separately, revision and item file attachments are saved outside of the database, in the Windows file system.

The database and library files, as well as other data, are saved in subfolders under a single \Data folder, typically located at C:\ProgramData\PDXpert\Data. This backup procedure replicates the PDXpert file structure to another location, which can then be copied onto an external device for safekeeping.

• The \Data\Database folder contains only the SQL Server .mdf and .ldf database files, and grows with the total number of parts, documents, BOMs and change forms that you have in your system. If your system has 10,000 indexed items and the database folder size is now 50MB, then you can estimate how big it will grow based on item records you'll add each year.

• The \Data\Library folder contains all file attachments, and its size depends entirely on the number and size of the files you attach. The folder size is simply the sum of all file attachments. (Library files are not compressed.) So, if 2500 file attachments are added each year, and the average file size is 1.4MB, then the library folder will grow at the rate of about 3.5GB per year. Of course, you should adjust this estimate based on your own system's expected file count and average file size, including files imported to initialize the system.

  A very large library may consume hundreds of gigabytes while the database remains just a few gigabytes.

• All other folders are created, maintained and (if necessary) recreated by PDXpert. These may be ignored in your backup if you wish to modify this procedure.

In most PDXpert systems, no changes are made to the PDXpert service configuration file. However, if you do make changes to this file, it should be saved for recovery. The easiest solution is to attach the configuration file to a PDXpert document record, which is then included in the system backup. Or, you can edit the backup script to copy the configuration file.

Preparing your server & backup tools§

There are several elements of your backup procedure, and each element uses a specific software utility.

• ShadowSpawn is an open source utility that obtains a read-only snapshot of your product database even if SQL Server has it locked. It uses the Microsoft C++ framework.
• RoboCopy replicates the SQL Server database snapshot, as well as all of the PDXpert library files, into a new folder. The RoboCopy.exe application is typically located in the C:\Windows\System32 folder. RoboCopy documentation is available in the Windows-Server Robocopy.
• Windows Task Manager will schedule the backup process. It's found in the Administrative Tools collection within Windows Control Panel.
• Windows PowerShell is a scripting environment used to create time-stamped archive folders. The PowerShell.exe application is located in the C:\Windows\System32 folder. It can also be downloaded from Microsoft and installed.

To prepare your server computer:

1. Ensure that the Microsoft C++ library has been installed on your server computer. The file vcredist.exe is often installed with other applications (e.g., Visual Studio and SolidWorks). It can be downloaded from the ShadowSpawn project site or directly from Microsoft (32-bit or 64-bit) and then installed.
2. Create a new folder for the ShadowSpawn executable, such as C:\Program Files\ShadowSpawn\ Download and save the ShadowSpawn software. If you downloaded a .zip file, extract the contents into the folder. ShadowSpawn runs "as is"; you don't need to install the program.

Identifying the ShadowSpawn arguments§

To create your scheduled task, identify the data source and destination locations, and an unused device letter:

• On your server computer, locate the PDXpert data folder. The default location is C:\ProgramData\PDXpert\Data but should be verified by checking the current Data Directory entry on the PDXpert Application Server. This is the source location of your data.

• Identify the folder where the source image will be replicated. This destination location must be a local internal (not USB or external) disk drive on the same server computer*. It's good practice to replicate the source data onto a different physical device than your data source.

  * This is not an absolute requirement, but non-local destinations require configuration beyond the scope of this application note. For more info, search the internet for "sql server backup to network share".

• Note drives that are in use on your server computer, and which drive letters are unused and available. The shadow copy will be saved to virtual drive, or vdrive, using an available drive letter, such as Q:.

Scheduling the primary backup§

To schedule the backup:

1. From the Windows Start menu, open the Control Panel, open Administrative Tools, and then open the Task Scheduler utility.
2. On the right side panel of the Task Scheduler window, click on the Create Task... command. The Create Task window opens.
3. On the General tab, assign a task Name, such as PDXpert backup. At the bottom of the panel, mark the Run with highest privileges checkbox.
4. On the Triggers tab, click the New... button to open the New Trigger window. Configure the schedule settings (such as selecting Daily and setting the start time). Click the OK button when you're done.
5. On the Actions tab, click the New... button to open the New Action window:

   1. Select the Action:Start a program.

   2. Click the Browse... button to select ShadowSpawn.exe from the C:\Program Files\ShadowSpawn folder.

   3. Use the information collected in the preceding section to copy the source into the vdrive, and then from the vdrive to the destination (copying all subfolders).

      source vdrive robocopy vdrive\ destination /e

      Enter the ShadowSpawn arguments into the Add arguments (optional): textbox. For example:

      C:\ProgramData\PDXpert\Data\ Q: robocopy Q:\ D:\Backup /e

      Click the OK button to save the action.

   If you prefer to log the outcome, replace steps B & C with a PowerShell script:

   $result = drive:\path\ShadowSpawn.exe source vdrive robocopy vdrive\ destination /e | out-string

   if ($result.Contains("Shadowing successfully completed."))

   {write-eventlog -logname "Backup Scripts" -source "PDXpert Copy" -EntryType Information -EventId 1 -Message $result}

   else

   {write-eventlog -logname "Backup Scripts" -source "PDXpert Copy" -EntryType Error -EventId 1 -Message $result}

6. Configure other scheduled task conditions and settings as appropriate for your environment.
7. Click the OK button to save the task.
8. To test your backup, select the PDXpert backup task in the upper panel of the Task Scheduler window, and click the Run command in the right Selected Item panel. Your primary backup location should have a database & library image.

This copies the entire backup image; it is not a differential copy. Monitor your destination location to ensure there's always sufficient space for your backup data.

Archiving your backup files§

The procedure above creates one consistent database and library dataset. It does not, however, protect your data from catastrophic primary backup device failures or ensure that you have multiple valid backups. You should retain multiple copies of your replicated data on a secure secondary device.

Many secondary backup devices have their own procedures and tools for copying data from the primary device. If you're using an external disk, USB drive, or cloud storage solution, check whether any tools are provided for scheduling archival backups. These tools may include useful features like email error notifications, automated error recovery, and advanced storage management and compression options.

However, you can also create your own archive process based on techniques described in this section.

Creating a task to save time-stamped archives

This task calls RoboCopy from a Windows PowerShell script, which also creates a time-stamped folder on the secondary backup destination. Although this example is executed by the previously-created primary backup task, the action can also be scheduled separately.

This example script doesn't include error handling. The robocopy.log file, which is created within the archive destination, can be reviewed for errors.

1. If the current machine has never executed a PowerShell script, you must enable scripting. Open a command prompt, and type:

   C:\>powershell

   PS C:\> Set-ExecutionPolicy Unrestricted -Scope CurrentUser

   PS C:\> Exit

   You can replace the Unrestricted argument with a different execution policy. If you do this, create the PowerShell file yourself as described in the next steps.

2. Download (Save As...) this PowerShell archive script to a convenient folder, such as C:\Scripts.
3. If your PowerShell execution policy is unrestricted, then rename the downloaded file to archive.ps1. If you're using a different execution policy, then copy the script contents into a new script file that you name as archive.ps1.
4. Open the previously-saved scheduled Windows task (e.g., PDXpert backup).
5. On the Actions tab, click the New... button to open the New Action window:
   1. Select the Action:Start a program.
   2. In the Program/script: textbox, enter powershell.exe

   3. You'll be copying the primary backup image to the secondary location, and specifying the count of archive images that should be retained.

      C:\Scripts\archive.ps1 primary secondary count

      Enter these PowerShell arguments into the Add arguments (optional): textbox. For example:

      C:\Scripts\archive.ps1 D:\Backup E:\Archive 30

      Click the OK button to save the action.

6. To test your backup, select the PDXpert backup task in the upper panel of the Task Scheduler window, and click the Run command in the right Selected Item panel. The contents of your primary backup location should be updated, and also appear in the time-stamped secondary backup location (the folder name's date/time format is YYYY-MM-DDThhmmss).

This copies the entire backup image; it is not a differential copy. Monitor your destination drive(s) to ensure there's always sufficient space for your archival data.

Archive depth§

You can design a more sophisticated archival process by using a backup "ladder" of frequent backups, interleaved with less frequent backups saved in parallel folders.

For example, you could schedule several independent tasks to maintain a multi-year history of data with a reasonable degree of granularity:

• Back up the production data into your primary backup device based on your daily changes.
• Each day, archive the primary device's backup into a secondary device's \Daily\ folder (perhaps overnight), with a maximum count of, say, 10 images.
• Each week, archive the primary device's backup into a secondary device's \Weekly\ folder (for example, every Saturday), with a maximum count of 6 images.
• Every month, archive the primary device's backup into a secondary device's \Monthly\ folder (for example, the first Sunday of the month), with a maximum count of 14 images.
• Every year, archive the primary device's backup into a secondary device's \Yearly\ folder (for example, the second Sunday of the year), with a maximum count of 10 images.
• Save any manual backups (made immediately prior to a software upgrade, bulk data import or other maintenance task) for one year in, say, a \Manual\ folder.

This schedule can require a lot of disk space: for example, 40 images of the database with a 200GB library needs over 8TB. But drives are cheap, and engineering data is expensive, so it's a good trade-off.

Ensure that your backup schedule(s) don't occur during high workload times, or overlap another backup period. You can also target different types of backups to various secondary destinations.

Microsoft and Windows are trademarks or registered trademarks of Microsoft Corp.

This application note was relevant to the PDXpert software release that was current at time of publication. Product changes since that time may affect its utility. We'd be happy to assist you in assessing the applicability of this note to your situation.