Backing Up Your Acuit Pinpoint System

The following data must be backed up to completely back up your Acuit Pinpoint system:

"CommonApplicationData\Acuit Pinpoint" Folder (including all files and folders)
Acuit Pinpoint Databases

See the following for more information.

"CommonApplicationData\Acuit Pinpoint" Folder

All files and folders within the "CommonApplicationData\Acuit Pinpoint" folder should be backed up.

The CommonApplicationData folder is the directory that serves as a common repository for all application-specific data that is used by all users. The specific location can vary, depending on the operating system. To view the folder location, type the following command from the Command Prompt:

echo %ALLUSERSPROFILE%

On recent versions of Microsoft Windows, the CommonApplicationData folder is usually "C:\ProgramData", so the folder to back up would be "C:\ProgramData\Acuit Pinpoint". Note that the "C:\ProgramData" folder is hidden by default.

Acuit Pinpoint Databases

All Acuit Pinpoint databases should be backed up regularly. Acuit Pinpoint databases contain all of the data for units tracked within various lines managed by Acuit Pinpoint. To view the list of Acuit Pinpoint databases, view the Acuit Pinpoint web pages, go to the "Admin" section, and click "Databases". Each database in the list should be backed up.

Recommended Database Backup Procedure

Regular automatic backups should be scheduled so that full backups occur on a regular basis, with transaction log backups occurring regularly between full backups.

The recommended way to set up database backups is to use the Maintenance Plan Wizard from Microsoft SQL Server Management Studio to create a maintenance plan with three subplans to perform full, differential, and transaction log backups, along with cleanup of backup files. The general procedure is as follows:

Start the Maintenance Plan Wizard and proceed past the start page if it appears.
On the "Select Plan Properties" page:
1. Enter a name for the plan as desired (e.g., "PinpointBackup").
2. Select "Separate schedules for each task".
On the "Select Maintenance Tasks" page:
1. Check "Back Up Database (Full)", "Back Up Database (Differential)", and "Back Up Database (Transaction Log)".
On the "Select Maintenance Task Order" page:
1. Leave the order as-is, with "Full" first, then "Differential", and then "Transaction Log".
On the "Define Back Up Database (Full) Task" page:
1. Select the desired databases. Choosing "All user databases" is recommended; alternatively, each of the Acuit Pinpoint databases can be selected.
2. Indicate the desired backup folder. The default location is the "Backup" folder in the SQL Server installation location, which would cause backup files to be on the same disk as the database files, which is normally not desirable. This should normally be changed so that backup files are written to a location on another disk and/or server. If the backup files are saved to the default location, then these files should be backed up via some other process to some other location.
3. Check "Verify backup integrity".
4. Click the "Change" button to specify a schedule.
On the "Job Schedule Properties" page that appears:
1. Make sure the schedule type is "Recurring".
2. Indicate the desired schedule for full backups. For example, weekly, every 1 week on Sunday, once at 12:00am, with no end date.
On the "Define Back Up Database (Differential) Task" page:
1. Select the same database(s) that were chosen for the "Full" task.
2. Indicate the desired backup folder, which should normally be the same folder as for the "Full" task.
3. Check "Verify backup integrity".
4. Click the "Change" button to specify a schedule.
On the "Job Schedule Properties" page that appears:
1. Make sure the schedule type is "Recurring".
2. Indicate the desired schedule for differential backups. For example, weekly, every 1 week on Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday, once at 12:00am, with no end date.
On the "Define Back Up Database (Transaction Log) Task" page:
1. Select the same database(s) that were chosen for the "Full" task.
2. Indicate the desired backup folder, which should normally be the same folder as for the "Full" task.
3. Check "Verify backup integrity".
4. Click the "Change" button to specify a schedule.
On the "Job Schedule Properties" page that appears:
1. Make sure the schedule type is "Recurring".
2. Indicate the desired schedule for transaction log backups. For example, daily, every 1 day, occurs every 1 hour starting at 1:00:00am and ending at 11:59:59pm, with no end date.
On the "Select Report Options" page, configure as desired.
On the "Complete the Wizard" page, click Finish.
Edit the newly-created maintenance plan.
Select the first subplan (which performs the full database backup).
From the Toolbox, select the "Maintenance Cleanup Task" and drag it into the design pane, dropping it under the "Back Up Database (Full)" task.
Right-click the "Maintenance Cleanup Task" and choose Edit.
On the "Maintenance Cleanup Task" dialog:
1. Select "Backup files".
2. Select "Search folder and delete files based on an extension".
3. In the "Folder" field, enter the backup location used for the full and differential backups.
4. In the "File extension" field, enter "bak" (without quotes or a period before the extension).
5. Check "Delete files based on the age of the file at task run time" and then indicate the desired age (e.g., 8 days).
Select the "Back Up Database (Full)" task and drag the green arrow down to the "Maintenance Cleanup Task" task.
From the Toolbox, select the "Maintenance Cleanup Task" and drag it into the design pane, dropping it under the first "Maintenance Cleanup Task" task.
Right-click the new "Maintenance Cleanup Task" and choose Edit.
On the "Maintenance Cleanup Task" dialog:
1. Select "Backup files".
2. Select "Search folder and delete files based on an extension".
3. In the "Folder" field, enter the backup location used for the transaction log backups.
4. In the "File extension" field, enter "trn" (without quotes or a period before the extension).
5. Check "Delete files based on the age of the file at task run time" and then indicate an appropriate age (e.g., 1 day).
Select the first "Maintenance Cleanup Task" task and drag the green arrow down to the second "Maintenance Cleanup Task" task.
Right-click the second "Maintenance Cleanup Task" task and click Copy.
Select the second subplan (which performs the differential log backup).
Right-click in the design pane, under the "Back Up Database (Differential)" task, and click Paste.
Select the "Back Up Database (Differential)" task and drag the green arrow down to the "Maintenance Cleanup Task" task.
Save the maintenance plan.
Make sure that the SQL Server Agent service is running and set up to start automatically.

