Introduction

In this article I will run a SQL Server script to backup a database using c# in visual studio with the help of the sqlcmd. SQLCMD is the command line of the SQL Server. This command line will let us run T-SQL scripts. SQLCMD is a fast and easy method to run scripts. 

In Visual Studio we are going to call the sqlcmd using the ProcessStartInfo Class. This class is used to start processes.  In this code we are going to call the sqlcmd process. We are going to have at the end of the article a Windows Form Application with a button to call a .sql script. In this case, the script will generate a database backup:

The button "call the sqlcmd" will create a SQL Server backup using the ProcessStartInfo class.

For more information about the ProcessStartInfo, the sqlcmd and running scripts with the sqlcmd you can see the references at the button of this article.

Files included in resource files

• test.zip is the backup of the database test.
• callSQLcmd.zip is the project in Visual Studio 2010.

Demonstration

First of all, let’s create a script named backup.sql to backup a database. The script name is backup.sql and it contains

BACKUP DATABASE [test] TO  DISK = N'C:\backup\test.bak'
  WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

This script will create a backup of the test database (you can create a database named test or replace the name test with an existing database name).

To run the backup.sql script using the sqlcmd, go to the cmd and run the following commands:

sqlcmd -S .\sqlexpress -i C:\backup.sql

Explanation

-S .\sqlexpress (note that the –S is uppercased) is the server information. In this case, it is the local machine and the SQL Server Express Edition instance. For example, if you are using an instance by default and the SQL Server name is: Server1 and the Active Directory domain is named sqlcentral, the command line would be:

sqlcmd -S sqlcentral\Server1 -i C:\backup.sql

-i c:\backup.sql

The parameter -i is the input (not that the parameter -i is lowercase) and c:\backup.sql is the path of the script. By default, the Windows authentication is used. See the SQLCMD command line in references for further information.

Using the Visual Studio Windows Form in C# to call the sqlcmd

We have a SQL command line that creates a SQL Server backup. Now I am going to create a C# Windows Form in Visual Studio and drag and drop the button from the Tools to the design pane. The button will call the sqlcmd command line to create the backup named test using the sqlcmd.

Double click on the button and add the following code:

using System.Diagnostics;

Also

// Calls the sqlcmd
ProcessStartInfo info = new ProcessStartInfo("sqlcmd", @" -S .\sqlexpress -i C:\backup.sql");
//  Indicades if the Operative System shell is used, in this case it is not
info.UseShellExecute = false;
//No new window is required
info.CreateNoWindow = true;
//The windows style will be hidden
info.WindowStyle = ProcessWindowStyle.Hidden;
//The output will be read by the starndar output process
info.RedirectStandardOutput = true;
Process proc = new Process();
proc.StartInfo = info;
//Start the process
proc.Start();

The most important section of the code is the following:

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

In this section we are calling the sqlcmd and executing the backup.sql script using the processStartInfo. This is the core of the code. The rest is just the configuration of the process parameters.

The code is calling to the Server SQL Express Edition Instance using the -S parameter. The -i parameter is the input. In this example the input is the backup.sql script. The SQL Server Authentication used by default is the Windows Authentication, therefore the Windows User that runs the application should have permissions in the SQL Server Database.

Testing your code

Now, press the start debugging button in Visual Studio and click on the windows form button. A new backup named test.bak should be created in the c:\ with the visual studio.

Conclusion

In this article we learned how to call the sqlcmd and run a .sql script using the visual studio.

References

Introduction to sqlcmd: http://www.sqlservercentral.com/articles/2224/

Running scripts with sqlcmd: http://www.sqlservercentral.com/articles/SQLCMD/66183/

SQLCMD command line reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx

Forums related: http://www.sqlservercentral.com/Forums/Topic572280-8-1.aspx

ProcessStartInfo class in C# http://msdn.microsoft.com/en-us/library/system.diagnostics.processstartinfo.aspx