Computer Aided Technology Blog

Popular Topics
DESIGN/ENGINEERING

The Ultimate Guide to SQL Backups

SQL backsupsSOLIDWORKS PDM leverages Microsoft SQL Server for its database back end to satisfy its need for a relational data store.  There are countless numbers of IT professionals that dedicate themselves to developing, administering, and architecting solutions for Microsoft SQL Server.  Of these professionals, one common role is the operational DBA (database administrator) – whose responsibility it is to maintain and tune SQL Server environments.  A lot of companies have dedicated database teams that can help, or completely, maintain your database backend for PDM. Far more companies exist that don’t have a dedicated resource to perform this work which requires the responsibility to fall to the implementer of PDM to set up the maintenance of the SQL Server instance.  

This blog will offer guidance and general rules of thumb for ensuring your SQL Server instance and databases are backed up and you are able to recover from oopsie deletes and disaster scenarios.

Overview

Microsoft SQL Server has an incredibly rich user community that contributes a lot of free tooling and knowledge for the greater good.  One of the highest quality and best-known tools is Ola Hallengren’s SQL Server Maintenance Solution.  In the absence of paid alternatives, Ola’s maintenance solution is the gold standard with regards to maintaining your SQL Server instance.  I will give a brief overview of how to deploy the database objects and how to configure the jobs for their execution. Three big items are covered by Ola’s solution: backups, index and statistics maintenance, and integrity checks.  For the purposes of this blog post, we will focus on the backups. As with all things SQL Server, a little background information will be helpful in understanding the general idea of this solution.

Backups

SQL Server backups come in three types (I’m leaving  advanced topics out of these descriptions):

  • Full backups – these are backups of every piece of data in a database to backup media (typically file(s)).  With just a full backup, you can restore an entire database to the point when that full backup was taken.
  • Differential backups – these are exactly what you would think – the difference in the data from the most recent full backup.  These backups are based on a differential bitmap which is flushed during the full backup process, so they are only useful as long as you have the full backup from which its backup chain is tied to.  That can otherwise be stated as a full backup is dependent on the most recent full backup that occurred before the differential backup was taken.
  • Log backups backup what is known as the transaction log.  The transaction log holds the history of every modification that occurs inside of the database.

Of the three of these backup types, the most involved to explain is the log backup.  For this reason, I am going to give the transaction log its own discussion.  

The Transaction Log

The transaction log is the SQL Server implementation to meet the durability component of the ACID properties that govern database transactions. Databases can be in one of three recovery models, which dictate the behavior of the transaction log: simple, bulk-logged, or full.  The most common recovery models are simple and full.  

Simple Recovery

In simple recovery model, as soon as a transaction either commits or rolls back it is complete and the portion of the transaction log used to record the modifications that were made during the transaction can be marked as complete and available for reuse (again, skipping advanced topics – there is much more to this, but it’s not pertinent for this conversation).  For this reason, a transaction log in simple recovery model can be thought of as circular – only needing enough room inside it for the active transaction(s). 

Full Recovery

In full recovery model, transaction log records are held until they are committed or rolled back and backed up by a transaction log backup.  Those who are not accustomed to this behavior will notice that their transaction log file will grow in perpetuity until it fills until the drive and the database goes into read-only mode.  The main benefit of the full recovery model is that you can restore your database to a point in time (or to a specific LSN [log sequence number]… or to a marked transaction…).

When contemplating your needs, it essentially boils down to one question: is the business okay with lost work, and if so, how much?  The answer is inevitably always “No, we can’t lose any work! Ever!” The dollar amounts associated with solutions like that are pretty high, so businesses will usually back off of that when they see what’s involved.  Regardless, the recovery model decision tree is very simple – can you lose data?

  • Yes
    • Do you need point in time recoverability?
      • No – simple recovery model
      • Yes – full recovery model
  • No – full recovery model

Please be aware that there are other ways to lose data, but ensuring a solid backup strategy is in place mitigates the vast majority of them.  That said, here are the rule of thumb backup policy recommendations I would make for each recovery model:

Simple Recovery Model

    • Full backups – schedule these daily when there is a logical breakpoint in work being performed in the database.  This will give you a very easy way to restore to a day. I generally recommend performing the full backup at the beginning of the workday to give a nice clean restore point.
    • Differential backups – schedule these to happen at whatever interval you set your recovery point objective to (how much data can you lose? 1 hour, 2 hours, etc.)

