When you copy a Microsoft SQL Server database 7.0 or later database, Access detaches the SQL Server database file (.mdf) connected to your Access project (.adp), copies it to a new database, and then re-attaches it. For more information on detaching and re-attaching a database, see the SQL Server documentation.
To copy a SQL Server database:
- You must have Microsoft SQL Server version 7.0 or later installed on you local computer.
- All users must be disconnected from the database.
- The database must be a single file database.
- The database must not be replicated.
- You must be a member of the sysadmin role on both the source and destination SQL server.
- You must have adequate disk space to create the SQL Server database on the destination server.
- Close all windows and views except the Database window.
- Point to the Database Utilities command on the Tools menu and click Copy Database File.
Access displays the Open dialog box.
- Enter a master data file name. The default file name is "Copy <database name>.mdf" and the default location is the default database folder.
- Press OK.
If other users are connected to the current SQL database (whether or not they are using the Access project), Access prompts you to disconnect all active connects th the database.
Caution Be careful disconnecting other users from a database. Disconnecting a user unexpectedly could lead to data loss.
Access detaches the database with the skipchecks option set to 'skipchecks' and copies the database with the new name. Whether or not, the copy operation succeeds or fails (for example, because of a sharing violation), Access re-attaches the current SQL Server database file and reconnects the Access project to the database.
Transfer a SQL Server database
You can transfer a SQL Server Database version 7.0 or later database file (.mdf) connected to an Access project (.adp). For more information on transferring a database, see the SQL Server documentation.
Note To transfer a database, you must have Microsoft SQL Server version 7.0 or later installed on you local compute, and be a member of the sysadmin role on the destination server.
- Close all open windows except the database window.
- Point to the Database Utilities command on the Tools menu and click Transfer Database.
- Follow the instructions in the Transfer Database Wizard:
What SQL Server would you like to use for this database? Type the name of the server you would like to use.
Use Trusted Connection You can use a trusted connection, that is, SQL Server can integrate with the Windows 2000 or later operating system security to provide a single log on to the network and the database.
Login ID and Password If you don't use a trusted connection, type the logon ID and password of an account with CREATE DATABASE privileges on the server.
What do you want to name your new SQL Server database? Type the name of the new SQL Server database. Access revises the name if it conflicts with an existing database name and adds a numbered suffix (mydatabase 1, for example).
Include Data Clear Include Data if you want to just transfer data definitions and extended properties (In this case, database diagrams are not transferred).
Access displays the Transfer Status dialog box. If you cancel the transfer operation before it is complete, the copy of the database may be in an unknown state. Access prompts you to either keep the database or drop it.