LavaBlast Software Blog

SQL Server - Restore a database backup via the command line

EtienneT — Tue, 14 Oct 2008 12:45:00 -0400

Anyone who's ever developed a web application in .NET has had to play with a database management system, most probably SQL Server or its free cousin, SQL Server Express. One of the tasks I personally hate doing with our SQL Server Express 2005 databases is restoring them from a backup, using SQL Management Studio. We sometimes restore the point of sale database used by our customers to track down various issues or to build reports using their data as our test set. The process is not that long when you restore a backup from your own machine (restoring the MDF and LDF files to their original directory). If you restore databases from foreign systems, the process is simple only if both systems stored their databases in the same directory, which is rarely the case.

For example, I use Windows Vista x64 and our dedicated server uses a 32-bit version of Windows 2003. Our data is stored in the default SQL Server directory, which is in the Program Files folder. However, when using a 64-bit operating system, the program files directory is different (C:\Program Files (x86)). Since the location of the MDF and LDF files are encoded directly in the bak file generated by SQL Server, restoring them via the command line is especially challenging when you don't control the original locations of the MDF and LDF files, nor their Logical Names.

Our goal is to be able to restore a database by executing a simple command such as this:

restore.bat LavaBlast

This command would look for LavaBlast.bak in the current directory and would restore the LavaBlast database to a default location on your computer where you want to store your MDF and LDF files.

Here is the code for restore.bat:

sqlcmd -S .\SQLEXPRESS -i attachDB.sql -v database="%1" -v root="%CD%"

We are simply calling sqlcmd (added to our path) to connect to our local instance of SQL Server Express and we are executing an SQL file (attachDB.sql) which includes two variables: database and root (the current path).

Here is the code for attachDB.sql:

USE MASTER

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[$(database)]') AND type in (N'U'))

  ALTER DATABASE $(database) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

create table #backupInformation (LogicalName varchar(100),

PhysicalName varchar(100),

Type varchar(1),

FileGroupName varchar(50) ,

Size bigint ,

MaxSize bigint,

FileId int,

CreateLSN int,

DropLSN int,

UniqueId uniqueidentifier,

ReadOnlyLSN int,

ReadWriteLSN int,

BackupSizeInBytes int,

SourceBlockSize int,

FileGroupId int,

LogGroupGUID uniqueidentifier,

DifferentialBaseLSN bigint,

DifferentialBaseGUID uniqueidentifier,

IsReadOnly bit, IsPresent bit )

insert into #backupInformation exec('restore filelistonly from disk = ''$(root)\$(database).bak''')

DECLARE @logicalNameD varchar(255);

DECLARE @logicalNameL varchar(255);

select top 1 @logicalNameD = LogicalName from #backupInformation where Type = 'D';

select top 1 @logicalNameL = LogicalName from #backupInformation where Type = 'L';

DROP TABLE #backupInformation

RESTORE DATABASE $(database)

FROM DISK = '$(root)\$(database).bak'

WITH REPLACE,

MOVE @logicalNameD TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\$(database).mdf',

MOVE @logicalNameL TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\$(database).ldf'

GO

Simply put, we are extracting the logical names (and other metadata) from the .bak file into a temporary table. We then use those values to restore the MDF and LDF to the correct location, instead of the ones specified in the .bak file.

If you want to use this script, simply ensure you change the location of your SQL Server data files (the last lines in the SQL file) and you should be good to go. Please note that in its current form, the script only supports files with one MDF and one LDF file in the database backup. Furthermore, it assumes your .bak file has the same name as the database you want to import. We could also enhance the script by automatically adding permissions to the ASP.NET user after restoring the database. Feel free to post any enhancements you make in this post's comments and I hope you'll find this script useful! Enjoy.

Jason Kealey and Etienne Tremblay featured in National Capital Scan

jkealey — Thu, 11 Sep 2008 22:39:00 -0400

