Before you upsize a Microsoft Access database

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Before you upsize your Microsoft Access database to a Microsoft SQL Server database or Microsoft Access project, consider doing the following:

Backup your database Although the Upsizing Wizard doesn't remove any data or database objects from your Access database, it's a good idea to create a backup copy of your Access database before you upsize it.

Ensure you have adequate disk space   You must have adequate disk space on the device that will contain the upsized SQL Server database. The Upsizing Wizard works best when there is plenty of disk space available.

If you have ample disk space on your server, multiply the size of your Access database by two and reserve that space on your disk. This ensures that the Upsizing Wizard has enough space to upsize your database and also leave it some room to grow. If you expect to add a lot of data to the database, make the multiple larger.

Caution If the Upsizing Wizard runs out of disk space on the server, the wizard halts, leaving a partial database and any devices it created on the server. If this happens, you can drop (delete) devices, databases, and tables using Microsoft SQL Server Enterprise Manager.

If desired, create multiple disk devices If you are upsizing to an SQL Server 6.5 database, you may want to create devices before running the Upsizing Wizard. The Upsizing Wizard creates all new devices on the same physical disk where the Master database device resides. If your server has multiple disks, you can place your database on one disk and the transaction log on a different disk. In the event of a disk failure, you can recover your database. Make sure that you have devices that are big enough on both disks. Create new devices if necessary.

SQL Server also allows databases and transaction logs to span several devices. However, the Upsizing Wizard only allows you to specify one device for your database and one device for the transaction log. To specify multiple devices for a database or transaction log, make those devices (and no other devices) the default devices. Then run the Upsizing Wizard, and select the default database or transaction log device.

Note If the size of the new SQL Server database or the size of the transaction log doesn't require using all the devices set as default, SQL Server uses only the devices necessary to accommodate the database or transaction log.

Set a default printer   You must set a default printer to use the Upsizing Wizard because it automatically creates a report snapshot of the Upsizing Wizard report.

Assign yourself appropriate permissions on the Access database You need read/design permission on all database objects to upsize them.

Assign yourself appropriate permissions on the SQL Server database

  • To upsize to an existing database, you need CREATE TABLE and CREATE DEFAULT permissions.

  • To build a new database, you need CREATE DATABASE permission, and SELECT permissions on the system tables in the Master database.

  • To create new devices, you must be a system administrator.

Adhere to SQL Server 6.5 naming rules from the beginning If you upsize to an SQL Server 6.5 database, the Upsizing Wizard converts illegal names for you by converting spaces and illegal characters to underscores (_). However, it's a good idea to adhere to SQL Server 6.5 naming rules from the beginning, if possible. SQL Server 6.5 names must be 30 characters or less. The first character must be a letter or the at sign (@). The remaining characters may be numbers, letters, the dollar sign ($), the number sign (#), or the underscore (_). No spaces are allowed.

Note SQL Server 7.0 database names are fully compatible with Access database names.