SQL Server backups

License:

Applies to geodatabases created with ArcGIS for Server at the Enterprise license level only

When you perform a backup of an on-premises Microsoft SQL Server database, you back up data, changes, or transaction logs. The difference between the three depends on how much you are willing to lose in the event of system failure.

Database, file group, and file backups back up the entire entity. They are the foundation for any backup and restoration strategies. Differential backups back up only changes made to the data since the last full database, file group, or file backup. Transaction log backups back up only the transaction log.

Differential backups record changes made to a database, file group, or file since the last full database backup. Changes are identified through the differential changes map, which represents all changed extents in a database. If an extent's value is 1 within the map, that extent is backed up. During the next full backup, any changed values within the map are reset to 0.

Transaction log backups back up the transaction log and control the log's size. Transaction log backups are only useful in full or bulk-logged recovery models.

During a transaction log backup, the entire log is first backed up. All committed or rolled-back transactions that precede the oldest active transaction (MinLSN) and reside in a previous virtual log are truncated from the transaction log. This controls the size and growth of the transaction log. In a simple recovery model, in which the transaction log cannot be used for recovery, backup is used to truncate the log.

ArcSDE single spatial databases can be backed up and recovered as any other SQL Server database. If you have deployed a multidatabase model (one sde database holds metadata for all spatial databases) the process becomes more complex. In this configuration, all spatial databases depend on the sde database, so you must back up all databases as one.

You can automate your backups using SQL Server Management Studio. Backups can be automated through SQL Server jobs or the SQL Server maintenance wizard.

It is important that you read the backup and recovery documentation available for SQL Server. This information can be found in the SQL Server Books Online.