How To: Backup and Restore the Multiple Database Model for SQL Server

Summary

Geodatabases created at version 8.3 are under what is known as the multiple database model. This includes an SDE database, where repository information is stored, and one or more user databases. These user databases store the business, F, S, and i tables, while the SDE database stores the repository information (metadata) for the spatial data. The combination of the user databases along with the SDE repository database collectively make up an ArcSDE geodatabase.

Note:
It is possible to continue to use the multiple database model at version 9.0 and higher, but keep in mind that it is not the default for the ArcSDE Post Installation wizard. ArcSDE 9.0 and higher geodatabases created by the Post Installation wizard are created to be self contained under the single database model. This means that each database contains the repository tables as well as the business, F, S, and i tables, making each database its own ArcSDE geodatabase. Each database also has a unique service pointing to it that runs on a unique port number.

It is very important to ensure that the databases that make up the geodatabase are always kept synchronized during backup and restore procedures. One piece of the user geodatabase without the other is useless when restored out of synch, because spatial data stored within the database is distributed amongst the user and SDE databases. For this reason, no edits should be made and no new data should be loaded into the geodatabase until backups of all the databases that make up the ArcSDE geodatabase are made.

Procedure

To ensure that there is no data being edited or loaded within the database, the administrator can check to see if there are users connected and temporarily shutdown the service. Try to perform this at a time of day when there is a low volume of users on the network, just as you normally would do with backing up the database. Keep in mind that there could be users connected via direct connect; shutting down the service will not prevent these users from reconnecting.

Check to see if users are connected by using the following command.
```
Code:
 sdemon -o info -I users -i <service> -p <sde_password> 
```
Get users to stop their work and disconnect.
Stop or pause the service to ensure that no new connections can be made.
```
Code:
sdemon -o shutdown -i <service> -p <sde_password> 
```
Proceed with performing the database backups of each database that makes up the geodatabase. Please read the documentation provided by Microsoft to complete this task.

Follow a routine schedule of database backups to ensure that you will be able to restore your geodatabase in the case of an emergency.

When you restore your geodatabase, be sure to restore all the databases that make up the geodatabase and that you restore synchronized versions of the databases. For instance, do not restore the SDE database with backup files created yesterday then restore a user database with backup files created last week. Please consult your Microsoft SQL Server documentation for instructions on how to restore the databases.

Article ID:000009014

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Backup and restore the multiple database model for SQL Server

Summary

Procedure

Discover more on this topic