All-In-One Field Service Management Software by Aptora

Database Maintenance

Automatic Database Backup

Question:
How can I backup my Total Office Manager Enterprise Edition database on a regular schedule (ie. nightly, weekly, monthly, etc.)?

Solution:

Please note that our support staff is not available to assist with the contents of this issue. This information is being made available “AS-IS”. Aptora takes no responsibility for problems caused from using the above information incorrectly, or without full understanding of the technology.

Applies To: On Premise Total Office Manager Enterprise Edition
SQL Version: All

Database backups for Total Office Manager Enterprise Edition should be handled using SQL Server components to ensure all data and log files stay intact. Backing up the physical files manually is not ideal, nor is it recommended by Aptora.

The best way to handle automated Database Backups is to create a custom backup script that can be scheduled using Windows Task Scheduler (Express Edition), use SQL Server Agent to schedule the a backup job (Workgroup Edition and above), or use third-party software that runs on a schedule and performs the backup tasks using standardized SQL Server methods (All Editions).

The following describes how to create a batch (.bat) file that can be scheduled to run in Windows Task Scheduler. Additional parameters that can be used with SQLCMD can be found elsewhere on the internet.

1. Open your favorite text editor application (ex. Notepad, Wordpad, etc.).
2. Create a new plain text document (if required by your editor).
3. Copy and paste the following text:

SQLCMD.exe -S “{SERVERNAME}” -E -Q “backup database [{DBNAME}] to DISK = ‘{BACKUPFILE}.bak’ WITH INIT, SKIP” -o “{LOGFILE}.log”

Note: This script connects to the Server Instance using Windows Authentication and backs up the database using the given parameters. It can only be ran on the local computer for the SERVERNAME provided. This is to say you cannot use this script to perform network backups of remote SQL Server databases.

4. Replace {SERVERNAME} with your server name (ie. “SERVERAPTORA”).
5. Replace {DBNAME} with your database name (ie. “Sample Company”)>

Note: This is the SQL Server Database Name, not your company name. If you are not sure what this value should be, run the Enterprise Server Utility and click the “Backup Database” option. The listed databases are the SQL Server Database Names. Use the name from this list for the database(s) you want to backup using this script.

6. Replace {BACKUPFILE} with the desired backup location (ie. “C:BackupsSample Company”). The “.bak” extension will be used for the generated backup file.

IMPORTANT: This file location must be on the local computer. It can be an external or internal drive, but it cannot be a network drive.

7. Replace {LOGFILE} with the desired backup log location (ie. “C:BackupsSample Company Backup Log”).
8. Repeat the above for each database you would like to backup using this method.
9. Save the file and give it a “.bat” extension (ie. “Sample Company Backup.bat”). You will also want to save it to a location where it can be accessed via 10. Windows Task Scheduler.
11. Run Windows Task Scheduler, create a new task, and then find and select the .bat file that was just created.
12. Set the Task to run as the Windows “Administrator” user, or another user that is part of the SQL Server Admins group for the provided SQL Server Instance.
13. Every time this task runs it will overwrite any existing backup at the location provided. A log file will be generated, which will provide any details about errors that occurred. You can manually double-click the .bat file at any time to run the script on demand.

If you have any additional questions about the above script or scheduling a task in Windows, please visit Microsoft Support or see an IT specialist.

Please note that our support staff is not available to assist with the contents of this issue. This information is being made available “AS-IS”. Aptora takes no responsibility for problems caused from using the above information incorrectly, or without full understanding of the technology.

Related Content

Database Configuration, Maintenance, and Backups

Automatic Database Backup

Database Configuration, Maintenance, Optimization, and Backups Tips

Introduction to Database Maintenance and Optimization

IMPORTANT: Your database may not be getting backed up. If you are not on Aptora’s hosting plan, please read this important help topic.

Your Total Office Manager (and related software such as Aptora Mobile II) company information is stored in a Microsoft(r) SQL Server(r) file. That file needs regular maintenance and backups. Improper database maintenance is the leading cause of slow software performance and stability problems.

This article is meant for IT professionals familiar with MS SQL Server. It explains what database maintenance is and why it is important. We cover the basics of database backups and offer some database configuration tips.

IMPORTANT NOTE: Aptora takes care of this for all hosted (cloud) users. If you are hosted (on the Aptora cloud), you do not to read any further.

Summary of Steps

  1. Users need to setup a backup system within SQL Server. It is not sufficient to just backup the computer. If that is all you do, the company file (database) will NOT be backed up. If the hard drive fails, the company information is gone forever.
  2. They must back up Document Link related files.
  3. SQL Server must be carefully setup. There are a lot of options, settings, and preferences. Getting them wrong means slow performance or worse. Most companies and IT people get them wrong.
  4. They must also setup SQL Server so that it will run maintenance on the database including  Reorganize index pages, rebuild indexes, update statistics on the indexes, shrink data and log files by removing empty pages, backup database and transaction log, perform internal consistency checks, and cleanup tasks.
  5. They must also carefully update MS SQL Server for speed, security, and stability reasons.

