Sunday, October 21, 2012

Creating and Restoring SQL Server 2008 Database in Setup project

Windows Installer deployment allows you to create installer packages to be distributed to users; the user runs the setup file and steps through a wizard to install the application. This is done by adding a Setup project to your solution; when built, it creates a setup file that you distribute to users; the user runs the setup file and steps through a wizard to install the application.
There are five types of deployment projects: Merge Module Project, Setup Project, Web Setup Project, and Cab Project. The Setup Wizard is provided to step you through the process of creating deployment projects.This step-by-step article explains how to create a setup package in the Visual Studio .NET development environment for creating database on target server.

I assume that you have one solution project and "Northwind" database backup file already.

First, create a new setup project
  1. On the File menu, point to Add Project, and then click New Project.
  2. In the resulting Add New Project dialog box, select the Setup and Deployment Projects folder.
  3. Choose Setup Project for a standard setup as below:

Second, create an installer class for custom action
  1. On the File menu, click New Project.
  2. In the New Project dialog box, select Visual C# Projects in the Project Type pane, and then choose Class Library in the Templates pane. In the Name box, type CreatingDB.
  3. On the Project menu, click Add New Item.
  4. In the Add New Item dialog box, choose Installer Class. In the Name box, type Installer1.cs.
In Installer1.cs, it needs Microsoft.SqlServer.Management.Smo namespace contains classes that represent the core SQL Server Database Engine objects that include instances, databases, tables, stored procedures, and views. You will have to import the following four files to access all of the classes in the Microsoft.SqlServer.Management.Smo namespace.
  •     Microsoft.SqlServer.ConnectionInfo
  •     Microsoft.SqlServer.Management.Sdk.Sfc
  •     Microsoft.SqlServer.Smo
  •     Microsoft.SqlServer.SmoExtended
You can find these .dll files at C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies location.
Create one folder "DLL" in "CreatingDB" class library and copy the above four dll files. Then add these assemblies to your project as reference as well as System.Windows.Forms as shown below:

Develop the "Installer1.cs" as show below:
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration.Install;
using System.Linq;
using System.Data.SqlClient;
using System.IO;
using System.Security.AccessControl;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;

public partial class Installer1 : System.Configuration.Install.Installer
    public Installer1()

    public void RestoreDatabase(String databaseName, String backupFilePath, String serverName,
        String userName, String password, String dataFilePath, String dbLogicalName)
        // Create Restore instance
        Restore sqlRestore = new Restore();
        // Point to database
        BackupDeviceItem deviceItem = new BackupDeviceItem(backupFilePath, DeviceType.File);
        sqlRestore.Database = databaseName;
        // Connect to DB Server
        ServerConnection connection;
        if (userName == "") // for Windows Authentication
            SqlConnection sqlCon = new SqlConnection(@"Data Source=" + serverName + @"; Integrated Security=True;");
            connection = new ServerConnection(sqlCon);
        else // for Server Authentication
            connection = new ServerConnection(serverName, userName, password);
        // Restoring
        Server sqlServer = new Server(connection);
        sqlRestore.Action = RestoreActionType.Database;
        String dataFileLocation = dataFilePath + databaseName + ".mdf";
        String logFileLocation = dataFilePath + databaseName + "_Log.ldf";
        sqlRestore.RelocateFiles.Add(new RelocateFile(dbLogicalName, dataFileLocation));
        sqlRestore.RelocateFiles.Add(new RelocateFile(dbLogicalName + "_log", logFileLocation));
        sqlRestore.ReplaceDatabase = true;
        sqlRestore.PercentCompleteNotification = 10;
        catch (Exception ex)
        //it dont need to attach when logical name and file name is the same ,and file location is MS SQL Server Data filepath.
        AttachDatabase(databaseName, connection, dataFileLocation, logFileLocation);
        Database db = sqlServer.Databases[databaseName];

    public void AttachDatabase(String databaseName, ServerConnection connection, 
        String dataFileLocation, string logFileLocation)
        Server SqlServer = new Server(connection);
        StringCollection DatabaseFilesCollection = new StringCollection();
            SqlServer.AttachDatabase(databaseName, DatabaseFilesCollection);
        catch (Microsoft.SqlServer.Management.Smo.SmoException ex)
            //it throw error when logical name and physical file name is the same,
            //and they are in default MS SQL Server Data file path.
        catch (Exception ex)

    public override void Commit(System.Collections.IDictionary savedState)
        // Required permission
            DirectorySecurity dirSec = Directory.GetAccessControl(Context.Parameters["TargetDir"]);
            FileSystemAccessRule fsar = new FileSystemAccessRule
            , FileSystemRights.FullControl
            , InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit
            , PropagationFlags.None
            , AccessControlType.Allow);
            Directory.SetAccessControl(Context.Parameters["TargetDir"], dirSec);
        catch (Exception ex)
        // Parameters that comes from setup project (CreatingDBSetup) 
        RestoreDatabase(Context.Parameters["databaseName"].ToString(), Context.Parameters["backupFilePath"].ToString(), 
            Context.Parameters["serverName"].ToString(), Context.Parameters["userName"].ToString(), Context.Parameters["password"].ToString(), Context.Parameters
        ["dataFilePath"].ToString(), Context.Parameters["dbLogicalName"].ToString());
