Electrical Design Software | Elecdes Design Suite by Scada Systems Ltd

How to Backup / Export from SQL Server to Access

SQL Server databases are not easy for regular users to back up and restore and are not easy to send via email or online file sharing. SQL Server databases cannot be accessed from a laptop that is disconnected from your workplace network.

You can use this procedure inside Instrument Manager to export your SQL Server project to a new Access database to create a backup, or to send to a client or subcontractor.

NOTE: This procedure does not merge with existing contents in an Access database; it exports the data from the current project into a new empty Access database file.

Procedure

  1. From the File menu, select Export database to Access file.

  2. Confirm (or edit if desired) the file name for the exported / back-up Access database file then click Save.

    The project name followed by ".exported.mdb" is suggested as a default file name, e.g. for a project named "Project1" the suggested database filename is "Project1.exported.mdb".

  3. If you have chosen or enter the name of a database file that already exists, you will be asked to confirm that you want to overwrite the file.

    The previous file will be completely overwritten if you click Yes to overwrite.

    NOTE: Usually this means that you have used the wrong file name, unless you are intentionally overwriting a previous export from the same project.

  4. The contents of the original SQL Server database will then be copied into the new Access database. Wait for the process to complete.

  5. The procedure finishes with a message confirming the export and the name of the exported database file.

You can now store the exported Access database as a backup or send it to subcontractor.

Wide tables in SQL Server

If the SQL Server database contained tables with more than 255 columns then these tables will have been split to successfully export them to Access, which has a limit of 255 columns in a table.

Split tables have the original name with a suffix "__SplitX", where X is a consecutive number starting at 1 for the first split.

The complimentary procedure, Migrate from Access to SQL Server, will re-combine this type of split table in an exported Access database into a single table in SQL Server.

See also

Creating a Project and Database

Migrate from Access to SQL Server