SQL Server backups


Applies to geodatabases created with an ArcGIS Server Enterprise license only

When you perform a backup, 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 restore 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 changed 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 following SQL Server documentation:

SQL Server 2008 Books Online

SQL Server 2005 Books Online

Related Topics