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.
Step 2: Click “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.
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.
Step 5: Perform a Save As and change the “Save as type” to “All Files”. Name the file “Sqlbackup.bat” and save the file.
Step 6: Launch the Task Scheduler and click “Create Basic Task” to launch the wizard.
Step 7: Assign a name to the task.
Step 8: Set the task to trigger daily.
Step 9: Set the time for the back up to be made. This should be at a time when no users will be working in the vault.
Step 10: Set the task action to be “Start a program”.
Upcoming Related Event
Become a SOLIDWORKS Expert by watching a LIVE Broadcast in October to learn What's New in SOLIDWORKS 2022, plus learn about 3D Scanning, and Additive Manufacturing.