Posted by: Andries Koorzen | August 14, 2013

Automating Backups… the easy way

For all you Enterprise administrators out there – I have a new-ish nugget. When I wrote about automating your backups in this post, I was a still a newbie in the beginning stages of my automation career here at Mecad, and yes… it might have been a solution at one point, but I’ve since tuned my attention to something a little more palatable. That is by no means a confession that I am an expert in anything, it simply means I have become…let’s just say, enlightened. Looking back at what I did, I suppose that was the knowledge I had at the time.

This new (actually it’s quite old) knowledge / solution comes in the form of an integrated, automated solution, from within SQL server, (which once again emphasizes the fact that the full version of SQL which is supplied to EPDM clients is just, well, that much better than the Express version which does not have backup maintenance plans). My point is simply this: Appreciate that the guys at SolidWorks has gone through the effort of providing you with the full version

Let’s look at the current setup I ‘invented’ lol:

  • You need to create a script (or download it from an old post)
  •  Customize the script to suit your environment
  • Save it as a .SQL file on the server hosting the EPDM database
  • Create an optional batch file that runs the script
  • Schedule running the batch file / SQL script using Windows Task Scheduler

VS

  •  Create a maintenance plan in SQL server
  •   Schedule it to run

So we’ve got 5 steps requiring a lot of work vs. 2…OK…So let’s dig into this baby

I would like to back up my SQL databases on a daily / weekly basis, and to set this up is really easy.

Fire up SQL server management studio:

Create a new maintenance plan by selecting the Management section, then right clicking on Maintenance Plans -> Maintenance Plan Wizard

Create a new maintenance plan

Create a new maintenance plan

You don’t want to run a differential backup so select Full. For what EPDM requires from SQL, this should be enough

Select action to perform

Select action to perform

Select the EPDM databases…the other databases are system databases that are created with every new installation of SQL server

Select the databases to backup

Select the databases to backup

When this is done, modify the plan to schedule when the backups should run. This should typically run as follows:

  • For very small to small offices (1-2 users) -> once a week
  • For small to medium sized (3-5) users -> at least once a week, preferable more
  • For medium and up (> 5 users) -> once a day
Schedule the backup

Schedule the backup

Needless to say, (but I’m gonna say it anyway) this backup should not made on the machine running the database, but rather to a separate hard drive / machine. This also goes for the archive files. Which brings me to another topic of database performance.

I recently saw a webcast from Tor Iveroth (SolidWorks World 2013) which gave me some great insight into database performance. The nature of the presentation was very technical, but the mains points for performance  were the following:

  • Consider strongly a RAID drive for your EPDM SQL server. Preferably RAID 5 or 10 (READ: Multiple drives are better than a single drive in terms of read access)
  • Have at least the same amount of RAM as the size of your database (If your database is 1 GB have 1 GB data over and above what the OS requires) (SQL loads the entire database into memory and then runs queries on it)
  • Do not virtualize SQL if you can help it… you’re just wasting resources running a resource hungry program on a resource hungry virtual machine (this is especially true of smaller companies)

I hope this could save some poor drawing office manager out there some time and effort. I know that not all companies are privileged to have an IT company do it all for them.

As a final note: I continually release / relearn how important it is to educate oneself on what is available in the software that I use on a daily basis. You would do well for yourself to do likewise

Until next time…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories

%d bloggers like this: