Run SQL Server Scripts using C#

  • I discovered Powershell a couple of years ago and I now open Visual Studio just to get TFS Explorer. 😀


    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • There is 1 central thing about stuff you can run as a SQL statment and that is:

    It's a SQL statement. 😎

    I have used the data access stuff just fine for running install, change, and configuration scripts. For error handling it is just like INSERT, UPDATE, or DELETE.

    As all the data handling stuff is built into .Net since 2.0 that means that most of the SQL tools that I write are ZERO INSTALLL. That means that I pass out an executable and nothing else.

    ATBCharles Kincaid

  • SQL-DBA (4/27/2012)

    I think many people are missing the point, or let's say use case, of this article. Users are not always rationale, nor do they necessarily have SSMS installed. So if you have someone that says "I MUST be able to generate a full backup at any time during the day and I need 1 minute turnaround time (can you say developers?)" then you might put together something like this app as an .exe and say "Just click the button and you are all set."

    Perhaps, and I must say that I appreciate the time and effort that went into writing this.

    But with that said, I think its better to fix the root problem. A developer that will ever touch SQL in any probably should have SSMS installed and know how to use it. Even if I wanted to create an executable, this would probably be easier in powershell, unless I wanted a GUI on top of it (and if I wanted a GUI, I might just look at Red Gate's Scripts Manager, which is free and includes a couple of nice backup scripts). And if I was going to do this in C# for whatever reason, I would use SMO (in fact, I would probably use SMO if I did this in PowerShell).

    In short, I would like to say thank you to the author for posting this, but I do not think I would take this approach in production.

    Timothy A Wiseman
    SQL Blog:

  • Yes, SMO in this case is a better option, but this code is useful to run any command line tool.

    For example you can call the DTUtil, Tablediff, sqlcmd, DTexec with this project.

    You only need to modify the following line of code:

    ProcessStartInfo info = new ProcessStartInfo("sqlcmd", @" -S .\sqlexpress -i C:\backup.sql");

  • While I actively follow the C# post, what I really want to know is how we geeks can be more actively involved in revenge against these spamming idiots....or is this out of our control because of botnets?

  • Perry Whittle (4/27/2012)

    My preference would be to invoke the SMO objects directly within C# to backup the database

    Agreed. I've even used the SMO Restore class to loop through a directory and restore multiple backups (including differential and transaction log). This is really rough code, but something like this:

    for (int i = 0; i < count; i++)


    string devicediff = path + abcid + ".diff" + (i);

    var isBakFile = filearray.ToLower().Contains(".bak");

    var isDiffFile = filearray.ToLower().Contains(".diff");



    if (isBakFile || isDiffFile)


    //Initiating restore class

    Restore res = new Restore();

    res.Database = abcid;

    res.Action = RestoreActionType.Database;

    //Condition - first loop, restore full primary .bak file

    if (isBakFile)


    MessageBox.Show("Restoring full backup from "+ devicefull);

    res.Devices.AddDevice(devicefull, DeviceType.File);

    res.ReplaceDatabase = true;

    res.RelocateFiles.Add(new RelocateFile(logicdataname, srcDestDataFilePath));

    res.RelocateFiles.Add(new RelocateFile(logicLogName, srcDestLogFilePath));


    //Conditional For every loop after first iteration, assume differential backup

    MessageBox.Show("if isDiffFile");

    if (isDiffFile)


    MessageBox.Show("Restoring a Differential from " + devicediff);

    res.Devices.AddDevice(devicediff, DeviceType.File);


    //Not the last file in backupset no recovery

    if (i < count - 1)


    MessageBox.Show("Restoring w/ No Recovery.");

    res.NoRecovery = true;


    else //Conditional last file (or only file) in the group will restore with recovery


    MessageBox.Show("Restore w/ recovery");

    res.NoRecovery = false;


    //Initiate Restore

    MessageBox.Show("Restoring... Chill Homes");




    catch (Exception ex)


    MessageBox.Show( ex.Message.ToString() + ex.InnerException.ToString());

    Dts.TaskResult = (int)ScriptResults.Failure;


  • Hello,everybody,the good shopping place,the new season approaching, click in. Let's facelift bar!

    ====== =======

    Air jordan(1-24)shoes $33

    UGG BOOT $50

    Nike shox(R4,NZ,OZ,TL1,TL2,TL3) $33

    Handbags(Coach lv fendi d&g) $33

    Tshirts (Polo ,ed hardy,lacoste) $16

    Jean(True Religion,ed hardy,coogi) $30

    Sunglasses(Oakey,coach,gucci,Armaini) $12

    New era cap $9

    Bikini (Ed hardy,polo) $18


    ========= ==========

    ========= ==========

  • Nice code, Both works well in my hands

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply