If you want to move the contents of one geodatabase to another database on the same Microsoft SQL Server instance, you can create a second geodatabase and move the data. If you want to move the geodatabase to a new SQL Server instance, you can move the whole database using one of several options available through SQL Server.
Move data to another geodatabase on the same SQL Server instance
Database and geodatabase names must be unique within the same SQL Server instance; therefore, if you want to move your data to a different geodatabase on the same instance, create a second geodatabase on the instance and move your existing data to the new geodatabase. You can move the data in ArcGIS by copying and pasting it, using the Export tool, using the Extract Data wizard, or using XML workspace documents.
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.
Move a database to a new SQL Server instance
If you want to move a geodatabase to a new SQL Server instance, you can use SQL Server tools to move the entire database. Options to move a SQL Server database include detaching the database file from the SQL Server instance and attaching it to a new instance, creating a backup of the database and restoring it to a new SQL Server instance, 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 restore 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 enterprise geodatabases for SQL Server, you can specify all data and log files in the CREATE DATABASE FOR ATTACH SQL command.
Restore a 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 documentation.
Copy Database wizard
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.