Scheduled backup for the PDXpert PLM database and file library

Last update 2014-02-19

Topic contents

Applies to PDXpert PLM server software release 7.0 and later, and Vista, Windows 7 or Windows Server 2008 operating systems. These same techniques may be modified as appropriate for other configurations.

Designing an automated backup process

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.

To ensure that your data is safe, you should adopt an automated backup procedure that saves the database and the library. If you prefer not using a commercial backup software 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.

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 part of Windows 7 and Windows Server 2008, typically located in the C:\Windows\System32 folder. RoboCopy is available for earlier systems in the Windows Server 2003 Resource Kit Tools.
  • 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 part of Windows 7 and Windows Server 2008, typically located in the C:\Windows\System32 folder. It must be downloaded and installed for earlier versions of Windows.

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. Download (Save As...) the archive.ps1.rename script to a convenient folder, such as C:\Scripts.
  2. 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

    If you copy the contents of the downloaded PowerShell script into a new local file, then you can replace the Unrestricted argument with a more secure execution policy.

  3. If your PowerShell execution policy is unrestricted, then rename the downloaded file to archive.ps1. If the policy is more restictive, 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 tactics

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 two-year history of data with a high 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, 15 images.
  • Each week, archive the primary device's backup into a secondary device's \Weekly\ folder (Saturdays), with a maximum count of 9 images.
  • Every month, archive the primary device's backup into a secondary device's \Monthly\ folder (for example, the second Sunday of the month), with a maximum count of 26 images.
  • Any backups made immediately prior to a software upgrade are saved separately from the scheduled archive process, and retained for one year.

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.