We take care of all of this for our cloud users. Read no more. :)

Tables, Queries, Indexes, and Keys

Your Total Office Manager data is stored in a Microsoft SQL Server Database. That database includes tables. Tables look a lot like worksheets in MS Excel. This is where the bulk of your database is stored.

When Aptora software needs information, it runs a query. A database query is a request for data from a database. Many times, the request is to retrieve data; however, data can also be manipulated using queries.

In order to make queries faster, databases include indexes. Inside indexes are Keys. Keys might be thought of as a “card catalog” or a set of bookmarks.

A database index allows a query to efficiently retrieve data from a database. Indexes are related to specific tables and consist of one or more keys. A table can have more than one index built from it. The keys are a fancy term for the values we want to look up in the index. The keys are based on the tables’ columns.

We Must Maintain Indexes

The performance of your indexes, and therefore your database queries, will degrade as you indexes become fragmented. The Rebuild Index task does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics. As such, it is very important that you schedule this task to run regularly.

On the other hand, the Rebuild Index task is a resource-intensive task. In addition, as an index is being rebuilt, locks will be placed on the index, preventing anyone from accessing it while the rebuilding occurs. Any queries trying to access this index in order to return the required results will be temporarily blocked until the rebuild is complete. As such, the Rebuild Index task is considered an offline activity, to be run when as few people as possible are accessing a database. In general, this means during a scheduled maintenance window.

Run Database Maintenance button

This process will rebuild and reorganize the indexes in your database. We provide a manual method for your convenience.

This process should be done automatically using a system that your IT person sets up with SQL Server Management Studio. You should also be performing a backup of your Total Office Manager database.

Backup Your SQL Server Database

This important topic should be discussed with your IT person.

We generally recommend that you use the Simple Recovery Model. This is done inside Microsoft SQL Server Management Studio. Backing up your server is NOT enough. You must create a backup file of your company database for your backup system to have something to back up.

SQL Server Simple Recovery Model

Source: https://www.mssqltips.com/sqlservertutorial/4/sql-server-simple-recovery-model/

Explanation

The “Simple” recovery model is the most basic recovery model for SQL Server. Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed. Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the “Full” recovery model.

Here are some reasons why you may choose this recovery model:

  1. Your data is not critical and can easily be recreated
  2. The database is only used for test or development
  3. Data is static and does not change
  4. Losing any or all transactions since the last backup is not a problem
  5. Data is derived and can easily be recreated

Type of backups you can run when the data is in the “Simple” recovery model:

  1. Complete backups
  2. Differential backups
  3. File and/or Filegroup backups
  4. Partial backups
  5. Copy-Only backups

MS SQL Server Editions and Versions

There are several editions available. Most of our users will be able to use the free version called SQL Server Express. You should use version 2016. It is possible that version 2008 R2 will work but we do not guarantee it. We test using SQL Server 2016 and this edition is much faster than all previous editions.

Microsoft offers SQL Server Standard and Professional editions. The Standard edition is not free and will be required for database files that exceed 10GB. We recommend that you consider the Standard edition when your database file reaches 4GB. That extra margin will allow the Transaction Log file to grow during the day.

Compatibility Level

The compatibility level is associated with each database. It allows the behavior of the database to be compatible with the specific version of SQL Server it is running on.

Have you IT professional check your SQL Server Database Compatibility Level to make sure that it matches your SQL Server Version. This step is very important for speed and stability. It is also critical for Aptora’s software updates to properly update your company database. Aptora takes care of this for all hosted (cloud) users.

SQL Server Version and Native Compatibility Level
SQL Server 7.0 = 70 (Not Compatible)
SQL Server 2000 = 80 (Not Compatible)
SQL Server 2005 = 90 (Not Compatible)
SQL Server 2008 and R2 = 100 (Not Compatible)
SQL Server 2012 = 110 (Not Recommended)
SQL Server 2014 = 120 (Not Recommended)
SQL Server 2016 = 130 (Recommended)
SQL Server 2017 = 140
SQL Server 2019 = 150

Recommended Backup Software

SQL Server Standard Edition does not include the ability to back up its files. You can purchase an inexpensive program to do this for you. We use and like SQL Backup Master. We are not affiliated with the company that makes this product.

Related Content

Creating Automatic Backups

How to Create a Backup w/ Server Utility

Scroll to Top

Request a Personalized Demo

Fill out the form below, and we will be in touch shortly.
Demo Form 121422

What number is equal to 7 plus 4?

By entering your information above and clicking the “Agree and Get Started” button, you agree to our Terms of Use and Privacy Policy that we may contact you, by SMS, at the phone number and email address you provide in this form in accordance with our Terms of Use. Promotion Terms & Conditions apply.

/* */