Other methods for moving a geodatabase in SQL Server

There are several options within Microsoft SQL Server to move a database. They include using detach and attach, backup and restore, or the Copy Database wizard.

No matter which of the methods you use to move your SQL Server database, you cannot rename the database. When you are restoring a database, for example, you are given the opportunity to restore it with a different name. Don't do this with a geodatabase; you won't be able to connect to it.

All object names in the geodatabase system tables are fully qualified with the database name. In addition, many stored procedures use a three-part naming syntax in their code, which follows the format <database>.<owner>.<object>. If the database name changes, you will not be able to execute these procedures.

If you are using a multiple spatial database instance—that is, the SDE database and other databases are all part of a single geodatabase—you must move the entire geodatabase. Every database that is referenced in the ArcSDE and geodatabase tables must be accounted for, or the target data may be inoperable.

Use detach and attach

The easiest way to move a geodatabase stored in a SQL Server database is to detach the database from the source server and attach it to the destination server. Keep in mind that

• No one can be connected to the database when you detach.
• Detach executes a clean shutdown of the database.
• Detach/Attach can be faster than a database backup and restore.
• Using detach/attach creates identical databases on the source and destination servers.
• This method is useful for moving large amounts of data.
• You must have, at a minimum, db_owner permission in the database to detach it from the SQL Server instance.
• You must have CREATE DATABASE permission to attach a database to the SQL Server instance.
• For ArcSDE geodatabases for SQL Server licensed through ArcGIS Server at the Enterprise level, you can specify all data and log files in the CREATE DATABASE FOR ATTACH SQL command.

Restore backup of another database

Another option for moving a SQL Server database is to do a full backup of the database and restore the backup file to your destination server. Keep the following in mind:

• You cannot change the logical file name during the restore operation.
• Users can be attached during the backup process but not during the restore process.

For details on restoring backups of ArcSDE for SQL Server databases, consult your SQL Server DBMS documentation.

Copy Database wizard (Management Studio)

Note:

Applies to geodatabases created with an ArcGIS Server Enterprise license only

SQL Server Management Studio (SQL Server 2005 and 2008) provides a Copy Database wizard you can use to move databases. Some things to consider when using the Copy Database wizard are as follows:

• You can copy multiple databases.
• The Copy Database wizard can't be used to make a copy of a database on the same instance.
• You can't exclude user logins from the Copy Database wizard, so they are transferred to the copied database with new server user IDs (SIDs).
• The wizard requires that the source and target servers be connected.