SQL Server Database Restore with ReadXML

Question

I've backup from a sqlserver created with this procedure:

protected void WriteXML(string tableName)
{
    using (SqlConnection cnn = new SqlConnection(ConnectionString))
    {
        using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM
        [" + tableName + "];", cnn))
        {
            cnn.Open();
            DataSet ds = new DataSet();
            da.Fill(ds, tableName);
            ds.WriteXml(Server.MapPath("App_Data\\" + tableName + ".xml");
            ds.WriteXmlSchema(Server.MapPath("App_Data\\" + tableName + ".xslt");
            cnn.Close();
        }
    }
}

Now I want to restore this database. Is there an easy way to do this?

Answer 1

Basically, no.

• you'll have foreign keys and triggers to think about (order of tables)
• data will have changed during the backup (missing parent or children)
• you won't have backed up system tables and metadata
• you won't have backed up security

Use SQL Server native backup and restore like everyone else. Sorry if this is brutal, but I've never known anyone to try and backup a database like this.

After comments:

Why not ask your host to FTP (etc) a backup to you?

Also, if you can backup every table in the database via a web site and it becomes accessible then it means you have poor security...

Answer 2

Okay, here is the solution based on eggheadcafe.com post

protected void ReadXML(string tableName)
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("App_Data\\" + tableName + ".xml");
    using (SqlBulkCopy sbc = new SqlBulkCopy(ConnectionString))
    {
        sbc.DestinationTableName = tableName;
        sbc.WriteToServer(ds.Tables[tableName]);
        sbc.Close();
    }
}

Also we have to SET IDENTITY_INSERT [tableName] ON/OFF if table has an identity column.