Exercise 11: Detach a geodatabase from the database server

Available with Standard or Advanced license.

Complexity:

Beginner

Data Requirement:

ArcGIS Tutorial Data for Desktop

Goal:

Learn how to detach a geodatabase from a database server.

To move a geodatabase from one database server to another, you can detach it from the database server; copy the database file (the .mdf file) over the network or onto transfer media, such as a thumb drive; and paste it onto the destination server. Once there, you can attach the geodatabase to the second database server.

Some examples of situations when you might do this include the following:

• If you use the data from the geodatabase on a database server on your desktop computer, but want to take the geodatabase into the field to collect data, you could detach the geodatabase from the database server on your desktop computer, transfer the database file to your laptop, and attach the geodatabase to the database server on your laptop.
• When ownership of a geodatabase is transferred, such as when an employee leaves the company, you could move the geodatabase from the database server on the former employee's computer, copy it to the new owner's computer, and attach it to a database server there.
• When you hire consultants to collect or create datasets for you, you can request they deliver the data in a geodatabase in SQL Server Express. Then, when they send you the files, you can attach them to your database server.

Note:

Detaching a geodatabase from the database server does not delete the database files, but it removes references to the database from the SQL Server Express instance.

When sharing geodatabases across database servers, be aware of who owns the data and has permission to work with it. This is important because geodatabases on database servers use Windows-authenticated logins. When you transfer the geodatabase to a new computer, local Windows logins from the old computer won't exist on the new computer. Similarly, if you use network logins and transfer the geodatabase to a database server outside the network, those logins will not exist.

How you deal with this depends on your workflow. If you know ahead of time that you are going move the geodatabase around a lot, you might want to create all the data in the geodatabase while logged in as the dbo user. The dbo user and schema are always present in database servers; therefore, even if you move the geodatabase to a database server on a different network, you can still log in as dbo, add new users to the database server, and grant them permissions to the data.

Tip:

Note that the dbo user has elevated privileges in the database server; therefore, for security reasons, designate as few dbo users as possible if you need to store your data in the dbo schema for portability reasons. For example, if possible, have only one person load data. That person can then grant data access to other users who need it.

If you hadn't anticipated having to move the geodatabase and the data owners don't exist on the destination database server, you could still log in as the dbo user and perform one of the following set of steps:

1. Add new users to the destination database server.
2. Grant read/write or higher-level access to the geodatabase to the users to whom you want to transfer ownership of the data. Granting these permissions at the geodatabase level allows the users to access all the data in the geodatabase.
3. Ask the users to log in to the geodatabase and either copy the data to a new geodatabase to which they also have read/write access or copy and paste the data in place, giving the pasted datasets and columns new names.

Or:

1. Create a second geodatabase on the destination database server.
2. Copy the data to the new geodatabase.

   Note:

   Dbo is now the owner of all the datasets in the second geodatabase.

3. Add new users to the destination database server.
4. Grant these users access to the datasets.

Detach a geodatabase

Suppose you want to move the buildings08 geodatabase from the database server on your personal computer (PC) to a database server on your laptop so you can take it into the field and collect traffic data there. You could use geodatabase replication to replicate the data to a geodatabase in a database server on your laptop, or you could detach the geodatabase from the database server on your PC, move it to your laptop's hard drive, and attach it to the database server there.

To detach the buildings geodatabase from the database server, do the following:

1. Log in to the computer with your own Windows login.
2. Start ArcCatalog.
3. Expand the Database Servers node in the Catalog tree and connect to the database server. However, do not connect to the buildings08 geodatabase.
4. Right-click the buildings08 geodatabase, point to Administration, then click Detach.

   Caution:

   If any other users are connected to the geodatabase at this time, they will be disconnected to allow you to detach the geodatabase. Be sure other users have logged out of the geodatabase before you detach it from the database server.

5. When the Confirm Detach dialog box appears, take note of the location of the database file; you will need to know where it is so you can copy it to another location or perhaps reattach the database in the future.
6. Click Yes on the Confirm Detach dialog box.

You could now open Windows Explorer, navigate to the location of the buildings08.mdf, then copy it to media or over the network to transfer it to another computer.

For instructions on attaching the geodatabase, see Exercise 3.

You detached a geodatabase from the database server to allow you to move it to another database server.

Feedback on this topic?