SQLTeam.com | Weblogs | Forums

Moving the SQL database to a brand new machine and attaching multiple databases with ease w/ support for Law PreDiscovery

sql2012

#1

If you are moving the SQL database for Law PreDiscovery a LexisNexis program then there are a few things you can do to make this process a few steps shorter. These are as follows:
 SQL Named Instance needs to remain the same as the box you are transferring from
 The computer NAME needs to be the EXACT SAME as the box you are transferring from, each case stores its own .ini which has the machinename\SQLINSTANCENAME. If you change this you will need to find a way to edit each and every .ini
 When installing SQL change the default directory name for SQL, the default directory includes a . and this dot does cause some issues although they are able to be worked around this will make this much easier.
 The LAW_User account which is made for SQL to be authenticated by SQL needs more permissions than the guide says, using only the permissions suggested in the guide I was unable to access the databases, as soon as I gave the LAW_User all permissions there were no issues. Since this is an automated account being used by a program this should not cause any issues.
 Keep the paths to your Law PreDiscovery cases the same, if you had the path at S:\path\LawCases, make sure the directory is named the same. It is not difficult to overcome if you choose not to follow this.
You will need 3 things in order to complete this process.
 Copy of DATA folder from working sql
 Copy of caselist.mdb (located in Law PreDiscovery directory)
 Copy of law cases (As I keep these on a NAS, knowing the path is all that is needed)
First begin with the SQL Installation, LexisNexis has posted detailed setup instructions AT THIS LINK
Next install Law PreDiscovery which Lexis Nexis has a guide AT THIS LINK if needed
Now that everything is installed lets test to make sure that our SQL database and Law PreDiscovery are set up and working together correctly. Open Law, create a new case, select SQL server and if it works everything is set up correctly thus far.
The next step is to copy the MDF files from the current SQL box to the new SQL box. To do this open SQL server configuration manager and stop the SQL service. Go find the DATA folder which is in the SQL program files directory and copy it to a thumb drive.
Append an _ to the DATA folder name and copy this folder next to the DATA folder on the new SQL server. DELETE ALL OF THE LDF files from this DATA_ folder, the one copied from the old/current working sql machine
The method I found easiest was to highlight all the mdf files in the DATA_ folder copy, and paste these into the DATA folder, DO NOT OVERWRITE ANYTHING
If you would like to figure out exactly which mdf to delete/not delete so you can perform a clean copy without needing to copy and not overwrite any of these files then what you need to know is:
 Master, model, msdb and tempdb are system files which need to stay
 If I tried to keep these in DATA and delete just these 4 from the DATA_ prior to copy. The sql service would be unable to start every time
 I didn’t have enough patience to figure out exactly which mdf was causing my issues I just know that copying everything and not overwriting anything is the method that I used
Now that you have the DATA_ folder copied to the DATA folder you can open sql server configuration manager and start the SQL service, verify the SQL service successfully starts

Open SQL Server management studio and connect with admin or SA account
Now the GUI provides you the option to attach each database 1 at a time, maybe you don’t have many cases or maybe you have time to spare to do this. I only verified that this method works and did not even consider going through attaching hundreds of these.
To run a query you will see an option along the top middle of the SQL program for new query, using the query copy paste the following:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

After this is pasted you will hit the execute button which is near the new query button. This command enabled xp_cmdshell which is used for the next script to execute.
With this new script, you will only have to change the path to your data folder, this is the folder where your mdf data files are stored and will be permanently.
 When using this method the first time I had done this from a network path which was S:\path\data and had no issues, when I did this my second time months later I used default folder location for the DATA folder and kept getting an error which consisted of “command passed to left or subsequent function”. This was telling me that it couldn’t find the path where I specified the DATA folder to be. By relocating the DATA folder to the root of C: the local filesystem there were no issues.

declare @path varchar(500), @cmd varchar(1000)
set @path = 'C:\PATH\DATA'
set @cmd = 'dir ' + @path + '*.mdf /B'
create table #t (output varchar(500))
insert into #t
exec master.dbo.xp_cmdshell @cmd
select 'exec sp_attach_single_file_db @dbname = ''' + left(output, charindex('.mdf', output)-1) + ''', @physname = ''' + @path + output + ''''
from #t
where output is not null
drop table #t

Once this command has been executed you will notice that it produced multiple entries beginning with exec, highlight all of these and paste these into a new query and execute.
In my case this will give me an error with s, when investigating the line specified I notice there is an apostrophe in the path which the name is Noah’s Ark. By correcting this to Noah’’s Ark I am able to bypass this issue.
After executing this query successfully it will take a bit of time to attach each database, this code cannot handle ldf files, if log files are to be transferred as well, functionality must be added. Therefore as you are watching the attach process happen in the messages tab it will notify you that it is unable to locate any ldf files and that it is creating an ldf file per mdf.

Now that SQL is installed and properly configured it is time to put the final touches on Law PreDiscovery so that all the attached mdf files are accessible by the law cases.
The caselist.mdb needs to be copied from the working Law PreDiscovery station to the new Law PreDiscovery station. We use a Server machine which contains the SQL as well as Law PreDiscovery. The computers in our network have law installed on them and are then given network paths to run law from the server.
The caselist.mdb can be accessed through Microsoft Access and when opened you will notice it lists the Case/Database name and the path to the law case, if you changed the directory for your law cases this will need to be updated accordingly or you can keep the new caselist.mdb and for each case that is needed in law when opening cases you can search to the case directory and open the .in for each case.
Once complete, upon opening Law PreDiscovery, you will see all of your cases in the list and be able to open and use these as if you were on the original production box.
Congratulations!