SOLIDWORKS PDM Professional Automated SQL Backups

SQL Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies. The Maintenance Plan Wizard comes with prebuilt core plans, but manually creating plans gives more flexibility in execution. They can only be used with SQL Standard or higher, and so are only compatible with SOLIDWORKS PDM Professional.

Why do you need to use Maintenance plans at all though? These days, this seems unnecessary. You’re likely already backing up the entire server with one tool. However, this action is not enough to ensure data retention in the event of a failure of the PDM Server.

Let’s use database backups as an example. The SQL log file does not truncate (complete, compress, and physically log the information) until an SQL backup is completed. If a backup is not performed, the log will grow until it fills the disk entirely.

Backups are needed to preserve disk space. An entire server backup will not initiate the SQL backup process. A Backup Maintenance plan will allow you to automate this action, on a frequent and regular basis. Having up-to-date database backups will allow you to recover your data after a failure.

In this guide:

1. Using the Maintenance Plan Wizard
2. Creating a Backup Plan
3. Creating a Reorganize and Rebuild Plan
4. Modifying a Plan

Using the Maintenance Plan Wizard

To use the Maintenance Plan Wizard, you must have the SQL Server Management Studio (SSMS) installed. If you do not, you can get it here.

Additionally, when implementing an SQL Server Maintenance Plan:

• SQL Server Integration Services is a required Feature on the 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 the 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 Plan

1. Walk through the above 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 Cleanup 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 backup 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 Reorganize and Rebuild Plan

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 the 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.

This concludes how to use maintenance plans to create automated backups and reorganize and rebuild the index. Learn more about SOLIDWORKS PDM below. Additionally, join the GoEngineer Community to participate in the conversation, create forum posts, and answer questions from other SOLIDWORKS users. 

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