SOLIDWORKS PDM Professional Automated SQL Backups

Please note: The following guide only applies to PDM Professional using SQL Standard. PDM Standard can only use SQL Express, which does not include these options.

Check out our PDM Standard SQL Backup guide for more information on your environment if you use PDM Standard.

SQL jobs and maintenance plans create workflows for the tasks needed to keep your database optimized, regularly backed up, and free of inconsistencies.

“Why do I need to create SQL-specific backup plans at all, though?” These days, this seems unnecessary when you’re likely already backing up the entire server with one tool. If you’ve read our PDM Backups Master List guide, however, you’ve likely learned why a snapshot isn't enough to ensure data retention in the event of a PDM server failure. Due to the nature of PDM’s SQL database usage, a snapshot may leave you with a mid-transaction database upon restore, and there’s a chance you’re left with a corrupt database afterwards.

Additionally, the databases’ log file does not truncate (complete, compress, and physically log the information) until an actual SQL-aware backup is completed. If that backup is not performed, the log will just keep growing until it fills the disk entirely. Backups are needed to preserve disk space. Snapshots are not SQL-aware – they don’t pause the database activity, and they don’t truncate your logs, which could leave you in a bad spot when you’re already in the middle of a disaster recovery scenario.

Methods for Automation

Maintenance plans and jobs configured through SQL Server Maintenance Studio will allow you to automate this action. Having frequent and regular database backups available will allow you to recover your data in the event of a failure.

There are two ways to set up backups in SQL:

Maintenance Plans (SQL 2022 and earlier): In SQL 2022 and earlier, the SQL Server Integration Services (SSIS) module was easily selectable as part of the base install. SSIS is what includes the Maintenance Plans section of SQL Server Management Studio (SSMS). So for SQL 2022 and earlier, we recommend Maintenance Plans.

Jobs (SQL 2025 and later): With SQL 2025 and beyond, SSIS is no longer a core component, and it requires some finagling with Visual Studio to get added back into your install. This is primarily because Microsoft is starting to push Maintenance plans into the “legacy” category, and therefore, move companies away from using them. Maintenance plans are extremely reliable and easy to use, but they can lack some scalability for larger or more complex environments. So, in looking to the future, we want to allow users a way to future-proof their setups now while still safely backing up their vault DB’s.

Prerequisites

To use either of these options, you will need to have SQL Server Management Studio (SSMS) installed. If you don't, you can get it here.

You’ll also need to ensure the SQL Server Agent is running on the SQL server in order to process the required tasks.

1. To do so, open the SQL Server Configuration Manager (you can search for it in the Windows Start menu).
2. Go to SQL Server Services in the left-hand menu, and ensure SQL Server Agent is running.
3. If it’s not, right-click it and select Start.
   1. If you have multiple SQL instances, you’ll need to turn on the agent for the correct instance. 

Maintenance Plans

The Maintenance Plan Wizard includes prebuilt core plans, but manually creating plans gives more flexibility in execution.

Additionally, when implementing an SQL Server Maintenance Plan:

• SQL Server Integration Services is a required feature on SQL Server to run Maintenance Plans.
• Always ensure that a proper backup is in place before making any changes to the SQL database.
• Never manually create your own Index, or otherwise directly edit the Database.
• It is possible to run the plan while the vault is in use; however, overall performance may be affected while the plan is running. It is recommended to schedule this activity during off-hours.

1. Once installed, open SSMS and log into the database engine.
   
   
   
   
2. In the Object Explorer to the left of the SSMS window, expand the Management node, and then right-click Maintenance Plan.
   
   
   
   You’ll be able to choose between New Maintenance Plan and the Maintenance Plan Wizard. The New plan will allow you to create a plan manually. We will explore using the Wizard in this article.
   
   
3. Choose Maintenance Plan Wizard and then click Next on the new window.
4. Select Plan Properties:
   
   
   
   
   1. Give your plan a name.
   2. Run As: SQL Server Agent service account.
   3. Select the button for Single schedule for the entire plan or no schedule.
   4. For the schedule, click Change.