Two of LavaBlast's founders (Etienne and myself) are featured in this month's National Capital Scan, an independent news leader for Ottawa's Tech Community. As we are proud to be University of Ottawa alumni, it is always a pleasure to keep in touch with the university and new software engineering students. In addition to this article about our launching our software company, I will be sharing lessons learned at the Students As Innovators workshop later this month at the School of Information Technology and Engineering.

Additionally, we're actively helping other startups in the Ottawa and Montreal regions, many of which we met at The Code Factory, the Ottawa-based co-working location. Ian Graham is busy organizing tons of events which we strongly recommend you attend if you're interested in getting known in the Ottawa software community.

LavaBlast Selected as a Red Herring Canada Top 50 Finalist

jkealey — Fri, 29 Aug 2008 14:02:52 -0400

We've been busy these lasts few weeks on a number of projects, but I thought I'd like you know that we were selected as an innovative Canadian software start-up.

Red Herring Magazine has named LavaBlast Software a finalist for the "Red Herring Top 50 Canada" award, a prestigious list honoring this year’s most promising private technology ventures in Canada. The Red Herring editorial team used an intensely competitive process to select the most innovative companies from a pool of over 300, leaving 100 finalists vying for this prestigious award. The names of all 100 companies short–listed as finalists for the "Red Herring Top 50 Canada 2008" can be found online at: http://www.herringevents.com/canada08/Canada50.html

BlogEngine.net Post Security

EtienneT — Wed, 13 Aug 2008 15:23:00 -0400

BlogEngine.net 1.3/1.4 supports user roles. But we can't seem to be able to make it mandatory for users to sign in to see blog posts. That's not something you usually want on a public blog, but for a corporate blog, maybe you want to make sure your news only gets to the people you want. This seemed like a perfect candidate for a BlogEngine extension.

User Filtering

In our scenario, we don’t want any unregistered users to be able to see blog posts. This can be easily checked by calling Membership.GetUser() and ensuring the returned value is not null. We could filter out specific users as well, but we didn’t implement this feature in our extension.

Post Filtering

It could be interesting to restrict who can see the posts in a specific blog category. For example, a blog category “Top Secret” which can only be read by your company's upper management… Not very likely in a blog, but you get the point. Our extension does this filtering by associating a blog Category with a membership Role in the extension’s settings.

By associating a membership role with a blog category name, the extension ensures the user has this role before displaying a post associated with this blog category name. If you add two roles for the same category, posts with this category will only be served if the user has both roles.

Adding a setting with an empty category name will ensure that all posts require a particular role.

Code

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using BlogEngine.Core;

using BlogEngine.Core.Web.Controls;

using System.Collections.Generic;

/// <summary>

/// Summary description for PostSecurity

/// </summary>

[Extension("Checks to see if a user can see this blog post.",

            "1.0", "<a href=\"http://www.lavablast.com\">LavaBlast.com</a>")]

public class PostSecurity

    static protected ExtensionSettings settings = null;

    public PostSecurity()

        Post.Serving += new EventHandler<ServingEventArgs>(Post_Serving);

        ExtensionSettings s = new ExtensionSettings("PostSecurity");

        s.AddParameter("Role", "Role", 50, true);

        s.AddParameter("Category", "Category", 50);

        // describe specific rules for entering parameters

        s.Help = "Checks to see if the user has any of those roles before displaying the post. ";

        s.Help += "You can associate a role with a specific category. ";

        s.Help += "All posts having this category will require that the user have the role. ";

        s.Help += "A parameter with only a role without a category will enable to filter all posts to this role. ";

        s.AddValues(new string[] { "Registered", "" });

        ExtensionManager.ImportSettings(s);

        settings = ExtensionManager.GetSettings("PostSecurity");

    protected void Post_Serving(object sender, ServingEventArgs e)

        Post post = (Post)sender;

        bool continu = false;

        MembershipUser user = Membership.GetUser();

        continu = user != null;

        if (user != null)

            List<string> categories = new List<string>();

            foreach (Category cat in post.Categories)

                categories.Add(cat.Title);

            string[] r = Roles.GetRolesForUser();

            List<string> roles = new List<string>(r);

            DataTable table = settings.GetDataTable();

            foreach (DataRow row in table.Rows)

                if (string.IsNullOrEmpty((string)row["Category"]))

                    continu &= roles.Contains((string)row["Role"]);

                else

                    if (categories.Contains((string)row["Category"]))

                        continu &= roles.Contains((string)row["Role"]);

        e.Cancel = !continu;

