Content Server (aka "DB/Text for SQL") Backup Script Utility

by Ted Jardine Wednesday, June 09, 2010 12:15 PM

If you are using Content Server (now known as "DB/Text for SQL"), it goes without saying that you should have regular automatic backups in place: regularly verified and regularly "test" restored. You DO have such a plan in place, right? Right?!?

Dilbert.com

For backing up DB/Text for SQL, it's a touch more involved than a typical SQL Server backup, simply because there's data both in and out of SQL Server and both need to be backed up at the same time. Moreover, SQL Server databases are not able to be backed up by typical backup mechanisms (to oversimplify, the actual files are always locked).

Fortunately, a manual backup of CS is pretty easy: just open up your CS Admin and follow the prompts to back up everything in one fell swoop into one handy .dat backup file. The bad news with that however, is that manual backups are a half-baked solution; backups need to be automated or they just won't get done often enough (if at all).

Options for automated CS backups:

  1. Back up the SQL Server aspects of CS as part of your regular automated SQL Server backups. And ensure that the external files are backed up at the same time. This solution will likely require the involvement of your IT staff. Moreover, automated SQL Server backups require SQL Server Agent which is not included with SQL Server Express (a requirement at least for a backup process that isn't the digital equivalent of the hokey-pokey dance crossed with a waltz). See your Administrator's Guide for details (for backup instructions, not dance tips).
  2. Use the built-in CS Admin backup capabilities, but automate them by plunking some CS-specific backup scripts in a batch file and scheduling them with Windows Task Scheduler.

Aha, you SQL-Server-Express-loving-person! Option two sounds great! Let's do it! Not so fast...have you seen those scripts? If you've got any more than one or two textbases, you'll go blind trying to accurately create and maintain those batch files. And that's where our new handy-dandy CS Backup Script Generator comes in. With it, you can quickly and easily generate accurate backup scripts contained in an automatically generated batch file that can be automatically run with a scheduled task. Did I mention automatically!?

Backups + Automation = Sweetness.

CS Backup Script Generator

 

  1. Download the Andornot CS Backup Script Generator here (yes, it's priceless so it's free) and install. Note that you can install the utility on any Windows machine as it does not need to be on your CS server.
  2. If you don't already have the Microsoft .NET Framework 4.0 installed, download it here and then install.
  3. Run the utility (should be in your start menu with a link on your desktop) and fill in three fields:
    1. SQL Server Instance: the name of your SQL Server instance, such as MachineName\SQLEXPRESS (if you don't know it, you can find it easily in your CS Admin).
    2. Backup directory: where you wish to have all backup files saved to (such as "D:\Backups\").
    3. Textbase Information Text: the text from the "List Textbases" summary within CS Admin (just select all the text in the summary, making sure to include the entire textbase listing such as the following:

      ...
      Textbase: D:\data\Barcodes
        SQL Database: '_InmTB_18'
      Textbase: D:\data\Borrower
        SQL Database: '_InmTB_19'
      Textbase: D:\data\Catalog
        SQL Database: '_InmTB_20'
      Textbase: D:\data\Contacts
        SQL Database: '_InmTB_21'
      Textbase: D:\data\Loans
        SQL Database: '_InmTB_22'
      ...

      The utility is smart enough to pick up the list of textbases from all the text in the summary, so you can paste in as much or as little of the summary as you like, as long as it includes the textbase listing. Get to the listing in CS Admin via the "Manage Textbases" > "List Textbases" menu item.
    4. Hit "Preview" to take a look at your new masterpiece (and shudder to think of doing that manually especially if you've got 10+ textbases) and/or "Batch it!" to save a .bat file to the location and name of your choosing.

      CS Backup Script Generator Preview
    5. Test the generated batch file by double-clicking it. After a few minutes (or less depending on the size of your database), there should now be a  .log and .dat file for each of your textbases in your specified backup directory.
    6. Review the .log file for any errors or warnings.
    7. Schedule the batch file to run daily during an off-peak time.
    8. Regularly review the generated log files for any errors or warnings and verify that backups are taking place as required.
    9. Ensure that your backup directory itself is backed up off-server and off-site for disaster recovery. The generated .dat files can be simply x-copied (copied and pasted) to another location, or better yet, automatically backed up with your server's backup software as part of your server's regular backup routine.
    10. Every so often, test out restoring your .dat backups via the CS Admin.

Remember, "hell hath no fury like data scorned." Please love your data and back it up.

Disclaimer: this backup utility is very much "beta" quality. I'm not responsible for anything it may or may not do to your system (bad or otherwise). Use at your own risk. Having said that, the setup program simply places the .exe with some support DLLs on your system, and when the utility is run, you can easily review the batch files it generates before running them (which you could generate manually yourself if you prefer).

blog comments powered by Disqus

Month List