Database backup and restore are the most important actions we need for our published web applications. Some shared hosts like Godaddy do not allow backup/restore for your databases, although godaddy has introduced backup/restore actions but is only for backup files (*.bak) have been created by their system, however *.bak files that made from local machines or any other system cannot be restored.
Here is how to backup/restore databases programatically without the need to execute backup or restore commands.
Backup:
You can backup all or choosen tables into an xml file. The following command gets all table:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME <> 'dtproperties'
By selecting all from the retrieved tables into one dataset and then calling DataSet.WriteXml() , would write all tables data into one xml file.
foreach (string table in tables)
{
cmd.CommandText = string.Format("SELECT * FROM [{0}]",table);
DataTable dt = new DataTable(table);
da.Fill(dt);
ds.Tables.Add(dt);
}
Restore:
1- First step is to disable all constraints and delete all data in each table before insert statement can execute:
string beforeInsertComm = string.Empty;
beforeInsertComm = "sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\" ";
beforeInsertComm += "DELETE [{0}]";
beforeInsertComm = string.Format(beforeInsertComm, table);
cmdBeforeInsert.CommandText = beforeInsertComm;
cmdBeforeInsert.Connection.Open();
cmdBeforeInsert.ExecuteNonQuery();
cmdBeforeInsert.Connection.Close();
sb.Append(beforeInsertComm);
ALTER TABLE statement will disable constraint for all database tables, and will automatically be restored to its default value when closing the connection; therefore ALTER statement must be followed by DELETE statement in the same command execution so that DELETE execution will not be violated with foreign key constraints.
2- Restoring data read from xml by DataSet.ReadXml() to the database:
a- We must set indentity_insert to on to allow insert into identity columns:
string beforeInsertComm = "IF(EXISTS(SELECT Name FROM SYSCOLUMNS WHERE COLUMNPROPERTY(OBJECT_ID('{0}'),Name,'ISIDENTITY') = 1))BEGIN ";
beforeInsertComm += "SET IDENTITY_INSERT [{0}] ON END ";
beforeInsertComm = string.Format(beforeInsertComm, dt.TableName);
b- Now comes the generating insert command for the given table:
sb.Append("INSERT INTO [" + dt.TableName + "](");
for (int x = 0; x < dt.Columns.Count; x++)
{
string column = dt.Columns[x].ColumnName;
sb.Append("[" + column + "]");
if (x < dt.Columns.Count - 1)
sb.Append(",");
}
sb.Append(") VALUES (");
for (int y = 0; y < dt.Columns.Count; y++)
{
string column = dt.Columns[y].ColumnName;
sb.Append("@" + column);
if (y < dt.Columns.Count - 1)
sb.Append(",");
SqlParameter p = new SqlParameter();
p.ParameterName = "@" + column;
p.SourceColumn = column;
cmdInsert.Parameters.Add(p);
}
sb.Append(")");
3- Reenable constraints:
sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
Note:
For better performance ,Bulk insert command is one of the fastest solutions but can only run under sysadmin or bulkadmin roles.
Source code is available within attachments.
Hope that helps,
Enjoy
BackupRestore.rar (182.7 KB)
9bf3d913-4e11-4820-9207-fe99b8abdd3a|0|.0