Full Recovery Model

    • Daily full backup – same as simple recovery model
    • Differential backups – in the full recovery model, these are just helpers to reduce the amount of transaction log backups you have to restore in a recovery scenario
    • Transaction Log backups – schedule these for relatively short intervals (every 15 minutes is pretty common)

Backup Policy Implementation

To implement your desired backup strategy, you will first need to download the Hallengren maintenance solution script(s).  You can download the individual solutions separately, but I would recommend just using the maintenance solution script that deploys all of the stored procedures and objects necessary at one time.

  1. Download the MaintenanceSolution.sql file from https://ola.hallengren.com/ (There is a link for downloading MaintenanceSolution.sql file on the default page)
  2. Connect to the SQL Server instance that you wish to configure your backup policy for
  3. Open the MaintenanceSolution.sql file while connected to that instance.
  4. Change the following in the SQL script:

USE [master] — Specify the database in which the objects will be created.

Replace it with:

USE [msdb] — Specify the database in which the objects will be created.

5. Change the following in the SQL Script:

SET @CreateJobs          = ‘Y‘          — Specify whether jobs should be created.

SET @BackupDirectory     = NULL         — Specify the backup root directory. If no directory is specified, the default backup directory is used.

SET @CleanupTime         = NULL         — Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.

SET @OutputFileDirectory = NULL         — Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.

SET @LogToTable          = ‘Y‘          — Log commands to a table.

We are going to specify some values, so you will want it to look like this (substitute your values that are appropriate for your server/environment – the cleanup time will hold your backups for 7 days and a little padding):

SET @CreateJobs          = ‘Y’

SET @BackupDirectory     = N'<<insert the path to backup folder here>>’

SET @CleanupTime         = 170

SET @OutputFileDirectory = N'<<insert the path to log output folder here>>’

SET @LogToTable          = ‘Y’

6. Execute the script – this will create a table, several stored procedures, and some SQL Agent jobs.

SQL Backup script

SQL backup server agent

7. Next, we need to schedule the jobs to run.  For each of the following jobs, you will need to create a schedule. In order to get to the screen displaying some general settings that should cover you pretty well, you can double click the job > Click “Schedules” > Click “New…”

  • a. DatabaseBackup – SYSTEM_DATABASES – FULL

SQL Backup new job schedule

  • b. DatabaseBackup – USER_DATABASES – DIFF

SQL Back up solidworks

  • c. DatabaseBackup – USER_DATABASES – FULL

SQL Backup guide

  • d. DatabaseBackup – USER_DATABASES – LOG

SQL backup PDM

8. Ensure that the jobs successfully run (you can check the backup folder you specified earlier to look for the backup files – they should be in subfolders similar to: <Backup Path><sqlInstanceName><DatabaseName><BackupType><backupFile>_<timestamp>.bak)

9. It is also recommended that you verify that you can restore your databases from the backups – it is recommended that you do this on a schedule.

That’s it!  You should now have a relatively robust backup strategy in place!  The integrity check and index maintenance jobs are incredibly important, but require blogs on their own to do them justice.  The cleanup jobs you can either schedule or not – if you don’t schedule them, you will merely maintain a history of all backups indefinitely.  This may actually be desirable in some highly regulated environments.  

If you want to learn more about the Ola Hallengren’s maintenance solution, here are some helpful links for further reading:

Keep in the know of all things SOLIDWORKS by subscribing to our software email newsletter. 

Related Articles

How to Relocate SOLIDWORKS PDM to a new Server 

Newly Announced SQL Server Updates for SOLIDWORKS Customers

Let’s Talk About PDM : Design to Manufacturing Series (Part 5)

Sunlight Media:Should you be looking at website speed?

About the Author

Lindsay EarlyLindsay Early is an Application Engineer Consultant and has been with Fisher Unitech for six years. Lindsay is a Certified SOLIDWORKS Expert and Elite Application Engineer having earned over 14 SOLIDWORKS Certifications including Mechanical Design, Simulation, and Data Management.

  • Share this
Find Your Design Solution in the CATI Store.
Browse Products