Advanced lightswitch debugging (part 1 of N): getting production data in your localDB

Introduction

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:

 

createSqlScripts

Step 2: Select all tables

A wizard will start and start wth skipping the first introduction page. On the Choose object page select “Tables”:

GenerateScriptsStep2

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

GenerateScriptsStep4

Step 4: Finish the wizard

GenerateScriptsStep5

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:

connect

The magic is now knowing the right connection string for the localdb engine: (localdb)\v11.0. Note the backslash character !

connect2

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:

USE [D:\TEMP\CREATESCRIPTTEST\CREATESCRIPTTEST\BIN\DATA\APPLICATIONDATABASE.MDF]

 

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
Version 11.0.2100.60 NT x64
Copyright (c) 2012 Microsoft. All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
   [-S server]            [-H hostname]          [-E trusted connection]
   [-N Encrypt Connection][-C Trust Server Certificate]
   [-d use database name] [-l login timeout]     [-t query timeout]
   [-h headers]           [-s colseparator]      [-w screen width]
   [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
   [-c cmdend]            [-L[c] list servers[clean output]]
   [-q “cmdline query”]   [-Q “cmdline query” and exit]
   [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
   [-u unicode output]    [-r[0|1] msgs to stderr]
   [-i inputfile]         [-o outputfile]        [-z new password]
   [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
   [-k[1|2] remove[replace] control characters]
   [-y variable length type display width]
   [-Y fixed length type display width]
   [-p[1] print statistics[colon format]]
   [-R use client regional setting]
   [-K application intent]
   [-M multisubnet failover]
   [-b On error batch abort]
   [-v var = “value”…]  [-A dedicated admin connection]
   [-X[1] disable commands, startup script, environment variables [and exit]]
   [-x disable variable substitution]
   [-? show syntax summary]

If you are wondering how I copied this console output here, I did it as follows:

sqlcmd/? |clip

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 –
i “C:\Users\paul\Documents\script.sql” -o d:\temp\scriptoutput.txt

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.

Conclusion

This is a very first technique which can be helpful to isolate potentials production problems with a LightSwitch application.