Backing Up PCS7 WinCC Central Archive Server SQL Databases

Overview

The Central Archive Server (CAS) in a Siemens PCS 7 / WinCC installation stores process values, alarms, and long-term logs in Microsoft SQL Server databases. A SQL Server Maintenance Plan is the conventional mechanism to schedule full, differential, and transaction-log backups, but the plan must be aware of which databases legitimately belong to the CAS and which are orphaned remnants from prior host renames, OS images, or project re-compilations. Backing up every database indiscriminately - including stale ones - is the single most common cause of intermittent maintenance-plan failures on a CAS host.

This reference documents the database topology of a WinCC/PCS 7 CAS, the identification procedure for live versus orphaned instances, the safe construction of a Maintenance Plan, and the verification checks that confirm a restore would be functional. All SQL Server procedures below reference the official Microsoft Learn documentation at Back up and restore of SQL Server databases.

Central Archive Server Database Topology

The CAS is a WinCC component that runs as a SQL Server instance and serves as a redundant, long-term store for runtime data. Multiple databases typically exist on a single host:

Identifying Required vs. Orphaned Databases

Before configuring any Maintenance Plan, enumerate the databases that are actually in use by the live WinCC/PCS 7 project.

1. Open SQL Server Management Studio and connect to the CAS instance (default instance name: WINCC or PCName\WINCC).
2. Expand Databases → System Databases and then Databases.
3. For each WinCC_* or CC_* database, run:

   SELECT name, database_id, create_date, compatibility_level,
          DATABASEPROPERTYEX(name, 'IsOnline') AS IsOnline,
          DATABASEPROPERTYEX(name, 'Updateability') AS UpdateMode
   FROM sys.databases
   WHERE name LIKE 'WinCC%' OR name LIKE 'CC%'
   ORDER BY create_date DESC;

4. Cross-reference the result against the live project path. Open the active WinCC project on the CAS and read the database identifier it expects from the project's Computer properties → Database dialog.
5. Mark every database that does not match the current project or current computer name as a candidate for deletion, not backup.

Root Cause: Why "Back Up Everything" Fails

The recurring failure pattern reported by integrators is rooted in the SQL Server Maintenance Plan Wizard interpreting the inclusion list as a snapshot at run time. If a database referenced in the plan no longer exists at execution time (because it was an orphan and was removed, or because the Maintenance Plan was authored against a database that has since been dropped), sqlmaint.exe records an error and may fail the surrounding job step.

Common precursor events that introduce orphan databases:

• OS image / system download on the CAS host restores an older disk state. WinCC re-initialises and creates a fresh database; the older database remains on the SQL instance until manually removed.
• Rename of the ES or CAS computer. WinCC embeds the computer name into the database name; renaming the host without re-projecting the database leaves the old instance behind (typically visible as newPCname\WinCC in the WinCC Explorer even though SSMS still shows the old WinCC_* database).
• PCS 7 project compilation that adds tags, alarms, or archive segments. WinCC Runtime does not always require a new database on a compilation-only change, but significant schema changes (e.g. new archive segments, name changes) can trigger creation of an additional database alongside the existing one.
• Manual detach/attach operations by administrators unfamiliar with WinCC.

Building a Safe Maintenance Plan

The Maintenance Plan Wizard in SQL Server Management Studio builds a job that ultimately calls sqlmaint.exe with a switch list. To avoid the failure mode above, do not use the "All user databases" radio button. Instead, enumerate the live databases explicitly.

1. In SSMS, expand Management → Maintenance Plans and choose New Maintenance Plan.
2. Add a Back Up Database Task. In the dialog, select Specific databases and tick only the live WinCC_CC_... and CC_ProcessValueArchive_... databases identified in the previous section. Leave tempdb and any orphan databases unchecked.
3. Choose the backup type. For a CAS archive, prefer Full weekly plus Differential daily; use Transaction Log only if the recovery model is set to FULL (verify with SELECT recovery_model_desc FROM sys.databases WHERE name = '<DB>').
4. Specify the backup destination as a stable UNC path on a backup server; never on the same physical disk as the SQL data files.
5. Set a retention policy (e.g. Backup set will expire after 14 days) and a verification step (Check backup integrity) - this corresponds to the CHECKSUM option in Microsoft's SQL Server backup documentation.
6. Schedule the plan. The sqlmaint.exe command line generated by the wizard is logged in the Maintenance Plan history; export it for documentation.

Compensation for PCS 7 Project Changes

Whenever a PCS 7 project is re-compiled or downloaded to the CAS, the operator must re-audit the SQL instance before the next scheduled Maintenance Plan run. Use the following checklist:

Removing Orphan Databases

After confirming a database is orphaned (host renames, project no longer references it, last write time older than the live archive cutoff), remove it deliberately rather than allowing it to be backed up indefinitely.

1. Stop the WinCC Runtime and the WinCC Archive Manager service on the CAS.
2. In SSMS, take a final BACKUP DATABASE <OrphanDB> TO DISK = '<archive_path>\<OrphanDB>_final.bak' for audit retention.
3. Run ALTER DATABASE <OrphanDB> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
4. Run DROP DATABASE <OrphanDB>;
5. Verify with SELECT name FROM sys.databases;

Reading the Maintenance Plan Log

The plan history file is the canonical source for diagnosis. Locate it via SQL Server Management Studio → Management → Maintenance Plans → <PlanName> → View History. Common entries and their interpretations:

Restore Verification

A backup is only valid if it can be restored. Schedule a periodic restore drill on a non-production host:

1. Copy the latest .bak file from the CAS backup share to the test host.
2. Run RESTORE HEADERONLY FROM DISK = '<path>\<file>.bak'; to verify the backup set.
3. Run RESTORE VERIFYONLY FROM DISK = '<path>\<file>.bak'; to validate the backup stream without restoring data.
4. Restore the database under a temporary name using RESTORE DATABASE <TestDB> FROM DISK = '<path>\<file>.bak' WITH MOVE ..., RECOVERY;
5. Open WinCC Explorer pointed at the restored DB and confirm tag logging and alarm logging queries return valid data.

The verification steps mirror the recommendations in Microsoft's Back up and restore of SQL Server databases reference, in particular the Media errors and Verify backups sections.

Capacity and Retention Sizing

The CAS database grows roughly with the product of (number of archived tags) × (archive cycle) × (retention). Use a representative calculation:

• Each tag value at a 1-second cycle occupies approximately 16 bytes raw in the compressed archive segment.
• Daily volume per tag ≈ 16 × 86,400 ≈ 1.4 MB (before compression). WinCC compression typically achieves 5×–10×.
• Example: 5,000 tags, 7-day online retention, 365-day archive retention on the CAS → approximately 4–7 GB compressed.
• Plan full backups at 2× this size for breathing room.

Troubleshooting Matrix

Operational Recommendations

• Document the sqlmaint.exe command line of every Maintenance Plan in a controlled location (e.g. the project administration folder). Operators must be able to reproduce the plan after a server rebuild.
• After every PCS 7 download, re-run the database inventory query in Identifying Required vs. Orphaned Databases.
• Treat the SQL Agent service account as a privileged identity. Backups of a CAS typically contain years of process data; protect the share with ACLs that allow only the SQL Agent service account, administrators, and the restore drill operator.
• Maintain a 3-2-1 backup policy: at least 3 copies, on 2 different media, with 1 off-site.
• When migrating the CAS to new hardware, use BACKUP ... WITH COMPRESSION, CHECKSUM to produce a single, verifiable, transferable file rather than detaching and copying raw MDF/LDF files.

FAQ