SOLIDWORKS PDM Standard – Backup SQL

The issue

SOLIDWORKS PDM Standard runs on SQL Express – a free version of SQL. As such, it doesn’t have all of the features that SQL has. In full SQL a Maintenance plan can be created in order to automatically make database backups. This functionality isn’t available in SQL Express. If a database backup is desired, then it must be made manually.

For PDM it is best if the backups are made daily. If the backups are manual then they may get overlooked as something more important takes the attention of the PDM Administrator. So, it would be best if this job can be automated.

Backups from SQL Express can be automated by creating a batch file that is then run on a regular basis by the Windows Task Scheduler. It is relatively simple to run the backup command which then either overwrites the existing backup or changes the file name (adds the date/time/second to the name) and create an endless number of backups until the hard drive is full. Of course, this isn’t desired but if the batch file was created to do this then the administrator would have to remember to regularly clear out old backups.

It would be best if more than a single backup is kept but the number is limited.

The solution

There are two files: a batch file and a file for the SQL commands. The batch file needs to have access to the SQL file so please save them to the same folder. The contents of both files will be contained within this document. Once the files have been created then use Windows Task Scheduler to run the batch file on a daily basis. After this, the batch file will run daily but the code in the batch will limit the number of backups. The default is (4) but this can be changed to whatever you like. It will delete the oldest backups.

The batch file

To create the batch file, start a new Notepad document. Copy and paste the script below into the document. Now, modify the below variables to your environment:

• “saveTo_dir” is the path where you want to keep your backups.
• “cmd_dir” is the path to where you copied “SQL backup script.sql”
• “pdm_instance_name” is the name of the SQL instance where your PDM is installed
• “Num_backups_to_keep” is the desired number of backups. Note it is currently set to “4” but it can be any number higher than “0”.
• The final item to be addressed is to change the file extension from “.txt” to “.bat”.

Begin Batch file text. Copy to the “End Batch File”. Don’t copy the “Begin” and “End” lines.

@echo off
REM Change the below variable to match your environment
SET saveTo_dir=”Path to your backup folder”
SET cmd_dir = “Path to SQL backup script”
SET pdm_instance_name=”Name of SQL instance”
SET Num_backups_to_keep=4

cd %saveTo_dir%

sqlcmd -S (local)\% pdm_instance_name% -i “%cmd_dir%\SQL backup script.sql”

setlocal enableextensions

:CheckNumBackups

set count=0
for %%x in (*.bak) do set /a count+=1
REM Check if number of backup files is greater than the quantity to keep
echo Backups to keep = %Num_backups_to_keep% and Number of backups = %count%
IF %Num_backups_to_keep% geq %count% (GOTO :EOF)
FOR /f “delims=” %%a in (
‘dir /b /a-d /tw /od %saveTo_dir%\*.bak’) DO (
echo File to delete is: %saveTo_dir%\%%a
DEL %saveTo_dir%\%%a
GOTO CheckNumBackups
)

Endlocal

End Batch File

The SQL file

To create the SQL file, start a new Notepad document. Then, copy and paste the script below into the document. Modify the “@databaseName” to the name of the database. The path name should start with the “saveTo_dir” from the batch file and then add a base file name. The date, time and extension will be added to it.

For example: if the saveTo_dir in the batch file is “C:\PDM Database Backups” and your database name is “Charles” then change the yellow area on the path name to “C:\PDM Database Backups\Charles_”. The text should begin and end with a single quote.

The saved backup will look like: “Charles_20180327_17-20-28.bak”

Save the file with the name “SQL backup script”. Once the file is saved, change the extension from “.txt” to “.sql”. The text to change in the script is highlighted in yellow.

Begin SQL file text. Copy to the “End SQL File”. Don’t copy the “Begin” and “End” lines.

DECLARE
@pathName NVARCHAR(512),
@databaseName NVARCHAR(512)

— Change the @databaseName and the @
SET @databaseName = ‘Your database name’
SET @pathName = ‘saveTo_dir\baseFileName_‘ +
Convert(varchar(8), GETDATE(), 112) + ‘_’ +
Replace((Convert(varchar(8), GETDATE(), 108)),’:’,’-‘)+ ‘.bak’

BACKUP DATABASE @databaseName TO DISK = @pathName WITH NOFORMAT,
NOINIT, NAME = N”, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

End SQL File

Windows Task Scheduler

There are many documents online that describe how to set up the Windows Task Scheduler. Use one of those to set up a task to run daily. Have this task execute the batch file.