Simply saving this code in a .cs and putting it in your App_Code/Extensions for BlogEngine.net shall enable the plugin.

SubSonic v2.1 Controller and Utilities

JKealey — Mon, 04 Aug 2008 11:45:00 -0400

We've done a few posts about how we use SubSonic here at LavaBlast. Recently, SubSonic v2.1 was released and we upgraded the code we've previously published to support this new version. We've blogged about our changes in the past and not much has changed since, but we did get a request to post our source code, so here it is. I've actually included a bit more code in this release so that this blog post has a bit more substance!

Download the source code.

The file contains our SubSonicController, SubSonicHelper, and our associated code generation templates. Nothing new to see here, except that you get downloadable code. We unfortunately did not have time to play with the new Query engine all that much, so our controller still uses the old one (which is used throughout our codebase). If anyone would like to augment our code to support the new query engine and post it in the comments, that would be great! Moving to the new query engine would circumvent the OR query limitation related to the search fields we've mentioned in the past.

Auditing using SubSonic

We like to log certain things in our Electronic Journal as it gives us ways to debug more efficiently, and provides us with a way to keep track of who changed what in case something breaks. We've included an SQL script that generates our ElectronicJournal table, and code which allows us to save events in the table. We've wired it up to our SubSonicController so that we can log all object updates, for example. What you log is your own business and it depends on your needs and performance requirements.

We've built an administrative interface over this table allowing us to navigate efficiently through the events. (Each of our pages in FranchiseBlast extends from generic controls which list/filter/page rows using our ObjectDataSource, effectively re-using the code we're presenting here.)

Various notes

Remember not to mix AND and OR in the current version of this code, with the old query engine.
Don't log everything on high volume sites, for obvious reasons.
Issue 3 is still open and waiting to be committed. The others bugs I previously reported (and a new one) have been committed.
We removed the ToList() which we added last time, because GetList() is already present. (Thanks to our readers for noticing!)
We replaced all calls to IsLoaded() to !IsNew() in our codebase. Click here to learn why.

The Code Factory in the News

JKealey — Thu, 24 Jul 2008 08:41:00 -0400

We're happy to see that our client The Code Factory, an Ottawa-based software co-working franchise, was recently featured on the local news. Some of us were on site while the spot was filmed and it was an interesting (yet stressful) experience to be filmed when you're trying to draft out the features to include in the next release!

Twitter + Co-working

This coincides with our recent addition of a cool little feature to The Code Factory's interactive kiosk. We're now publishing events to Twitter when people check-in and check-out of the location. Of course, members can choose to hide their activities for privacy reasons, but this serves as an interesting off-site complement to the in-store kiosk which indicates who's currently on location. If you follow TheCodeFactory on Twitter, you can see when your friends arrive and decide to head out there yourself!

jQuery Content Slider Tutorial

EtienneT — Tue, 22 Jul 2008 15:22:00 -0400

Simple Demo (Firefox, IE7, IE6, Opera, Safari) | Demo with content | Source Code

LavaBlast launched a new front page recently and we have incorporated a new jQuery slider. You can see it in action below (in the Flash animation) or you can go see it directly on our website home page.

Scroll down to find out how this little puppy was implemented!

HTML & CSS

HTML

This is the basic ASP.NET in the ASPX page:

