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.