That's it for class library. Build it. Now, it need to use above created class library in "CreatingDBSetup" msi.
Go File System Editor by right click on msi project > View > File System.
In the File System Editor, select the Application Folder. On the Action menu, click Add, and then click Project Output as follow:

In the Add Project Output Group dialog box, select Primary output for the CreatingDB project as below:

Required dependencies are loaded automatically as you see in the below picture. Now, we should add database backup file. Right click on empty area in panel, select Add - File.

In Add Files dialog, select backup file "Northwind" and click Open.

Now, we create custom installation dialog for accepting user input such as server name and db connection info. Go User Interface Editor by right click on msi project > View > User Interface.

In the User Interface Editor, select Start node under Install. On the Action menu, choose Add Dialog.
In the Add Dialog dialog box, select the Textboxes (A) dialog, then click OK.
On the Action menu, choose Move Up. Repeat until the Textboxes (A) dialog is above the Installation Folder node and changes its Properties as below:
See Server Information Dialog for ouput
For Textboxes (B), do the same way like above setps.

See Database Connection Dialog for output

Go Custom Actions Editor by right click on msi project > View > Custom Actions.
There are four folders named Install, Commit, Rollback, Uninstall. We need to add custom action for each folder. Right click on Install folder, select Add Custom Action as below:

In Select Item in Project dialog, Click Application Folder to get in. Select Primary output from CreatingDB (Active) and click OK. Do the same steps for other folder.

You will see Primary output from CreatingDB (Active) section under the Install and other folders as shown below image. Click each section and change CustomActionData property to the following string:
/TargetDir="[TARGETDIR]\" /databaseName="[DBNAME]" /backupFilePath="[TARGETDIR]Northwind.bak" /serverName="[SERVERNAME]" /userName="[USERNAME]" /password="[PASSWORD]" /dataFilePath="[DBLOC]\\" /dbLogicalName="Northwind"

Now, you finished developing Setup Project and Build and Install it. You will see the following wizards step by step.

Server Information Dialog
Database Connecton Dialog

Reference Article is here.


  1. Hello
    I am deploying my project called stock.I have used all the steps ...and thank you for that.
    But when i install, it goes good up to the last step. but it couldn't finish the installation. I got this error.
    "Error 1001 Exception accord while initializing the installation.Sytem.IO.FileNotfindException. Couldn't load file or assembly. file///C\Windows\system32\Romhay(Name of the product. the system couldn't find the file specified."

  2. Sorry, I am reply late.I am not sure about your FileNotfindException. Keep in mind that we need to add database backup file in File System Panel(Northwind.bak in this post) and make sure CustomActionData property correct. If you have still error, you can send me your program. Thank.

  3. hye,
    I am deploying a windows application using c# and Sql server database.
    after reading your steps, I felt that my problem is solved, but when I created a installer class, copy your code to it and tried to build it I got many syntax
    "'RestoreDB.Class1.Commit(System.Collections.IDictionary)': no suitable method found to override"
    "The name 'Context' does not exist in the current context"
    "'System.Runtime.Remoting.Contexts.Context' does not contain a definition for 'Parameters'"
    "'base' does not contain a definition for 'Commit'"
    Please help me....

  4. hye,
    Syntax errors have gone, everything is going perfectly, but installation is still not completed properly, In the last step I got an Exception
    "The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2869."

  5. Hi,

    Please try Right-click on the setup.exe file and select ‘Run as administrator’. Otherwise, go to Control Panel > User Account > Change Security Settings and Uncheck the Use User Account Control (UAC) and reboot your computer and try install it. It may be helpful, I hope. Thank.

  6. Thanks a lot .it really helpful..
    Its working.

  7. Is the config file is needed for this project...if it is needed,what is it code????.....