Database Backup

Ensuring that a proper backup plan is in place for the company's DEACOM database is a critical responsibility of any company's IT team. Since the DEACOM database sits on Microsoft SQL Server, a vast array of database features are readily available. The following page from Microsoft SQL Server's help library will overview all of the recovery models available: Microsoft SQL Server - Backup and Recovery Guide.

Additionally, implementing a high-availability solution or a replicated server through Microsoft SQL Server's tools: Microsoft SQL Server - High Availability Solutions Guide and Microsoft SQL Server - Replication Guide is recommended.

Establishing and configuring the Microsoft SQL Server backup plan is not part of DEACOM Support, but a Technical Specialist can be contracted to assist a customer with the project. For inquiries, please email [email protected].

Databases

The DEACOM.ini file in each DEACOM folder points to the DEACOM system database that will be accessed when deacom.exe is executed. The system database points to the DEACOM data and document databases. Each DEACOM instance has two or more databases:

  • The database(s) that contains the company data
  • The database(s) that contains documents (version 14.1 and higher)
  • The database that contains the DEACOM system information

In order to perform the following tasks, the computer must be running SQL Server Management Studio and connected to the SQL Server. The server name can be found by looking at the deacom.ini file in the Deacom\Production folder. The name of the Server is shown on the SERVER= line. These instructions assume the computer is running SQL Server 2008 with the SQL Server Management Studio installed.

Performing a manual database backup

Below is how to manually backup DEACOM SQL databases. It is recommended to have at least one full SQL backup daily of each database within the DEACOM environment. These backup files will then be backed up by the regular backup procedure to tape or external hard drive. It is recommended that the SQL backup process be performed automatically using a maintenance plan or script procedure. How long to keep backups is a company decision. Keep in mind the production folder\programs must match the database version. For example, if are keeping a historical 14.1 backup, be sure to have a backup of the 14.1 production folder at that time.

Note: A full version of SQL is necessary to setup a maintenance plan.

  1. Log into SQL Server.
  2. Launch SQL Server Management Studio, enter/confirm the SQL server name, login, and password, then click "Connect" to connect to the SQL Server.
  3. Click the "+" sign next to Databases.
    1. Note: The dxcomp4 table in the system database will show all related databases. The c4_name field has the name of database that contains the company data. The c4_docdb field has the name of the database that contains documents (version 14.1 and higher).
  4. Highlight then right-click the database to be backed up, select "Tasks", then "Back-up".
  5. Confirm the "Backup Database Name" is correct, "Backup Type" is Full, and "Destination" is Disk
  6. Highlight any existing destinations and click "Remove".
  7. Click "Add" to add a new backup destination, then "…" to choose the destination.
  8. After selecting the backup path, enter a name for the file, such as CompanyNameMMDDYYYY.bak, where CompanyName is the database name and MMDDYYYY is the month, day, and year of the backup.
  9. Note: The .bak is required.
  10. Click "OK" until the user is returned to the main screen.
  11. Click "OK" to begin the backup. Progress of the backup can be viewed in the lower left-hand corner.
  12. After the backup is completed, click "OK" on the prompt.
  13. Repeat steps 4-11 for each database.

Creating a SQL Maintenance Plan

Maintenance Plans in Microsoft SQL Server Management Studio are used to schedule backups to automatically run. From within Microsoft SQL Server Management Studio, double click the Management folder. To view existing Maintenance Plans, click the “+” sign next to the Maintenance Plans folder. To create a new Maintenance Plan, follow the instructions below.

