PI AF backup considerations

Note: If you are using a cloud database platform, refer to the official documentation on automatic backups. See Enabling automatic backups (Amazon RDS), Automated backups in Azure SQL Database, and Automated backups in Azure Managed Instance.

Perform backups of your database on a regular basis. Use Microsoft SQL Server Management Studio or the sqlcmd command utility.

Consider these points as you design a backup strategy:

• Standard maintenance best practices include log backups, daily data backups, and periodic re-index on all databases.

• When the SQL Server Agent is available (all editions of SQL Server except Express), PI AF will automatically install and schedule a nightly SQL Server backup. Refer to the Maintenance.sql file located in the %PIHOME64%\AF\SQL directory.

• Releases beginning with PI AF 2.1 schedule a SQL Server Agent job to back up data and logs. SQL Server replication requires the SQL Server Agent on the publisher (primary) instance.

• Frequency of backup depends on your application; nightly backups might be best. The default backup does a complete backup every night at 0315, local time. However, you can change the time and can change the frequency and whether full or differential backups are done.

• Place the back up file on a different physical disk from where the SQL Server data is located. You might not be able to write to the root folder of C:\ drive. Use another drive, such as a network drive, or a subfolder.

• SQL Express does not include a job scheduler, so you need to use a Windows utility to schedule the backup. You can use the following command to run the backup:

  sqlcmd -S <SQLINSTANCE> -d <PIFD> -Q "EXEC dbo.usp_backup @outpath = N'', @alwaysfullbackup = 1;" -E

  You will need the sysadmin or db_owner role. The least privilege is the best security practice.

  <PIFD> is the PI AF SQL Server database. By default the name is PIFD.

• Back up System Databases regularly. System Databases contain metadata for the PI AF SQL Server database (typically called PIFD). The PI AF scheduled backup backs up the PIFD database only. For more information on back up and restore of System Databases, refer to this Microsoft article: Back Up and Restore of System Databases (SQL Server).

• We recommend that you change your PIFD database from the simple recovery model to the full recovery model to allow point-in-time recovery. The PI Server install kit configures the PIFD database with a simple recovery model by default. With this simple recovery model, transaction logs cannot be backed up and point-of-failure recovery is not possible. If you set the PIFD database to the full recovery model, the PIFD transaction logs are also backed up. The transaction logs are truncated so they do not grow without bounds and either point-in-time or point-of-failure recovery is allowed.

• If you are using the Audit Trail feature and you want to include CDC tables in the moved or restored database, you need to use the "KEEP_CDC" command in your command line to Restore and/or Move the PIFD database.

• For information on SQL Server maintenance jobs, refer to PI AF server's SQL Server maintenance jobs.

• For more information on restoring a PI AF SQL database from a backup, see the Knowledge Base article: How to restore the AF SQL Database from a backup.