5. Set up the New Job Schedule:
   
   
   
   
   1. Choose Recurring for the Schedule type.
   2. Choose Daily for the frequency of the action.
   3. Choose an off-peak time to run the action.
   4. Choose a date to begin the action and click OK.
6. On the next page, select the Maintenance Task you wish to run. We will cover Backup Plans and Reorganize/Rebuild Plans.

Creating a Backup Task

1. Walk through the steps for using the Maintenance Plan Wizard. Set the schedule to daily backups for the best possible chance of recovery.
2. On the Select Maintenance Tasks page, choose Back up Database (Full) and Maintenance Cleanup Task. Then, click Next.
   
   
   
   
3. On the Task Order page, ensure that the back up comes first, and the clean up task is second. Then, click Next.
   
   
   
   
4. Define the Back Up Database (Full) Task.
   
   
   1. General Tab
      
      
      
      
      1. Choose All Databases from the dropdown menu.
      2. Choose to back up to Disk. Then, click Next.
         
         
   2. Destination Tab
      
      
      
      
      1. Select Create a backup for every database.
      2. Choose a directory to put the backups in or leave it as the default.
      3. Ensure the extension chosen is bak and then click Next.
         
         
5. Define the Maintenance Cleanup Task
   
   
   
   
   1. For Delete files of the following type, choose Backup files.
   2. For File Name, choose to Search folder and delete files based on an extension.
   3. Make sure to put the extension of bak.
   4. For File Age, select the box for Delete files based on the age of the file at task run time.
   5. Choose how long you’d like to keep back up files for, and then click Next.
      
      
6. Select Report Options
   
   
   
   
   1. Select the box for Write a report to a text file.
   2. Choose a location to store this file. Then, click Next.
      
      
7. Complete the Wizard by clicking Finish.

Creating a Rebuild & Reorganize Index Task

Tables in the Vault Database contain indexes, which ensure efficient lookups. This is automatically maintained by the SQL Server, whenever an insert, update, or delete type change is made in the underlying data. Over time, due to growth, indexes get a bit fragmented. Updating and refreshing the indexes will help with more severely fragmented instances. In turn, the maintenance plan will improve performance for searching, browsing, and other interactions with the File Vault.

1. Walk through the above steps for using the Maintenance Plan Wizard. Set the schedule to once a week to maintain a healthy vault.
2. On the Select Maintenance Tasks page, choose Reorganize Index and Rebuild Index. Then, click Next.
   
   
   
   
3. On the Task Order page, ensure that the Reorganize Task comes first, and the Rebuild Task is second. Then, click Next.
   
   
   
   
4. Define the Reorganize Index Task:
   
   
   
   
   1. Choose All Databases from the dropdown menu. Then, click Next.
      
      
5. Define the Rebuild Index Task:
   
   
   
   
   1. Choose All Databases from the dropdown menu. Then, click Next.
      
      
6. Select Report Options
   
   
   
   
   1. Select the box for Write a report to a text file.
   2. Choose a location to store this file. Then, click Next.
      
      
7. Complete the Wizard by clicking Finish.

Modifying a Maintenance Plan

1. In the Object Explorer to the left of the SSMS window, expand the Management node and the Maintenance Plan node.
2. Right-click the plan you wish to change and select Modify.
   
   
   
   
3. A flow chart will appear in the right-hand panel of the SSMS tool. You can drag new tasks from the toolbox to the flow chart to add them in.
   
   
   
   
4. To change the order of operations, select the box. A green arrow will appear. Grab the arrow and drag it to the next action box.
5. Actions and arrows can be deleted by selecting them and clicking the delete key on your keyboard.

Jobs

Creating a Job

