Methods for moving a geodatabase in SQL Server

There are several options for moving a geodatabase: you can create a new geodatabase and move the data, or you can move the whole database. Microsoft SQL Server has several options to move a database.

Creating a new geodatabase then moving the data

You can create a new SQL Server database, create a geodatabase in it, then move your existing data to the new geodatabase. This can be done using Copy and Paste, the Export tool, the Extract Data wizard, or XML workspace documents in ArcGIS.

Be aware that the user logged in when loading the data to the new geodatabase will own the data. If you want the data to have the same owner in the new geodatabase, have each user move his or her own data.

Moving a SQL Server database

Options to move a SQL Server database include detaching and attaching, backing up and restoring, or using 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.

Use detach and attach

The easiest way to move a geodatabase stored in an on-premises SQL Server database is to detach the database from the source server and attach it to the destination server. Keep the following in mind:

• No one can be connected to the database when you detach.
• Detaching executes a clean shutdown of the database.
• Detaching/Attaching can be faster than a database backup and restoration.
• Detaching/Attaching 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 for 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 an on-premises 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 restoration operation.
• Users can be attached during the backup process but not during the restoration process.

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

Copy Database wizard

License:

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

SQL Server Management Studio 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.