SOLIDWORKS PDM Standard Automated SQL Backup

SOLIDWORKS PDM Standard is a great data management tool included with SOLIDWORKS Professional. It’s built on an SQL Express database, which unfortunately means there are no automated maintenance plans in SQL Studio Manager. This makes automating the SQL backup procedure more difficult, but as I’ll explain in this article, it is possible. There are various utilities on the internet to accomplish this, but I wanted to explore a way to automate the backup process without installing any additional software. 

So, setting the stage for this article, I want to clarify some assumptions. The information below assumes you have SQL Express, SQL Studio Manager, and PDM Standard already installed on a server. If you need any assistance with installing the software please feel free to contact our services team for information. 

This article also assumes you are looking to automate the SQL backup procedure for the PDM databases in SQL. It should be noted, that this is not the only way to accomplish this, nor is it even GoEngineer’s recommended way. It is simply a method I’ve discovered to accomplish this without purchasing, downloading, or installing any additional software on the server, and I thought I would share with the customer base who may be interested. 

Without installing any software, how can the backups be automated? 

Answer: A batch file that fires the SQL commands to backup the databases at a specified location, which is then run using Windows Task Scheduler to trigger on a schedule 

This requires the customized batch file to be placed locally on the server, and the task created in Windows Task Scheduler. 

What does the script look like? 

Here is an example script I’ve written that can be altered to suit your needs:  

SQLCMD -S "DESKTOP-B46BJVO\PDM" -Q "BACKUP DATABASE ConisioMasterDb to DISK = 'E:\SQL BACKUPS\ConisioMasterDB.bak' WITH INIT" 
SQLCMD -S "DESKTOP-B46BJVO\PDM" -Q "BACKUP DATABASE PDMStandard to DISK = 'E:\SQL BACKUPS\PDMStandard.bak' WITH INIT"  

If you copy and paste this script into a notepad file, you can adjust it as needed for your setup. I’ll bold and italicize below what variables need to be adjusted. 

what does this script do exactly? 

So here is the breakdown:  

SQLCMD -S “DESKTOP-B46BJVO\PDM”  

This launches the SQL command prompt and logs into the instance specified inside the quotes. You’ll need to swap out your server and instance name here as Servername\InstanceName  

-Q "BACKUP DATABASE ConisioMasterDb to DISK = 'E:\SQL BACKUPS\ConisioMasterDB.bak' WITH INIT" 

This fires the backup of the ConisioMaster Database to the specified location. You’ll need to swap out your local server path here. 

-Q "BACKUP DATABASE PDMStandard to DISK = 'E:\SQL BACKUPS\PDMStandard.bak' WITH INIT"  

This fires the backup of the PDM Standard Database to the specified location. You’ll need to swap out your local server path and vault name here. 

Be sure to keep the WITH INIT syntax in the command. This causes the new backup to overwrite the existing backup on the hard drive. Without it, the backup grows in size with each day. 

Once I have the script modified, what do I need to do to automate it? 

First, you’ll need to convert it from a text file to a batch file. To do this, you simply need to rename the file from .txt to .bat for the extension. To accomplish this, you’ll need your windows folder settings set to not hide extensions from know file types. 

Second, I would recommend launching the batch file manually to ensure it is working as intended. Make sure it backs up the databases to the specified location.  

Then it's just a matter of building it into Windows Task Scheduler as shown below.   

Creating a task to run the batch file in windows task scheduler 

Go to the windows start menu and search for Task Scheduler to launch it. Right-click Task Scheduler (local) and choose Create Task: 

Click on the Triggers tab, and set a schedule. I would recommend daily and off normal working hours. 

On the actions tab, browse out to the customized batch file stored locally on the server. 

Set any conditions and settings you like on the other two tabs. Click ok.  

Lastly, click on the Task Scheduler Library and you should see it in the queue: 

Check the backup location after the scheduled task time to ensure it's properly generating the latest SQL backups as intended.  

So that pretty much covers automating the SQL backups for a PDM Standard database on your server.   

It should be noted that it only leaves the latest backup on a local hard drive, so you’ll want to copy those off of the server for safe keeping as well in the event of hardware failure. 

If you would like to preserve more than just the latest backup on the server, you can adjust the path on the output file to include a day of the week, then just create multiple daily tasks for each day. 

What’s next from here?  

To fully automate a disaster recovery plan, you’ll need to back up the archive server settings, and a full copy of the entire root archive folder. There are many ways to do this, including additional batch script and windows task scheduler options.  

Please feel free to reach out to us to discuss any questions or concerns you run into. 

For an even more in-depth SQL backup that can be customized to the number of backups stored, view this article. 

Want to Learn More? 

How to Map PDM Variables into Microsoft Excel

Moving the SOLIDWORKS PDM Vault Database Yourself? Start Here

How to Reset the SQL 'SA' User Password