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 creates 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.
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.
To create the batch file, start a new Notepad document. Copy and paste the script below into the document. (Please note that Word has replaced the quotation marks with its own and they don’t work well in batch files. Therefore, once pasted into Notepad please replace all existing quotation marks with ones from Notepad) Now, modify the below variables to your environment:
Begin Batch file text. Copy to the “End Batch File”. Don’t copy the “Begin” and “End” lines.
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
sqlcmd -S (local)\% pdm_instance_name% -i "%cmd_dir%\SQL backup script.sql"
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"
End Batch 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 pathname 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.
-- Change the @databaseName and the @pathName
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
End SQL File
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.
Link to zip of SQL and batch files.
GoEngineer delivers software, technology and expertise that enable companies to unlock design innovation and deliver better products faster. With more than 30 years experience and thousands of customers in high tech, medical, machine design, energy and other industries, GoEngineer provides best-in-class design solutions from SOLIDWORKS, Stratasys, Altair, CAMWorks, Creaform and Product Lifecycle Management (PLM).
Get the latest articles delivered daily to your inbox, unsubscribe at any time.