Moving MSDE to MSSQL

As your ListManager MSDE database grows, or as you performance requirements increase, you may need to migrate the data in ListManager's MSDE database to Microsoft SQL server. These instructions show how to do so when installing MS SQL on the same machine currently used by ListManager. If you are moving ListManager, please do so as a separate step.

Note: Although ListManager may use Microsoft SQL, Lyris does not support the Microsoft SQL database itself. These instructions are provided for the convenience of those using ListManager with Microsoft SQL, and are not a substitute for Microsoft's documentation.

1. Shut down ListManager.

2. Open a command prompt.

3. Detach the ListManager database by entering the following at the command prompt:

osql -S localhost -E
sp_detach_db ListManager
go

4. Use the service manager to stop MSDE.

5. Copy the following files to another location:
 

C:\Program Files\Microsoft SQL Server\MSSQL\Data\ListManager_Data.MDF
C:\Program Files\Microsoft SQL Server\MSSQL\Data\ListManager_Log.LDF

C:\Program Files\ListManager\lmcfg.txt

The first two files are a backup of your ListManager database. The third file (lmcfg.txt) contains information ListManager uses to connect to the database. Be sure you have backup copies of these files in another location; if they are lost or corrupted, you will not be able to restore your ListManager database.

6. Uninstall ListManager and MSDE (Microsoft SQL Server Desktop Engine) using the Windows Add or Remove Programs utility (Start > Control Panel > Add or Remove Programs).

7. Install Microsoft SQL server (or at a minimum, the Client Network Tools) on the ListManager server.

On the SQL server, create an initial database to be used for installation. This database will be used for installation purposes only.

8. Download ListManager for MS SQL

9. When prompted by the installer to specify the type of installation, select "New Install".

10. Proceed with the installation as prompted, specifying the database you created.

11. Shut down ListManager if you started it after the installation.

12. Attach the ListManager database backup files using SQL Server Enterprise Manager:

1.   Right click on Databases.

2.   Click All Tasks.

3.   Click Attach Database

4.   Specify the path where you copied ListManager_Data.MDF (e.g., C:\Program Files\Microsoft SQL Server\MSSQL\Data\ListManager_Data.MDF)

5.   By default, the database will be attached as ListManager. Change the database name in Attach as: if desired.

6.   Specify the database owner. You may need to create an owner through the "Security" option in Enterprise Manager.

7.   Click OK to attach the database.

13. Copy the lmcfg.txt file created before deleting the original ListManager installation to C:/Program Files/ListManager, overwriting the copy of lmcfg.txt created by ListManager during installation.

Open the lmcfg.txt file using a plain-text editor such as Notepad and edit it to use the database name, owner and password you specified when you attached the ListManager database using SQL Server Enterprise Manager. Save.

14. Open a command prompt, and change to the ListManager directory.

15. If upgrading ListManager, run the following command to upgrade the database:

lm dbupgrade

NOTE Upgrading the database may take several hours, depending on the size of your data.

16. Start ListManager and the ListManager web server.

You're done!