C# - Generate Database Backup Script In-Memory

Question

In a C# application, I know using ADO.NET we can execute a backup command like this:

BACKUP DATABASE MyDb TO DISK='C:\\MyDb.bak'

and take a database backup and store it at some given location.

I want to take backup of database in-memory i.e. return the backup script content (schema and data) which I can later save as .sql file at some location.

Is this possible?

The BACKUP DATABASE command in T-SQL does NOT return a SQL-based "script file" or anything - it's a proprietary, binary format only. — marc_s
– marc_s, Commented Feb 29, 2020 at 13:04
Yes. That I know. Here what I mean is to return schema and data which we get when we generate script to new window — Reyan Chougle
– Reyan Chougle, Commented Feb 29, 2020 at 13:09
Well as I said - you cannot use BACKUP DATABASE for that. There are no options or switches to make this command return SQL scripts to create database objects and fill in the data.... — marc_s
– marc_s, Commented Feb 29, 2020 at 13:11
You can use SQL Management Objects to generate scripts, etc just like you would from SSMS (schema, data, both) . Perhaps take a look at that. — pinkfloydx33
– pinkfloydx33, Commented Feb 29, 2020 at 15:15
You can achieve it using SMO, have a look at this thread here. stackoverflow.com/questions/36978473/… — iVad
– iVad, Commented Feb 29, 2020 at 17:31

Reyan Chougle · Accepted Answer · 2020-03-14 15:36:35Z

6

I achieved it using SQL Server Management Objects (SMO). Thanks to all the friends who helped in comments.

First, install Microsoft.SqlServer.SqlManagementObjects from nuget package manager.

The working code:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

var script = new StringBuilder();

Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));
Database database = server.Databases[databaseName];
ScriptingOptions options = new ScriptingOptions
{
    ScriptData = true,
    ScriptSchema = true,
    ScriptDrops = false,
    Indexes = true,
    IncludeHeaders = true
};

foreach (Table table in database.Tables)
{
    foreach (var statement in table.EnumScript(options))
    {
        script.Append(statement);
        script.Append(Environment.NewLine);
    }
}

File.WriteAllText(backupPath + databaseName + ".sql", script.ToString());

edited Mar 14, 2020 at 15:36

answered Feb 29, 2020 at 20:24

Reyan Chougle

5,3813 gold badges37 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# - Generate Database Backup Script In-Memory

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related