1. On the SQL Server, log into your SQL instance via SSMS.
2. In Object Explorer, navigate to SQL Server Agent and expand it.
3. Right-click Jobs > New Job…
   
   
   
   
4. On the General page:
   1. Define the Name of the job. Ensure it’s something easily recognizable, like “PDM Database Backups (Nightly)”.
   2. Ensure the Owner is ‘sa’.
   3. Category can remain “uncategorized”.
   4. Ensure the Enabled checkbox is checked if it’s not enabled by default.
   5. When finished, click Steps from the page list on the left.
      
      

Backup Step

1. On the Steps page, click New…
   
   
   
   
2. The first step is taking backups. Name it “Backup – Vault” (replace vault with the name of your vault).
   1. If you have multiple vaults, you’ll need to create a unique step in the job for each vault’s database.
   2. Type is Transact-SQL script.
   3. Run as can be left blank.
   4. Database can stay on “Master”.
   5. In Command, paste in the SQL query below. Be sure you capture all of the text and preserve the formatting.
      1. Replace [VaultName] (in both locations) with the name of your vault’s database. Repeat this process and add a new step for each vault hosted on the server.
      2. Replace [Path] with the path (including drive letter) where you’d like the backups to write to. Be sure to preserve the text around the path.

Default is “C:\Program Files\Microsoft SQL Server\MSSQL##.MSSQLSERVER\MSSQL\Backup”. 

When you've finished configuring the step, click OK to go back to the main Steps page. Repeat for each database. 

Clean Up Step

After adding a step for each database in your environment (you should have at least 2 – one for each vault and one ConisioMasterDb), you'll need to add a step for cleaning up the old backup files. We only need one step to perform the cleanup action, as it captures all “.bak” files in the designated folder.

1. On the Steps page, click New….
2. Name the new step “Delete old backups”.
   1. If you have multiple vaults, you’ll need to create a unique step in the job for each vault’s database.
   2. Type is Transact-SQL script.
   3. Run as can be left blank.
   4. Database can stay on Master.In “Command”, paste in the SQL query below. Be sure you capture all of the text and preserve the formatting.
      1. Replace [Path] with the path (including drive letter) to the parent folder containing the backups that you defined earlier.
         1. For example, if your path earlier was “C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\Vault_”, then you’ll set the path for deleting old backups to “C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup”
      2. You can replace the ‘30’ in this query with whatever number of days you’d like to keep. So if you’d rather delete anything older than 2 weeks, you’d set it to:
         1. SET @cutoff = DATEADD(DAY, -14, GETDATE());

When you’ve finished configuring the step, click OK to go back to the main Steps page.

Schedule

1. When all steps have been added, go to the Schedules page and click New…
2. Set the Backup job:
   1. Schedule type: Recurring
   2. Occurs: Daily (we recommend running it at least once a day during off hours)
   3. Occurs once at: 12:00:00 AM (or whenever fits your environment’s schedule)
   4. Duration: Start immediately and do not set an end date
      
      
      
      
3. When done, click OK on the schedule page.
4. Review your job setup to ensure everything looks correct, and if so, click OK to save and close the job.

Index Maintenance Job

You’ll want to create a separate job for index rebuild and reorganization, as each job can only follow one schedule for all steps. We definitely want at least daily backups, but index rebuild and reorganization could cause performance issues if run that often. Usually, you want to run index maintenance no more than weekly. Though monthly would suffice for most environments. 

1. Follow the same steps as above to create and name a new job, then add a new step to the job.
2. Name the new step “Index Maintenance / Vault”.
   1. Replace Vault with the name of your vault to keep things tidy.
