Restore backup of another database

To move a geodatabase stored in a DB2 database management system (DBMS) and ensure you include all the necessary database objects, back up the database that you want to transfer and restore it to your target server. Doing this will achieve the following:

• Move all objects in the database.
• Allow you to specify alternate paths for your table space containers.
• Allow you to use backup and restore scripts if you need to move the database on a regular basis.

NOTE: You need to create your database on the destination machine with the correct code page before you perform the restore operation if the source and destination aren't using the default code page of 1252 (Windows). If you do not do this and your production code page is different from that of the source, the restore utility assumes the default code page. This results in a SQL2548N error.

Restore backup of another database

The easiest way to move a geodatabase stored in an Informix DBMS and ensure you get all the necessary database objects transferred is to make a full backup of the database you want to transfer and restore it to your destination server.

dbexport and dbimport utilities

If you need to copy an entire database, Informix provides the dbexport and dbimport utilities. These utilities unload an entire database, including its schema, and re-create the database.

These utilities move the entire database. If you want to move selected tables or selected columns of a table, you must use different utilities.

You can use Oracle's export and import utilities to transport ArcSDE data. Be sure all necessary objects are exported along with the data tables.

Remember, ArcSDE feature classes and rasters exist as a number of tables and related database objects. Leaving any related object behind renders the data inoperable in the target database. Therefore, it is recommended that the export include the data owner's entire schema.

The entire ArcSDE administrator's schema also needs to be included in the export/import operation, because ArcSDE data objects rely on the metadata repository in the ArcSDE administrator's schema to manage the data and provide structure to it.

Transportable table spaces

One way to keep tables together is to use Oracle transportable table spaces. This is especially efficient when moving large databases. In the Oracle releases prior to 10g, the operating system architectures of the source and destination servers had to match. In other words, both servers had to be big endian or both had to be little endian. The operating systems for both servers also had to be either 32 bit or 64 bit. For Oracle 10g servers transferring to an Oracle 10g server, you do not have to be concerned with matching system architecture, because the Oracle 10g recovery manager (RMAN) utility can be used to convert the data files.

Things to consider when transporting table spaces are as follows:

• Each table space must be self-contained—tables and their indexes must be in the same table space before that table space can be transported. In other words, all the table's dependencies must share its table space.
• The size of the transfer media must accommodate the data files to be transported.
• You cannot import the table space if the destination database already has a table space with the same name.
• You cannot import the table spaces if a table or index stored in the table space already exists on the destination server. Tables and indexes must be uniquely named within their schema.
• Exports must be performed by a user with EXP_FULL_DATABASE privileges.

You can move individual PostgreSQL databases using the pg_dump command, or use the pg_dumpall command to move all databases on a PostgreSQL instance plus users and permissions.

The pg_dump command can extract the database definition (schema) and data or just the database schema to a structured query language (SQL) script or an archive file. If you extract to an archive file, you can use the pg_restore command to rebuild the database on another PostgreSQL instance. If you extract to a SQL script, run the SQL script on the new server to rebuild the database. The pg_dumpall command can only extract to a SQL script, then restore to another PostgreSQL instance by running the SQL script.

The following are some guidelines for moving PostgreSQL databases with pg_dump or pg_dumpall:

• If you use object identifiers (OIDs) in your user-defined data, be sure to specify the -o option to include the OIDs in the dump.
• Table spaces must be re-created.
• If you output to TAR archives, the text version of any individual table cannot be larger than 8 GB.
• After rebuilding the databases, run ANALYZE to update database statistics.
• Because pg_dumpall reconnects to the PostgreSQL server for each database, it prompts you for a password each time (if you are using password authentication).
• Connect as a superuser to run pg_dump or pg_dumpall successfully.

For specific information on how to use pg_dump, pg_dumpall, or pg_restore, consult the PostgreSQL documentation.

No matter which of the methods below 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.

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 SDE and GDB 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.
• SQL Server 7.0 system databases are not compatible with SQL Server 2000 or later release databases.
• You must have system administrator privileges to detach and attach.
• For ArcSDE geodatabases for SQL Server licensed through ArcGIS Server Enterprise, you can specify all data and log files in a comma-delimited list in the sp_attach_db stored procedure.

To learn how to detach and attach a SQL Server Express database from an ArcSDE database server, see Detaching and attaching a geodatabase on a database server.

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 in mind that

• 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.

Backups of ArcSDE geodatabases on SQL Server Express (ArcSDE database servers) are always full backups. To restore a backup from one database server to another, perform the following steps:

1. Copy the .mdf database file to transfer media, such as a memory stick. If you created the geodatabase in the default location, you will find this file at C:\Program Files\Microsoft SQL Server\MSSQL\Data.

NOTE: The database is actually made up of an .mdf and .ldf file. However, the attach operation you perform on the other database server only requires the .mdf file; a new .ldf file is created automatically.

2. Insert or attach the transfer media to the computer on which resides the database server to which you want to transfer the database.
3. Copy the database file from the transfer media to the hard drive.
4. In ArcCatalog, connect to the database server to which the geodatabase is to be attached. Be sure you are connected as a database server administrator.
5. Right-click the database server in the Catalog tree and click Attach.
6. Browse to the .mdf file of the database you transferred and click OK.

Copy database wizard (Enterprise Manager or Management Studio)

NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only

The SQL Server Enterprise Manager (SQL Server 2000) and Management Studio (SQL Server 2005 and 2008) provide 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.

Transfer schema and data with DTS

NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only

For SQL Server 2000, you could also use data transformation services (DTS) to transfer the database; however, this is a more involved process. You might consider using this if you have to do the migration regularly and want to automate it, because you can control exactly what objects are moved by writing a query that specifies the data to transfer. Other things to consider when using DTS are as follows:

• You can move just schema, just data, or schema and data.
• DTS is slower than detach/attach or backup and restoration of a database.
• If you move the logins with the database, they are created with blank passwords and assigned new SIDs.
• You can use DTS while users are logged in to the database.
• DTS requires that the source and target servers be connected.