Creating nopCommerce Database Backup Programmatically Using C#

Being a nopCommerce programmer, have you ever encountered a situation where you need to take over an existing nopCommerce projects from other developers but are not provided with enough information? I have!

Recently, I am working on a project on nopCommerce customization that put me in hot water. The client has only provided FTP access to compiled files - no source code, no database backups, no RDP access, no nothing!

Because the project is an already-customized version of nopCommerce 3.40, I am faced with the following issue:

1. No source code means I can't check what has been customized
2. No database backups means that I can't even infer the customization from database
3. The database is fire-walled. That means even if I can get the Connection String from Settings.txt, I can't connect to it using Microsoft Sql Server Management Studio

Although the project was only involving theme development & theme customization, the challenge is big enough to have stopped my team from normal development for a few hours.

We needed a solution to solve [1], [2] and [3]. And the solution? Programmatic generation of nopCommerce SQL Server database backup using C#!

SQL Server Management Objects (SMO) to the help!

Is it even possible to create MS SQL Server database backup with C#? It turned out that the answer is a big YES. Microsoft is kind enough to provide a suite of C# SDK to program against MS SQL - it is called SQL Server Management Objects (SMO).

Microsoft's explanation of SMO is (quoted):

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management.

So we are very sure that C# creation of MS SQL Server backup is possible! The next problem?

Because I've only access to the compiled files (.DLL and not .CS), there is no way I can add new codes to the project! Or maybe I can?

Thanks to Microsoft's ASP.Net Web Pages technology, I can add codes just by using .CSHTML (no need for .CS)!

So the plan? To create a new ASP.Net Web Pages .CSHTML that allows me to generate a database backup. And the backup is generated in the form of SQL scripts, just like what will get generated using the "Generate Scripts" feature of SQL Server Management Studio.

Sample screenshot of using the "Generate Scripts" feature of SQL Server Management Studio to generate database backup scripts

Writing the codes...

Before we actually writes the code, there is one thing we need to do - to enable Web Pages in nopCommerce. Web Pages is, by default, disabled in nopCommerce's web.config, so we need to enable it temporarily:

There are also 2 very useful resources you need to refer to. The techniques documented in this blog post is based largely on the following articles:

• Generating SQL Backup Script for Tables & Data from any .NET Application using SMO
• How to resolve "SqlClrProvider not found" without installing Sql Server on the client machine or needing a deployment project

Below is an excerpt from the actual codes I've written. The full source code can be downloaded here:

var filePath = HostingEnvironment.MapPath(string.Format("~/App_Data/Backup_{0}/script.sql", DateTime.Now.ToString("yyMMdd-hhmmss", CultureInfo.InvariantCulture)));
var folderPath = filePath.Replace(@"\script.sql", "");
if (!Directory.Exists(folderPath))
{
 Directory.CreateDirectory(folderPath);
}

ServerConnection connection = null;
if (string.IsNullOrWhiteSpace(dbUserId) ||
 string.IsNullOrWhiteSpace(dbPassword))
{
 connection = new ServerConnection(dbHost);
}
else
{
 connection = new ServerConnection(dbHost, dbUserId, dbPassword);
}
Server srv = new Server(connection);
Database dbs = srv.Databases[dbName];
ScriptingOptions options = new ScriptingOptions();
options.ScriptData = true;
options.ScriptDrops = false;
options.FileName = filePath;
options.EnforceScriptingOptions = true;
options.ScriptSchema = true;
options.IncludeHeaders = true;
options.AppendToFile = true;
options.Indexes = true;
options.WithDependencies = true;

foreach (Table tbl in dbs.Tables)
{
 tbl.EnumScript(options);
}

string zipPath = filePath.Replace(folderPath, ".zip");
ZipFile.CreateFromDirectory(startPath, zipPath);
File.AppendAllLines(HostingEnvironment.MapPath("~/App_Data/BackupLog.txt"), new List<string>() { filePath });

Notice that I am creating a ZIP file from the generated .SQL. This is very useful, as the generated uncompressed text file can be very large (in my case about 2GB) for large database. And it can ZIP down to use only a few hundreds MB. (Yes, text files are highly compressible!)

You can download the full source code here. (Remember to change the database info!)

Also note that you need to add a few DLLs to your /bin folder.

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.SqlEnum.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• Microsoft.SqlServer.SqlClrProvider.dll
• System.IO.Compression.dll
• System.IO.Compression.FileSystem.dll

How to use the tool

After you've updated web.config, and added the required .DLLs to /bin folder, upload the full source code to your nopCommerce website. If you uploaded it to the root folder, it is accessible at http://yourstore.com/sqlutils.cshtml, with an interface that looks like below:

I've included a few other features in the tool:

• Get SQL Server Version - useful if you want to find out the exact version of the SQL Server database installed
• Clear nopCommerce Log - useful if you want to clear nopCommerce log before backup, as nopCommerce log can take quite some space
• Get Database Size - list out, in descending order, the size in KB of each database table
• Generate Script from Database - the actual backup generation feature

Enjoy the code! :)