3. Type is Transact-SQL.
4. Run as can remain blank, or set it to SA.
5. Database can remain on “Master”
6. In “Command”, paste in the following text. Be sure you capture all of the text and preserve the formatting. 
   1. Replace [VaultName] with the name of your vault’s database. Repeat this process and add a new step for each vault hosted on the server.
      
      
      
      

      /* Official SOLIDWORKS index maintenance script (KB QA00000123139)

       Checks fragmentation on each index and acts based on thresholds:

       - Under 5%: No action taken

       - Between 5% and 30%: Reorganize (lighter, online operation)

       - Above 30%: Rebuild (full reconstruction)

       Only processes indexes with more than 1000 pages (small indexes are not worth maintaining) */

       USE [VaultName]

       DECLARE

       @id int,

       @dbname SYSNAME,

       @cmd1 nvarchar(max)

       SET @dbname = DB_NAME();

       IF OBJECT_ID('tempdb..#work_table') IS NOT NULL DROP TABLE #work_table

       CREATE TABLE #work_table

       (

       Task int IDENTITY (1,1) NOT NULL,

       ObjectName sysname,

       IndexName sysname,

       SchemaName sysname,

       IndexFragm float

       )

       SET @Cmd1= N'USE ['+ @dbname + N'] ;

       INSERT INTO #work_table

       SELECT

       o.name AS objectName,

       i.name AS indexName,

       s.name AS schemaname,

       p.avg_fragmentation_in_percent

       FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, null) AS p

       INNER JOIN sys.objects AS o ON p.object_id = o.object_id

       INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id

       INNER JOIN sys.indexes AS i ON p.object_id = i.object_id

       AND i.index_id = p.index_id

       WHERE p.page_count > 1000

       AND p.avg_fragmentation_in_percent > 5

       AND p.index_id > 0

       AND s.name <> ''sys'';'

      EXEC sp_executesql @CMD1

       DECLARE

       @Object_name sysname,

       @index_name sysname,

       @SchemaName sysname,

       @Fragmentation float,

       @command1 nvarchar(max),

       @command2 nvarchar(max),

       @task int

       SET @task = 1

       WHILE (1=1)

       BEGIN

       SELECT

       @Object_name = ObjectName,

       @index_name = IndexName,

       @SchemaName = SchemaName,

       @Fragmentation = IndexFragm

       FROM #work_table

       WHERE task = @task

      IF @@ROWCOUNT = 0

       BREAK

       IF (@Fragmentation > 30) -- Rebuild

       BEGIN

       SET @Command1 = N'USE [' + @Dbname + '] ; ALTER INDEX [' + @index_name + '] ON '

       + @SchemaName + '.[' + @Object_name + ']'

       + N' REBUILD WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'

       PRINT (@Command1)

       EXEC sp_executesql @command1

       END

       IF (@Fragmentation > 5 AND @Fragmentation < 30) -- Reorganize

       BEGIN

       SET @Command2 = N'USE [' + @Dbname + ']; ALTER INDEX [' + @index_name + '] ON '

       + @SchemaName + '.[' + @Object_name + ']'

       + N' REORGANIZE WITH (LOB_COMPACTION = ON)'

       PRINT @command2

       EXEC sp_executesql @command2

       END

       SET @task = @task + 1

       END

       DROP TABLE #work_table

7. Go to the Schedules page and click New...
8. Set the Index Maintenance job to run either weekly or monthly during off hours (we usually recommend later at night on a weekend). 
   
   
   
   
   
9. When done, click OK on the schedule page. 
10. Review your job setup to ensure everything looks correct, and if so, click OK to save and close the job.

Final Thoughts

If GoEngineer is your reseller and you have any questions or issues, please reach out to our Support Team for more help! We’d be happy to assist.

Additionally, you can join the GoEngineer Community to participate in conversations, create forum posts, and answer questions from other SOLIDWORKS users. 

Editor's Note: This article was originally published in January 2025 and has been updated by our Technical Team for accuracy and comprehensiveness.

Learn More About SOLIDWORKS PDM

SOLIDWORKS PDM 2025 - What's New

Configuration Properties in SOLIDWORKS PDM Data Cards

SOLIDWORKS PDM - Implement Working Revisions

How to Create Dynamic Lists in SOLIDWORKS PDM Standard Data Cards

How to Install the SOLIDWORKS PDM Server

VIEW ALL SOLIDWORKS PDM ARTICLES