Created On:  10 September 2012

Problem:

User wishes to move a SQL Express based workspace in Enterprise Analyzer from one machine to another.

Resolution:

Note that in order to be able to move one MS SQL Server database from one machine to another you have to have the same version of MS SQL Server running on both machines.
Steps:
Install the SQL Server Management Studio

1.       If the Microsoft SQL Server Management Studio is not already installed, you will need to download it from http://www.microsoft.com/en-us/download/details.aspx?id=26729 .

2.       Install Microsoft SQL Server 2008 Management Studio Express (for more details refer to http://blogs.msdn.com/b/bethmassi/archive/2011/02/18/step-by-step-installing-sql-server-management-studio-2008-express-after-visual-studio-2010.aspx):

a.       Right-click the installer and choose “Run as Administrator”.

b.      Select Installation.

c.       Select “New SQL Server stand-alone installation or add features to an existing installation”.

d.      Click OK on the Setup Support Rules step.

e.      Click Install in the Setup Support Files window.

f.        Resolve any failed rules and click Next.  (I had to install Windows PowerShell on a Windows XP machine http://www.microsoft.com/en-us/download/details.aspx?id=7217).

g.       Select “Perform a new installation of SQL Server 2008” and click Next.

h.      Click Next on the Product Key step.

i.         Accept the license terms and click Next.

j.        Check “Management Tools – Basic” and click Next.

k.       Click Next through the remaining steps then click Install.

l.         Click Next after the installation finishes.

m.    Close the installer.  The SQL Server Management Studio will now be available in the Start Menu under Programs.

Detach and copy the database and workspace

3.       Open MS SQL Management Studio.

4.       Connect to the database server where your database is located.

5.       Expand the Database node.

6.       Select the database you want to move.

7.       Right Click on the database.

8.       Tasks -> Detach -> OK.

9.       Wait until the operation is complete.

10.   Go to C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA (or similar folder depending on your installation).

11.   Find .mdf and .ldf files corresponding to the name of the database you detached on step 6.

12.   Copy those files to a temp folder.

13.   Copy the workspace files (.rwp and the corresponding folder) to the temp folder.

14.   If there are any custom extensions required for the workspace, copy the plugin.xml file from the \Plugins folder and other required files to the temp folder.

15.   Archive the files by adding them to a zip file to make the copy faster.

16.   Copy the temp folder archive to the machine where you want to move your workspace to and extract the files.

IMPORTANT:  Before copying the workspace files and .rwp file into the destination folder, be sure to share the folder so it is accessible to other clients.

Attach the database on the destination machine

17.   Open MS SQL Management Studio on the destination machine.

18.   Connect to the database server where your database will be located.

19.   Select the Databases node.

20.   Right Click -> Attach.

21.   Click on the Add button.

22.   Select the .mdf file from the temp folder -> OK.

23.   Click OK, and wait until the operation is complete.

24.   Repeat steps 17 through 23 on the original machine to be able to connect to the workspace again.  When selecting the .mdf file, point to the original file in the ...\MSSQL\DATA directory.

Make changes so the workspace can be opened on the destination machine

25.   Open the .rwp file in a text editor and change the property name="Server" value to the MSSQL server name of the destination machine.

26.   Update the WorkspaceDir value in the database with the following command:

 

UPDATE WorkspaceSettings SET Value = ‘\\COMPUTER-NAME\workspaces\workspace1’ WHERE Name = ‘WorkspaceDir’

Where COMPUTER-NAME is the name of the destination computer, workspaces is the name of the shared folder, and workspace1 is the name of the workspace.  The new value entered should be the shared location including the workspace folder name.  For example, if the workspace named workspace1 and is on a computer called COMPUTER1 in a shared folder called workspaces, the Workspace directory network path would be \\COMPUTER1\workspaces\workspace1

27.   OPTIONAL:  If the user that will open the workspace on the destination machine is different from the source machine and needs to be a master user, insert the user of the destination machine as a master user in the syUserRole table:

INSERT INTO syUserRole (UserID, Role)
VALUES ('[user name]', 'Master')

28.   Make sure that the configuration and EA version and HotFix level on the destination machine matches the configuration and version on the original machine so no information is lost when opening the workspace.

29.   Open the .rwp on the destination machine.