Symptoms
When you run the adTempus SQL Server conversion/migration tool (adtsqldb.exe), you are given the option to create the adTempus database only, or to create it and import existing data. If you are using a remote SQL Server instance (SQL Server is on a different computer from adTempus and atsqldb), the migration will fail if you choose not to import existing data, or choose to import from a SQL Server backup file.
In other words, the migration will succeed only if you choose to import existing data, and then select a Microsoft Access database as the import source.
If you choose not to import, or select a SQL Server backup file as the import source, the tool will fail with an error similar to the following:
Failed to extract data from SQL Server data file: Failed to read backup headers from file "C:\Program Files\Arcana Development\adTempus\sqlserver\adtempusinitialdata.dat": RESTORE HEADERONLY is terminating abnormally.
Cannot open backup device 'C:\Program Files\Arcana Development\adTempus\sqlserver\adtempusinitialdata.dat'. Device error or device off-line. See the SQL Server error log for more details.
Database creation succeeded, data copy (or initialization) failed. The uninitialized database has been dropped. Address the errors listed above and rerun the conversion process.
Cause
This occurs because adtsqldb does not perform the database initialization or import itself: it passes the file name to SQL Server, which does the work. If SQL Server is on a different computer, it will not find the file specified by adtsqldb, because that file is on a different computer.
When you tell adtsqldb not to import data, it still must initialize the new database by using a data file that is located on the computer with adtsqldb.
Workaround
If you were trying to create the database without importing data
If you are performing a new installation of adTempus, the Microsoft Access database will not contain any data (other than the initial data required by adTempus). You can therefore choose the "import" option, and let adTempus import the mostly empty Access database. This will produce the same result as choosing not to import.
If you have been running adTempus and therefore have data in your Access database, but want to initialize SQL Server with an empty database, you will need to "trick" the conversion process by putting a copy of the data file on the computer where SQL Server is located:
- Locate the data file, which is named in the error message from adtsqldb. Generally it will be "C:\Program Files\Arcana Development\adTempus\sqlserver\adtempusinitialdata.dat"
- Create an identical directory structure on the computer where SQL Server is running. The drive letter and directory names must be the same.
- Copy the adtempusinitialdata.dat file into the mirrored directory on the SQL Server computer.
- Rerun the conversion process. The migration should now succeed, because SQL Server will find the data file it is looking for.
- After the conversion succeeds, you can delete the data file from the SQL Server computer.
If you were trying to initialize the database from a SQL Server backup file
You must specify the backup file name relative to the computer where SQL Server is running. Therefore you cannot use the browse button in adtsqldb to browse for the file. Instead, manually enter the path to the file, as it would appear from the computer where SQL Server is running.