Adding a SQL Maintenance Plan

  1. From within Microsoft SQL Server Management Studio, double click the Management folder, right click the Maintenance Plans folder, and click “Maintenance Plan Wizard”, which will walk the user through creating a new Maintenance Plan.
  2. Click “Next” to get past the summary window.
  3. Enter a Name for the new Maintenance Plan – for example, “DatabaseName Daily Backup”.
  4. Enter a Description (optional).
  5. Click the “Change…” button to set a Schedule.
  6. The Schedule type should be set to “Recurring” by default.
  7. Select “Daily” under Occurs in the Frequency settings.
  8. The time should be set to before regular network backups, so the finished backup can be backed up to tape or disk.
  9. No changes to Duration should be needed and no End Date should be automatically selected.
  10. Review the Summary and make changes if needed.
  11. Click “OK” once all items are confirmed accurate.
  12. Once the schedule setup is completed, click “Next” to finalize the Plan Properties section.
  13. From the list of available maintenance tasks, at minimum, select “Back Up Database (Full)”.
  14. Once all tasks for the plan are selected, click “Next” to finalize the Maintenance Tasks section.
  15. If more than one task was selected in Step 7, reorganize the listed tasks, if desired, by using the “Move Up…” and “Move Down…” buttons then click “Next” to finalize the Maintenance Task Order section.
  16. The user will now be prompted to configure the selected maintenance task (the “Back Up Database (Full)” task selected in Step 7).
  17. On the General tab:
  18. Select the Databases to be backed up from the pick list.
  19. Confirm Back up to is set to “Disk”.
  20. On the Destination tab:
  21. check “Create a sub-directory for each database” (optional).
  22. Click the “…” button next to Folder to specify where the backups will be created. The default is the Backup folder created during the SQL install.
  23. On the Options tab:
  24. Set the Backup set will expire values, if desired. This option will prevent files from being overwritten by SQL.
  25. Check the “Verify backup integrity” box (suggested, not required).
  26. Once the backup plan is defined, click “Next” to finalize the maintenance task configuration.
  27. In the Report Options window, uncheck the “Write a report to a text file” box.
  28. If desired, check the “E-mail report” box then complete the required setup. This will send a summary of what the maintenance task accomplished every time it runs.
  29. Once report preferences are set, click “Next” to finalize the Report Options.
  30. Review the summary provided then click “Finish” to finalize the maintenance plan and perform the actions listed.
  31. Click the “Close” button to exit the Maintenance Plan Wizard.
  32. Right click the newly created Maintenance Plan in the Maintenance Plans folder and click “Execute” to confirm the job is operational.
    1. Note: Maintenance Plans can be manually modified after they are created by right clicking the desired Plan and clicking “Modify”.
  33. Adding a SQL Maintenance Cleanup Task
  34. Backups will not be removed automatically unless a Maintenance Cleanup Task is created for the Maintenance Plan. Below are instructions to add a Maintenance Cleanup Task to an existing Maintenance Plan. Make sure the SQL Toolbox is showing. See below Note regarding Toolbox troubleshooting.
  35. Right click an existing Maintenance Plan and click "Modify".
  36. Drag Maintenance Cleanup Task from Toolbox over to right side of screen and drop.
  37. Click on "Back Up Database" box, green arrow will appear.
  38. Drag green arrow to connect Back Up Database box to Maintenance Cleanup Task.
  39. Double click "Maintenance Cleanup Task" to set properties.
  40. Under File location, enter Folder: location where backups are stored.
  41. Enter File extension: bak.
  42. File Age: Set desired settings.
  43. Close and Save Maintenance Plan.

Note: If Toolbox is not displayed, click View > Toolbox. If there are no tasks in the Toolbox, click Tools > Choose Toolbox Items.

Confirming backup status

Confirm the backup(s) are running by checking the backup folder for backup files. Right click the Maintenance Plan and select "View History" to show the success or failure logs. The best way to confirm the backups are working is to restore the data into another database.

Note: Confirm the backups are working by restoring data to the test system.

FAQ and Diagnostic Tips

What recovery model should the databases be using?

The recovery model used is determined by the customer. If your plan in an emergency is to use your last full backup then Simple should be used. Full recovery model can impact performance and requires additional setup and maintenance.