<div class="FrontMenu">

    <div class="Bar">

        <asp:Repeater ID="Repeater" runat="server" onitemdatabound="Repeater_ItemDataBound">

            <ItemTemplate>

                <span class="item">

                    <a href="#" class="jFlowControl">

                        <asp:Label ID="lbl" runat="server" />

                    </a>

                    <div class="spike" style="z-index:999999">

                    </div>

                    <div class="right">

                    </div>

                </span>

            </ItemTemplate>

        </asp:Repeater>

    </div>

    <div class="Panel">

        <div><cms:Paragraph ID="paragraph1" runat="server" ContentName="frontMenu1" /></div>

        <div><cms:Paragraph ID="paragraph2" runat="server" ContentName="frontMenu2" /></div>

        <div><cms:Paragraph ID="paragraph3" runat="server" ContentName="frontMenu3" /></div>

        <div><cms:Paragraph ID="paragraph4" runat="server" ContentName="frontMenu4" /></div>

        <div><cms:Paragraph ID="paragraph6" runat="server" ContentName="frontMenu5" /></div>

    </div>

</div>

This is pretty simple HTML. Everything is enclosed in the main div with the css class FrontMenu. We have the animated bar at the top and the content panel underneath it. The menu bar is generated by a simple repeater control bound to data in our ASP.NET page. Each menu item is a span containing a link that we’ll use to change the selected menu item. The Panel div contains multiple dynamic paragraphs from our content management system (SubSonic CMS). You could easily change this code to bind to data from another source.

Here is the code behind for this control. We kept it pretty simple:

public partial class FrontPageMenu : UserControl

    protected void Page_Load(object sender, EventArgs e)

        if (!IsPostBack)

            List<string> list = new List<string>();

            list.Add("How can we help?");

            list.Add("Our Products");

            list.Add("Hot Features");

            list.Add("Testimonials");

            list.Add("Read");

            Repeater.DataSource = list;

            Repeater.DataBind();

    protected void Repeater_ItemDataBound(object sender, RepeaterItemEventArgs e)

        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)

            Label lbl = e.Item.FindControl("lbl") as Label;

            lbl.Text = (string)e.Item.DataItem;

The following picture shows how the html menu item elements are rendered. The CSS section of this article will dive deeper in the inner workings of the menu.

JQuery

jFlow

We used jFlow to scroll our panels when we click on a menu item. The code was fairly straightforward to use. We included this script in our user control.

$(".FrontMenu").jFlow({

    slides: ".FrontMenu .Panel",

    width: "974px",

    height: "344px",

    duration: 300

});

And then we included a simple reference to our script manager.

<asp:ScriptManagerProxy ID="proxy" runat="server">

    <Scripts>

        <asp:ScriptReference Path="~/js/jquery.flow.1.0.min.js" />

    </Scripts>

</asp:ScriptManagerProxy>

We could have used Next and Previous buttons but decided not to.

IE6 Problems

This menu did require some basic jQuery. Except for the scrolling animation, we could have built almost all of this in pure CSS thanks to the hover pseudo-class. However, Internet Explorer 6 came back from the grave to haunt us… As we still have some visitors who are using IE6, we could not afford to let our home page break down in IE6. Microsoft gods: please please, hear our prayers and find a way to erase IE6 from the face of earth. But hey, we've got work to do and can't wait five years for the browser to die like IE 5 did.

We discovered that using a:hover in IE6 to change our background image will make the browser crash. We used IETester to test the menu in IE6: it made IETester crash. We then tried Virtual PC running Win98 and IE6: Internet Explorer crashed again when we hovered over one of the links with a:hover CSS styles.

The solution to this problem was simply to apply a style to the hovered link (.hover). Then we could easily style the children of this element to our liking without breaking IE6.

$(".FrontMenu .Bar a").mouseover(function() { $(this).addClass("hover"); });

$(".FrontMenu .Bar a").mouseout(function() { $(this).removeClass("hover"); });

