Manually create a Database on a new SQL server

Users occasionally need to create the Express Maintenance database on a different SQL Server.  Moving an SQL database is not difficult but it is not a matter of simply moving files.  Because the SQL server has the database files locked and in use, you must take the following steps.

1. Locate the current files that make up the database.  The files are likely ExpressMaintenance_Data.mdf and ExpressMaintenance_Log.ldf.  With the MSDE2000 or SQL2000, the files are likely in the C:\\Program Files\\MSSQL\\Data folder.  With the older MSDE7 they will likely be in the C:\\MSSQL7\\Data folder.  You can determine the location of the data and log files in Express Maintenance under Utilities / View Connection Properties and note the Data File location.  These files will exist in the ExpressTech folder but these are not the database, they are the original model files used to create the database.
2. Stop the SQL Server.  This is done in Start / Programs / Startup / Configuration Manager for SQL 7 and 2000.  For SQL 2005 and 2008, this is done under Start / Programs / Microsoft SQL Server / Configuration Manager.
3. Once the SQL Server is stopped, locate the data and log files in the folder noted in step 1 above.  Copy the files and paste  the files to the desired folder on the destination computer which is running SQL Server.
4. Make sure the SQL Server on the destination computer has been properly setup with Mixed Mode Authentication.
5. On the destination, download install and run Express SQL to perform the remaining steps.  For information on downloading, installing and setting up Express SQL, contact us.
6. In Express SQL, connect to your SQL Server by registering the server name and login info.
7. Left click on the SQL Server in upper left corner.
8. Right click on the SQL Server in upper left corner and select the Create New Database option.
9. When the Create New Database option appears, select the 3rd option “Both Files”.
10. Enter the name of the new database.  We recommend using “ExpressMaintenance”.
11. Select the Express Maintenance data and log files from the folder where they were pasted in step 3 above.
12. Click on the Proceed button to have the database created.
13. Once the database is created, you will see it in the list of databases under the Server in upper left corner.
14. Left click on the new Express Maintenance database in upper left corner.
15. In lower left you will see the Users list.  Right click on in the Users list and left click on the Add Special ‘etuser’ Account option.  This option will create the ‘etuser’ login account and give it full permission to the database.  This is the account used by Express Maintenance to connect to the SQL database.

This will complete the steps for manually creating the Express Maintenance database on an SQL Server.