Backup and Restore
Overview
The backup mechanism is implemented either as local backup jobs based on stored procedure or as the Agent Based Backup (ABB) if the tenant is accordingly equipped.
General Restrictions
Either Local Backup or Agent Based Backup can be used, it is not possible to combine those two technologies.
Migration from Local Backup to Agent Based Backup for existing deployments is possible by using the Manage Agent Based Backup Day 2 Action in the portal. Returning from ABB to Local Backup is not possible. Once the tenant is equipped with ABB feature, all new deployments (VMs) will be deployed with an installed Backup Agent and will use ABB as a backup mechanism.
Local Backup
Ad hoc backup can be executed through a member of the database roles db_owner or db_backupoperator.
Backup Process
Backups are executed through stored procedures, which are stored in the scs_AdminDB:
| SP Name | Description |
|---|---|
| sp_Full_Backup_SystemDB | Backup all system DB's |
| sp_Full_Backup_UserDB | Backup all user DB's |
| sp_TL_Backup_UserDB | Backup transaction log for all user's DB without DB in simple mode |
| sp_Cleanup_Files_All | Delete backup files older than 112 hours |
| sp_RenameLogFile | Rename backup log file with date and hour after backup execution |
| sp_Cleanup_History_All | Delete job and backup history older than 30 days |
The implementation is based on the following SQL Agent Jobs:
| Job Name | Description | Schedule |
|---|---|---|
| SCS_SysDB_Backup_Full | Full Backup of system databases | Daily at 18:30 |
| SCS_UserDB_Full_Backup | Full backup of all user databases | Daily at 19.00 |
| SCS_UserDB_TL_Backup | Transaction Log backup for all user databases in full recovery mode | every 3 hours |
| SCS_CleanupFiles | Delete backup files older than 40 hours | Daily at 04:00 |
| SCS_CleanupHistoryAll | Delete job and backup history | Daily at 23:30 |
Restore Process
The restore depends on the needed backup file date. Files not older than 5 days:
Full and transaction log backup can be found under x:\sql_dump\default and are restored directly as copy or overwrite.
You can't execute a restore with replace, but a restore as a copy.
Restrictions
In case of a complete crash of the VM, the last good backups will be available in the VM snapshot.
When a VM is destroyed the snapshot will be available according to the retention time. However, no DB last backups will be automatically started by a delete order.
Single file restore can be done by Swisscom operations through a customer service request ticket.
Agent Based Backup
When Managed MS SQL Product (VM) is deployed with ABB option, the Backup Policy for the current deployment must be chosen from the drop-down list. If you see the policies in the drop-down list, your tenant is equipped with ABB.
The onboarding of ESC tenants with the Agent Based Backup feature is an ongoing process. If no policies are visible in the ABB dropdown list during the deployment, then the agent-based backup is not yet available in your tenant. Please consult your tenant owner for the availability of this feature.
The list of available policies is as follows:
| Policy Name | Description | FULL | TLOG | FULL Retention | TLOG Retention |
|---|---|---|---|---|---|
| P01-FullDaily-TLog15Minutes-Retention90D | For User DBs (high transaction rate, long retention) including System Databases | Daily | 15 min | 90 days | 15 days |
| P02-FullDaily-TLog60Minutes-Retention90D | For Uses DBs (medium transactions rate, long retention) including System Databases | Daily | 60 min | 90 days | 15 days |
| P03-FullWeekly-TLog60Minutes-Retention90D | For User DBs (medium transaction rate, long retention) including System Databases | Weekly | 60 min | 90 days | 15 days |
| P04-FullDaily-TLog15Minutes-Retention30D | For User DBs (high transaction rate, standard retention) including System Databases | Daily | 15 min | 30 days | 15 days |
| P05-FullWeekly-TLog60Minutes-Retention30D | For User DBs (medium transaction rate, standard retention) including System Databases | Weekly | 60 min | 30 days | 15 days |
| P06-FullWeekly-TLog60Minutes-Retention08D | User DBs are backed up with the shortest possible backup retention time, including System Databases | Weekly | 60 min | 8 days | 8 days |
| *P07-No-UserDB-Backup | No User DB backup * | Weekly | - | 8 days | - |
| **P08-E-FullDaily-TLog15Minutes-Retention90D | For VLDB and High turnover DB with higher retention period | Daily | The best effort – 15 min | 90 days | 15 days |
| **P09-E-FullDaily-TLog15Minutes-Retention30D | For VLDB and High turnover DB with lower retention period | Daily | The best effort – 15 min | 30 days | 15 days |
ABB Backup Process
Beside the automated backups managed by the chosen backup policy, there is an additional Backup On Demand action available. This option will run Full, Differential or Log backup on demand depending on the option chosen from the drop-down list.
Additionally, the target User DB and Encryption can be chosen in the same Backup On Demand dialog.
ABB Restore Process
Restore On Demand is available as a separate Day 2 Action.
By choosing this option the user will be asked to choose the Target DB to be restored and then the point in time can be chosen from the list (Full Backups) or can be entered manually using the calendar controls.
Please consider ABB Restrictions chapter for more general limitations and specifics of ABB functionality.
Run On Demand DB Restore is available as 2nd Day action in tenant that are migrated to Cohesity as ABB backend. When using this action user has option to restore database in place or it can be restored with a new name (keeping original database intact).
Manage ABB
Manage ABB option offers the possibility to change the currently active ABB Policy or to equip existing deployment with Local Backup, ABB Agent and policy.
If the deployment had previously only Local Backup active, by choosing the ABB policy, local backup jobs will be deleted, the ABB Agent will be installed on the machine and will be joined to a protection group according to the chosen policy.
Local backups with retention of 5 days from former backup jobs will stay on X: drive after this change. They can be deleted through a service request to Swisscom.
Please consider the ABB Restrictions chapter for more general limitations and specifics of ABB functionality.
ABB Restrictions
When choosing the ABB policy please consider the sizing and expected DB log file utilization. If the Log Backup cannot be taken in the allotted time, the default behavior is that the log backup action will be cancelled due to the backup mechanism limitations and a new log backup will be started. Please consider this especially with policies of 15 minutes Log Backup pace.
When choosing the No UserDB Backup policy, please consider that this action is not reversible. Once chosen this policy will stay on the deployment until it's destroyed.
When choosing the No UserDB Backup policy with DBA mode deployments (where the customer has more access rights), please consider that the policy is based on Trigger and Audit and deleting any of them will break down the mechanism causing problems and potential service outage.
The common Microsoft MS SQL Backup limitations and recommendations apply here as well.
Maintenance tasks and other potentially blocking actions can interfere the backup schedules (especially LOG backups) affecting the point in time recovery availability.
If one would destroy the deployment (VM), there would be VM Snapshots and ABB backups that would not be deleted. In this case, the more restrictive retention time from Snapshots or ABB would apply and recovery would be possible through a service request to Swisscom.