To change the style of the selected item we added the css class .sel to the span.item (the parent of the clicked link). First of all, when a link is clicked, remove the currently selected item. Second, set the parent of the link as the current selected item. It’s important to return false as otherwise the browser will follow the link and scroll to the top of the page.

$("div.FrontMenu div.Bar a").click(function()

    $("div.FrontMenu div.Bar").children("span.sel").removeClass("sel");

    $(this).parent().addClass("sel");

    return false;

});

CSS

Let’s take a deeper look at the menu’s CSS. Let’s start with the FirstMenu class which is not too complicated.

.FrontMenu

    padding: 5px 5px 0px 5px;

    margin-left: 2px;

    margin-top: -4px;

.FrontMenu .Bar

    background: #F6ECA4 url(images/MainPage/bar.jpg) no-repeat top left;

    width: 980px;

    height: 48px;

    position: relative;

.FrontMenu .Bar a

    color: #FFFFFF;

    font-size: large;

    font-family: Tahoma;

    position: relative;

    top: 7px;

    display: block;

    text-decoration: none;

    padding-right: 6px;

    margin-right: -6px;

    cursor: pointer;

Now let’s take a closer look at the Bar menu. Here are the images we used to style our menu items.

CSS

Images

.FrontMenu .Bar span.sel

selLeft.gif

selRight.gif

spike.gif

.FrontMenu .Bar a.hover span

hoverLeft.gif

hoverRight.gif

Like you saw in the jQuery part, we change the class in JavaScript to bypass some IE6 issues, so you should not be surprised by the CSS.

The code for the main span for each menu item:

.FrontMenu .Bar span.item

    line-height: 30px;

    margin: 0px 0px;

    float: left;

    position: relative;

    display: inline;

    cursor: pointer;

    width: 188px;

    text-align: center;

    margin-left: 6px;

Here is the code when you hover over the link inside the menu item:

/* We have to handle hover with jQuery because :hover makes IE6 crash when we change the background image. */

.FrontMenu .Bar a.hover

    background: transparent url(images/MainPage/hoverRight.gif) no-repeat top right;

    height: 30px;

/* We have to handle hover with jQuery because :hover makes IE6 crash when we change the background image. */

.FrontMenu .Bar a.hover span

    background: transparent url(images/MainPage/hoverLeft.gif) no-repeat top left;

    height: 30px;

    display: block;

Like you can see, the link contains the background image and the span inside the link contains the . This enables the link to have any length and the control will resize easily. If you ever get a link that is wider than the left image, simply make the image wider...

Then we only needed the CSS to change the menu item to make it look selected.

.FrontMenu .Bar span.sel a:hover { background: none; padding-left: 0px; margin-left: 0px; }

.FrontMenu .Bar span.sel a:hover span { background: none; padding-left: 0px; margin-left: 0px; }

.FrontMenu .Bar span.sel

    background: transparent url(images/MainPage/selLeft.gif) no-repeat top left;

    height: 48px;

.FrontMenu .Bar span.item .spike, .FrontMenu .Bar span.sel .spike

    background: transparent url(images/MainPage/spike.gif) no-repeat top left;

    display:none;

    position: absolute;

    top: 44px;

    left: 50%;

    margin-left: -11px;

    width: 22px;

    height: 17px;

    z-index: 9999;

.FrontMenu .Bar span.sel .spike

    display: block;

.FrontMenu .Bar span.sel .right

    background: transparent url(images/MainPage/selRight.gif) no-repeat top right;

    position: absolute;

    height: 48px;

    width: 4px;

    right: 0px;

    top: 0px;

