How To Schedule SOLIDWORKS PDM Standard Database Backups

Article by Andrew Lidstone, CSWE updated June 14, 2017

Article

SOLIDWORKS PDM Professional runs on Microsoft SQL Standard which allows maintenance plans to be set up which will automatically create backup files for the PDM databases. However SOLIDWORKS PDM Standard runs on Microsoft SQL Express, which does not include the SQL Server Agent and thus does not have the ability to create automatic backups.

It is VERY important that the SOLIDWORKS PDM Standard vault databases be backed up on a daily basis. If you haven’t set up a backup process for the databases yet, do so right away, your data is at risk until you do so (even if you are backing up the Archive Folder with the actual vault files, you still need a backup of the database to restore in case of system failure).

There are two options for setting up scheduled back ups of a Microsoft SQL Server Express database.

Option 1: Third Part Utilities

There are several utilities, both paid and free, which can do an excellent job of backing up the databases. Some of these tools even include the ability to upload the backups to cloud storage or to send out email notifications if desired.

A few options are:

Please Note: These utilities are not SOLIDWORKS or Javelin products, so this list is provided for information purposes only. Javelin does not specifically recommend any of these utilities over another and cannot guarantee their effectiveness.

When using these utilities, ensure that both the database for the SOLIDWORKS PDM Vault AND the ConisioMasterDB database are being backed up.

Option 2: Using SQL Server Management Studio and Windows Task Scheduler

A backup can be scripted through the SQL Server Management Studio and then set up to be done on a daily basis using a batch file that is run by the Task Scheduler integrated into Windows.

The steps for creating a scheduled backup of a database hosted in Microsoft SQL Server Express are explained in the Microsoft Knowledge Base article KB2019698.

Below is a slight modification to those instructions, including a slightly simpler to set up batch file which will also perform a cleanup of the backups, removing those older than a set number of days.

To use this method, the SQL Server Management Studio tool must be installed.

Here are the steps:

Step 1: Launch the SQL Server Management Studio and log in to the named instance “SWPDM” using the sa account and password.

Connect to server

Step 2: Click “New Query”

New Query

Step 3: Copy and paste the script from the Microsoft Knowledge Base article KB2019698 into the query window (the text of the query is below for easy reference). Ensure that the “master” database is specified in the drop list, then click “Execute”. This will create the stored procedure “sp_BackupDatabases” that the batch file will trigger to create the backups.

SOLIDWORKS PDM Standard Database Backups

Copy & Paste Query

This is the script for the query:

USE [master] 
GO 
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
 
-- ============================================= 
-- Author: Microsoft 
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
 
CREATE PROCEDURE [dbo].[sp_BackupDatabases] 
 @databaseName sysname = null,
 @backupType CHAR(1),
 @backupLocation nvarchar(200) 
AS 
 
 SET NOCOUNT ON; 
 
 DECLARE @DBs TABLE
 (
 ID int IDENTITY PRIMARY KEY,
 DBNAME nvarchar(500)
 )
 
 -- Pick out only databases which are online in case ALL databases are chosen to be backed up
 -- If specific database is chosen to be backed up only pick that out from @DBs
 INSERT INTO @DBs (DBNAME)
 SELECT Name FROM master.sys.databases
 where state=0
 AND name=@DatabaseName
 OR @DatabaseName IS NULL
 ORDER BY Name
 
 -- Filter out databases which do not need to backed up
 IF @backupType='F'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
 END
 ELSE IF @backupType='D'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
 END
 ELSE IF @backupType='L'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
 END
 ELSE
 BEGIN
 RETURN
 END
 
 -- Declare variables
 DECLARE @BackupName varchar(100)
 DECLARE @BackupFile varchar(100)
 DECLARE @DBNAME varchar(300)
 DECLARE @sqlCommand NVARCHAR(1000) 
 DECLARE @dateTime NVARCHAR(20)
 DECLARE @Loop int 
 
 -- Loop through the databases one by one
 SELECT @Loop = min(ID) FROM @DBs
 
 WHILE @Loop IS NOT NULL
 BEGIN
 
-- Database Names have to be in [dbname] format since some have - or _ in their name
 SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
 
-- Set the current date and time n yyyyhhmmss format
 SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 
 
-- Create backup filename in path\filename.extension format for full,diff and log backups
 IF @backupType = 'F'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
 ELSE IF @backupType = 'D'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
 ELSE IF @backupType = 'L'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
 
-- Provide the backup a name for storing in the media
 IF @backupType = 'F'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
 IF @backupType = 'D'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
 IF @backupType = 'L'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
 
-- Generate the dynamic SQL command to be executed
 
 IF @backupType = 'F' 
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
 END
 IF @backupType = 'D'
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
 END
 IF @backupType = 'L' 
 BEGIN
 SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
 END
 
-- Execute the generated SQL command
 EXEC(@sqlCommand)
 
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 
END

Step 4: Open a text editor such as Notepad then copy and paste the following text.

::Specify Folder Location For SQL Database Backups
set BACKUPPATH=c:\SQL Backup
::Specify Name of PDM Vault Database
set PDMDATABASE=PDMVault
::Specify How Many Days Of Backups To Keep
set numbackups=14
sqlcmd -S .\SWPDM -E -Q"EXEC sp_BackupDatabases @backupLocation='%BACKUPPATH%\', @databaseName=%PDMDATABASE%, @backupType=F"
sqlcmd -S .\SWPDM -E -Q"EXEC sp_BackupDatabases @backupLocation='%BACKUPPATH%\', @databaseName=ConisioMasterDB, @backupType=F"
forfiles /P "%BACKUPPATH%" /S /M *.BAK /D -%numbackups% /C "cmd /c del @PATH"

Replace “c:\SQL Backup” with the path for the folder where the SQL database backup files will be stored (this must be a location on the local hard drive).

Replace “PDMVault” with name of the database for the PDM Vault. PLEASE NOTE this may be different from the name of the vault. To verify the database name, expand the list of databases in the SQL Management Studio and use the name found there.

Replace “14” with the number of days that you wish keep the backup files for.