Backup strategy for the PI Vision database

Your strategy should be tailored to your environment, but ideally you need to back up your AVEVA PI Vision SQL Server database every day. Standard maintenance best practices include:

• Backups of logs

• Daily data backups

• Periodic re-index and update statistics on all databases, and always after importing displays, deleting import folders, or deleting many displays

To develop your strategy, refer to the appropriate documentation linked below based on the database platform that you use for your PI Vision database:

On-premises SQL Server - Back Up and Restore of SQL Server Databases

Azure SQL Database - Automated backups in Azure SQL Database

Amazon RDS for SQL Server: Support for native backup and restore in SQL Server

On-premises SQL Server backup information

Use Microsoft SQL Server Management Studio or the sqlcmd command utility.

We recommend that you change your database from the simple recovery model to the full recovery model to allow point-in-time recovery. The installation kit configures the 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 database to the full recovery model, the 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.

Consider these further points as you design your backup strategy:

• Place the backup 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 the C:\ drive. Use another drive, such as a network drive, or a subfolder.

• Frequency of backup depends on your application; nightly backups are usually best. You can set the timing and frequency of backups and whether full or differential backups are done. Daily, full backup is recommended.

• You can put contents similar to the example below in a file called BackupPIVision.sql and schedule as a task, shown in the command below. Note that you will need to change DestFile to the directory that you have created to store backups and change the database name from PIVisionDB to the name of your AVEVA PI Vision database.

  Contents of BackupPIVision.sql file:

  declare @DestFile varchar(128) = N'E:\Backups\PIVisionBackupDB.bak';
  backup database PIVisionDB
  to disk = @DestFile;
  go

  Command for scheduled task:

  sqlcmd -S .\SQLEXPRESS -i "E:\Backups\BackupPIVision.sql"

  You need the sysadmin, db_owner, or db_backupoperator role (the least privilege is the best security practice).