.FrontMenu .Bar span.sel a { color: #d43300; }

The parent span for the menu item with the .sel class contains the image and the div.right inside this span contains the image. We have to make sure too that the hover style does not get applied when the item is selected.

The spike is applied in absolute position in the center. To do that in absolute position, you have to set the following:

left: 50%;

margin-left: -11px;  // <—This is the width/2

width: 22px;

Even with a higher z-index, we were not able to make the spike go on top of the content panel in IE6. Therefore, we had to put a margin on top of content panel just to make sure the spike was not overlapping the content panel below:

.FrontMenu div.Panel

    height: 328px;

    width: 974px;

    margin-top: 15px;

Simple Demo (Firefox, IE7, IE6, Opera, Safari) | Demo with content | Source Code

Would you put cartoons on your software startup's website?

JKealey — Thu, 17 Jul 2008 20:42:00 -0400

We've revamped our website home page and wanted to invite you to visit it and let us know what you think. The general template of the site hasn't changed, as our enhancements focused on five core elements:

Simpler menu structure. When we first launched our website, our pages were never nested more than one level deep. We've since added new content and our site was getting harder to navigate. By going with a tree-like structure and adding markers to indicate which page you are currently viewing, we feel this solves our main usability problem.
Testimonials. Ian Graham, the man behind The Code Factory, an Ottawa-based software co-working location, talks about how he enjoyed doing business with us. We feel this touch increases our credibility, and the fact that we get things done.
Concise information. We've integrated much more information on our home page and re-worked the text to make it very concise. The home page leads you to numerous inner pages which feature more detailed information about our products. We're always re-working the innards of our site and we're never "done", but we feel this new home page will help drive traffic to the appropriate locations.... only ~~time~~ Google Analytics will tell.
Web 2.0 slider. We wanted to have a bit of fun even if it meant requiring JavaScript on our pages.
Cartoons. This is the most controversial aspect of our new home page. We've integrated cartoons on our homepage.... cartoons on a franchise software corporate site? Allow us to explain.

Why are we using cartoons?

Simply put, everyone we've talked to is divided in two completely distinct camps. One camp feels our cartoons makes our website unprofessional and inappropriate for the franchise industry's decision makers (one of the more vocal people in this camp is Michael Webster, Ph.D, LL.B.). Others feel it gives us a more personalized feel (a human touch) which increases their trust in our company.

There are hundreds of companies building software for the franchise industry and we want to show that we have a different philosophy from many of the old-school companies. Simply put, we (as web visitors) distrust generic consulting websites littered with stock photography and we didn't want to repeat the same mistake. We love to use pictures, but bad quality pictures or video are often worse than not having any. After a year and a half of having a more corporate feel (without using stock photography), we decided it was time to do something wilder. We hope to impress our target market with an atypical corporate website, even if it ruffles a few feathers.

We target small yet energetic franchise systems. These franchisors are not heads of billion dollar corporate empires, they are entrepreneurs who want to grow a concept which worked in their flagship store and scale it to the next level, via franchising. At their growth stage, these franchisors are looking for someone who can listen to their needs, build cost-effective software solutions, and help them grow. The franchises we deal with don't have large IT departments: they're looking to get outside help with technology, as they don't have the knowledge in-house. Outsourcing allows them to get more bang for their buck than hiring software engineers to build everything from scratch.

Why don't you like stock photos?

Does the following image incite you to contact a software firm for custom development?

When we shop around and find a company featuring such a picture, it reveals that they botched their web development work and they're probably going to botch any work we give them. Attention to detail is one characteristic we always want to see; however, we're not completely against stock photography but we disapprove of stock photography abuse. For example, if a company has a page talking about their team, and the team picture is actually a stock photo... they're taking it too far.

As a sidenote, Toronto-based Idée Inc. created an image search engine that not only helps identify stock photography but also people that have stolen your copyrighted images. Here's a screenshot of the results returned by TinEye for the previous image:

TinEye even found this modified image... very nice technology!

What do you think?

In summary, we decided to go with a cartoonish feel because we felt it was the best way to distinguish ourselves from our stereotypical competition. We purposefully project more youthful brand image, as we are targeting smaller franchise systems. Do you think differently? Are you an ardent defender of stock photo or do you think you've found the perfect balance of web 2.0 styling with the warm fuzzy feeling of seeing people? Do you agree with us? Let us know!