How to backup only table data from SQL Server 2008 R2 using C#

Question

I developed a good application with backup and restore feature. It works fine. every time I added new features to its SQL Server 2008 R2 database, for example add a new stored procedure or user-defined table type to upgrade my software.

My backup function is this:

protected int BackUpDataBase(string dbName, string address)
{
    try
    {
        using (_con)
        {
            string command = "Backup database " + dbName + " to disk='" + address + "'";
            SqlCommand cmd = new SqlCommand(command, _con);
            cmd.CommandType = CommandType.Text;
            connect();
            cmd.ExecuteNonQuery();
            return 1;
        }
    }
    catch (SqlException ex)
    {
        return ex.Number * (-1);
    }
}

and my restore function is here:

protected int RecoverDataBase(string dbName, string address)
{
    try
    {
        SqlConnection temp = new SqlConnection(_addressMaster);
        string Restore = "USE master" + Environment.NewLine;
        if (CheckDatabaseExists(dbName))
        {
            Restore += @"ALTER DATABASE [" + dbName + "]" + Environment.NewLine;
            Restore += @"SET OFFLINE WITH ROLLBACK IMMEDIATE" + Environment.NewLine;
            Restore += @"ALTER DATABASE [" + dbName + "] SET ONLINE" + Environment.NewLine;
        }
        Restore += @"RESTORE DATABASE [" + dbName + "] FROM DISK = N'" + address + @"' WITH FILE = 1,  NOUNLOAD, REPLACE, STATS = 10" + Environment.NewLine;
        Restore += @"ALTER DATABASE [" + dbName + "] SET Single_User WITH Rollback Immediate" + Environment.NewLine;
        Restore += @"ALTER DATABASE [" + dbName + "] SET Multi_User" + Environment.NewLine;
        using (temp)
        {
            using (SqlCommand cmd = new SqlCommand(Restore, temp))
            {
                cmd.CommandText = Restore;
                cmd.CommandType = CommandType.Text;
                temp.Open();
                cmd.ExecuteNonQuery();
                temp.Close();
                return 1;
            }
        }
    }
    catch (SqlException ex)
    {
        return ex.Number * (-1);
    }
}

Everything is ok BUT! The problem is here: I developed my upgraded Windows App with new stored procedures and etc, then install it to a new computer and wants to restore the old backup to my upgraded app, all new stored procedures and feature will back to Old because I restored entire old backup not only its data. So how can I restore only tables data from a backup file using C# and SQL query?

sepupic · Answer 1 · 2017-06-01T08:19:05.757

1

You cannot restore just the data, but you can script all your objects-modules (you can do it in some mouse click using SSMS) before you restore your backup and then drop all the modules and launch the script that re-creates all the modules.

Update:

if you cannot use SSMS, you can script your modules using

select definition from sys.sql_modules

as CREATE statements. The only caveat here is your objects must not be ever renamed because the definition in sys.sql_modules is not updated when you rename a module.

Other options are:

script the database with the data as INSERT statements (for small data sizes)
or import/export data using bcp utility. This does not script any object so you should truncate your tables before importing data or recreate the tables if their structure is different from what your backup contains

edited Jun 01 '17 at 08:19

answered Jun 01 '17 at 07:46

sepupic

8,409
1
9
20

yes, you are right but my clients do not access to SSMS. The Application install on SQL Server Express and everything needs operates runtime. – Reza Paidar Jun 01 '17 at 08:00
In this case all depends on how much data do you want to import. For small size database you can script all the tables including data as INSERT statements and launch this script – sepupic Jun 01 '17 at 08:02
For large databases you can export your data using bcp in flat files and then import them using the same bcp – sepupic Jun 01 '17 at 08:04
Or you can just create the creation scripts for all you modules using sys.sql_modules and launch this script after restoring from backup – sepupic Jun 01 '17 at 08:05
The last option is for you: script all your modules as CREATE using sys.sql_modules, restore from backup, launch the CREATE scripts – sepupic Jun 01 '17 at 08:08
can you update your post and tell me about BCP or next one method? – Reza Paidar Jun 01 '17 at 08:09

Hybris95 · Answer 2 · 2017-06-01T09:09:43.697

Restore to another environment

Restore your database on another database (a copy or an existing dev/test environment).

RESTORE statement

Then pick only the required data and copy them to the production environment.

The way to pick the data and insert them back will entirely depend on what data has to be transfered, and if there are any constraints to add them (indexes, keys, etc...)

Restore Stored Procedures

For example here you can get all the STORED PROCEDURE names to drop them.

SELECT 'DROP PROCEDURE ' + objects.name FROM sys.sql_modules
INNER JOIN sys.objects
ON objects.object_id = sql_modules.object_id
WHERE objects.type_desc = 'SQL_STORED_PROCEDURE'

Then you can recover create scripts with the following query

SELECT sql_modules.definition FROM sys.sql_modules
INNER JOIN sys.objects
ON objects.object_id = sql_modules.object_id
WHERE objects.type_desc = 'SQL_STORED_PROCEDURE'

DROP Procedure

Just put those in an EXEC and make sure it is executed on the Production Database and data is selected from the Copy Database.

Restore data (without indexes and keys)

DROP TABLE [prodDB].[mySchema].[myTable]
SELECT * INTO [prodDB].[mySchema].[myTable] FROM [copyDB].[mySchema].[myTable]

Also you can get table definitions from sys.objects table again.

SELECT schemas.name + '.' + objects.name FROM sys.objects
INNER JOIN sys.schemas
ON objects.schema_id = schemas.schema_id
WHERE type_desc = 'USER_TABLE'

Restore data (with indexes and keys)

TRUNCATE TABLE [prodDB].[mySchema].[myTable]
INSERT INTO [prodDB].[mySchema].[myTable] SELECT * FROM [copyDB].[mySchema].[myTable]

Also consider reading this post if you have any foreign keys referencing the restored tables : https://stackoverflow.com/a/253858/3635715

If you need to get keys definitions you can get them from [sys].[key_constraints]

Also consider backuping the production database before doing all this as a security measure. And to upvote the answer :) — Hybris95, Jun 01 '17 at 08:49
@Hybrys95 sys.objects does not have any table definition, what did you mean by saying >>>Also you can get table definitions from sys.objects table again<<< ? — sepupic, Jun 01 '17 at 09:12
`sys.objects` contains table names, this way you can list all the tables that might be transferred. For example if you want to transfer all user tables without distinctions. Elsehow, you can hardcode table names in your restore script if you don't want to use the definitions from the database. — Hybris95, Jun 01 '17 at 09:20

How to backup only table data from SQL Server 2008 R2 using C#

2 Answers2

Restore to another environment

Restore Stored Procedures

Restore data (without indexes and keys)

Restore data (with indexes and keys)