Move SQL workspace across machines in Enterprise Analyzer
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.
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.
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.
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’
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:
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.