A simple database backup restore class based on ADO.net

During development/testing, to backup/restore database is a common task. Manual clicks are not ideal solution, to automation, commonly we have two solutions:

1. Leverage database server feature, such as SQLServer backup/restore, which is database level.
2. Backup/Restore in table level, store it in file.

Neither of them is perfect:

	Solution1:  DB Level	Solution 2: Table Level
PROS	The feature is provided by product which should be stable.Foreign key can be handled correctly and easily	Fine grained control, be able to skip some tables to get faster speed.Easy to deal with Replication/Mirroring/Logshipping
CONS	Very difficult under multi-server environment. Such as Replication, Mirroring, etc.	If table has foreign key, the logic would be very complicated

I am lucky that our new system adopts the foreign key free sql design, so solution 2 looks a good choice for us.

Here is a very simple class to do the back/restore job:

To enumerate table names:

    1: public List<string> GetTableNames()
    2: {
    3:     List<string> result = new List<string>();
    4:     string tableName = string.Empty;
    5:     string sql = "SELECT name AS table_name, * FROM sys.tables WHERE Type = 'U'";
    6:     using (SqlConnection conn = new SqlConnection(ConnectionString))
    7:     using (DataTable table = new DataTable())
    8:     using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
    9:     {
   10:         adapter.Fill(table);
   11:         foreach (DataRow row in table.Rows)
   12:         {
   13:             result.Add(Convert.ToString(row["table_name"]));
   14:         }
   15:     }
   16:     result.Sort();
   17:     return result;
   18: }

To backup tables to DataSet/String:

    1: /// <summary>
    2: /// Pass null as includedTables if you want to backup all.
    3: ///     Actual tables = includedTables - excludedTables
    4: /// </summary>
    5: public DataSet BackupDatabase(List<string> includedTables = null, List<string> excludedTables = null)
    6: {
    7:     includedTables = includedTables ?? GetTableNames();
    8:     if (excludedTables != null)
    9:     {
   10:         includedTables = includedTables.Except(excludedTables, StringComparer.OrdinalIgnoreCase).ToList();
   11:     }
   12:  
   13:     DataSet result = new DataSet();
   14:     using (SqlConnection conn = new SqlConnection(ConnectionString))
   15:     {
   16:         includedTables.ForEach(t =>
   17:             {
   18:                 string sql = "select * from " + t;
   19:                 using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
   20:                 {
   21:                     adapter.Fill(result, t);
   22:                 }
   23:             });
   24:     }
   25:     return result;
   26: }
   27:  
   28: public string BackupDatabaseToString(List<string> includedTables = null, List<string> excludedTables = null)
   29: {
   30:     using (var dataSet = BackupDatabase(includedTables, excludedTables))
   31:     using (MemoryStream ms = new MemoryStream())
   32:     {
   33:         dataSet.WriteXml(ms, XmlWriteMode.WriteSchema);
   34:         return Encoding.UTF8.GetString(ms.ToArray());
   35:     }
   36: }

To restore table from DataSet:

    1: public void RestoreDatabase(DataSet newDataSet)
    2: {
    3:     using (TransactionScope scope = new TransactionScope())
    4:     using (SqlConnection conn = new SqlConnection(ConnectionString))
    5:     using (DataSet currentDataSet = new DataSet())
    6:     {
    7:         conn.Open();
    8:         foreach (DataTable newTable in newDataSet.Tables)
    9:         {
   10:             string tableName = newTable.TableName;
   11:             SqlCommand deleteAllCmd = new SqlCommand("delete from " + tableName, conn);
   12:             deleteAllCmd.ExecuteNonQuery();
   13:             // reseed the indetity columns
   14:             SqlCommand reseedCmd = new SqlCommand(String.Format("DBCC CHECKIDENT ('{0}', RESEED, -1)", tableName), conn);
   15:             try
   16:             {
   17:                 reseedCmd.ExecuteNonQuery();
   18:             }
   19:             catch { } // ignore the error from table without identity columns
   20:  
   21:             using (SqlDataAdapter adapter = new SqlDataAdapter("select * from " + tableName, conn))
   22:             {
   23:                 adapter.Fill(currentDataSet, tableName);
   24:                 foreach (DataRow newRow in newTable.Rows)
   25:                 {
   26:                     currentDataSet.Tables[tableName].Rows.Add(newRow.ItemArray);
   27:                 }
   28:                 Console.WriteLine("restoring table '{0}' with {1} rows", tableName, newTable.Rows.Count);
   29:                 SqlCommandBuilder dummyCmd = new SqlCommandBuilder(adapter); // this would generate insertCommand inside adapter
   30:                 adapter.Update(currentDataSet, tableName);
   31:             }
   32:         }
   33:         scope.Complete();
   34:     }
   35: }

You can use this in your c# automated test case, or powershell.