Advanced lightswitch debugging (part 1 of N): getting production data in your localDB
There are occasions where you run in trouble in production which can not be reproduced in the visual studio debug environment. Often the root cause is difficult to identify: it can be due to an IIS issue, different behavior of the production database vis-à-vis the localdb, etc. …
What if I suspect my problem is caused by the production data itself?
We’ll start with an easy one. Imagine you run in trouble with an application in production and your intuition is that the problem is caused by the data in production itself and not by potential different behavior of the database system. This last cause will be covered in a consecutive article.
In that scenario, the most straightforward step is to try to get the production data into your localdb. I’ll try to describe the necessary steps to do this.
You will need sql server 2012 locally !
How to copy production data to the localdb
Obviously, we presume here that you have access to this data via sql management studio.
Step 1: connect via sql management studio to the production database
Right click on the database, select Tasks and select Generate Scripts:
Step 2: Select all tables
A wizard will start and start wth skipping the first introduction page. On the Choose object page select “Tables”:
On the next page of the wizard update if you want the file path and click the “advanced” button.
Step 3: Make sure to export only the data and not the schema
Step 4: Finish the wizard
Step 5: locate the file path of the localdb database
Goal is to find the file path of the our localdb this time. The database name is ApplicationDatabase.mdf and the path is relative to your solution folder .\bin\data.
In my case this path is D:\temp\CreateScriptTest\CreateScriptTest\Bin\Data\ApplicationDatabase.mdf
You can also find the database path (even even manipulate the data in the localdb) by using sql management studio 2012.
Click the Connect button:
The magic is now knowing the right connection string for the localdb engine: (localdb)\v11.0. Note the backslash character !
Step 6: Update the script file
You will need to update now the first line in the script you created in step 4 with the localdb database path:
Step 7: run SqlCmd
First of all, in case you have multipe versions of sql server on your workstation, make sure to the sqlcmd which goes with the 2012 version of Sql Server. On my machine it’s here:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>
run SqlCmd/? and learn that you following options and that you are using Version 11 :
Microsoft (R) SQL Server Command Line Tool
usage: Sqlcmd [-U login id] [-P password]
If you are wondering how I copied this console output here, I did it as follows:
Check first if you can connect to the localdb egine:
|C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqlcmd -S (localdb)\v11.0|
If you don’t get an error, everything is ok.
We are ready now to run the command for executing the script:
sqlcmd -S (localdb)\v11.0 -
Pretty simple indeed. The – I argument refers to the location of the script and we dump also all output via the – o argument to a specific file.
This is a very first technique which can be helpful to isolate potentials production problems with a LightSwitch application.