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

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:

  • Finding common value in two columns
  • Test unique filtered index with tSQLt
  • get a count of each value from every column in a table SQL Server
  • Complicated SQL Query--finding items matching multiple different foreign keys
  • SQL Server Rounding Issue where there is 5
  • SQL Server: how to calculate median (group by)?
  • 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?

  • SQL Server float data type understanding
  • using a SqlConnection in C++ / .NET
  • How to get SQL Server DateTime field in ODBC native client
  • Pass widget data using json and saving to database
  • Need help with some stored procedure
  • How to synchronize Database and DataGridView
  • 2 Solutions collect form web for “How to backup only table data from SQL Server 2008 R2 using C#”

    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

    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]

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.