SQL Server Transaction Log Growth

Caution

Proper backups of SQL Server databases must be configured to avoid serious problems caused by growing transaction logs, resulting in potential Acuit Pinpoint Server downtime!

By default, SQL Server databases use the "Full" recovery model, which allows for the most flexibility in recovering databases should disaster recovery become necessary. With this recovery model, database transaction logs will grow until the transaction logs are backed up, which allows the space in the transaction log to be reused. If these logs are never backed up, they will grow indefinitely. When a transaction log fills up, SQL Server will initiate an automatic transaction log grow process, which can take a long time and during which Acuit Pinpoint Server will be unresponsive to workstations and web application requests for the affected lines. Eventually, all available disk space can be used, which will cause Acuit Pinpoint Server stop functioning altogether.

One symptom of full transaction logs is that Acuit Pinpoint Workstation starts displaying unexpected errors from Acuit Pinpoint Server. Checking the Acuit Pinpoint Server log reveals errors like "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Checking the SQL Server application logs reveals errors like "MSSQLSERVER: Autogrow of file 'DatabaseName_log' in database 'DatabaseName' was canceled by user or timed out after 30123 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."

Note that full database backups will not cause transaction log space to be freed up or transaction logs to be truncated. For more information, see "Transaction Log Truncation" in SQL Server Books Online.

One way to avoid this situation is to change the Acuit Pinpoint database(s) to use the "Simple" recovery model. With this model, transactions are retained in the log until they successfully complete. Thus, transaction logs do not need to be backed up and they will not grow. However, with this model there is less flexibility in recovering data should disaster recovery be necessary. Restoring the latest full backup is likely the only option. This approach can make sense, though, for databases that exist for reference only or for testing. When using the "Simple" recovery model, only full database backups need be performed.

Normally, the "Full" recovery model should be used for Acuit Pinpoint databases, and the backup procedure recommended in the previous section should be used.

To determine whether transaction logs are being backed up properly, from Microsoft SQL Server Management Studio, right-click a database and click "Properties". In the "General" page, the last database backup and last database log backup date and times are shown. The database recovery model is shown on the "Options" page.

For a quick overview of transaction log usage for all databases, the following T-SQL command can be used:

DBCC SQLPERF(logspace)