Backup the data from your Sql Server

An often requirement in a CMS is the user to be able to backup the database of its CMS installation. If you are using MS Sql Server there is a simple command that creates a .bak file of your database.

BACKUP DATABASE [mydb] TO DISK = N'<bak_file_path>'

Restoring the database is also simple with the use of the restore command.

USE master RESTORE DATABASE [mydb] FROM DISK = N'<bak_file_path>' WITH REPLACE

In many cases though, the db user credentials don’t have sufficient rights to execute these commands. Also, a full backup is not necessary, the most critical part is the data. I came up with a solution that creates a full data backup of your database. This process works also for MySql databases, or any database that can be accessed with ADO.NET. The first function is a query to the database that gives all the tables of our database.

Dim myComm As New SqlCommand("SELECT name FROM sys.Tables", myConnection)
Dim myReader As SqlDataReader = myComm.ExecuteReader()
While (myReader.Read())
    BackupTable(myReader("name"))
End While
myReader.Close()
myComm.Dispose()

For MySQL the equivalent command to get the tables of a database is SHOW TABLES. The above code calls the BackupTable(…) function, which loads all data to a dataset and then exports it to a xml file.

Dim myComm As New SqlCommand("SELECT * FROM " & TableName, myConnection)
Dim ds As New DataSet
Dim myAdapter As New SqlDataAdapter(myComm)
myAdapter.Fill(ds, TableName)

For Each _Column As DataColumn In ds.Tables(0).Columns
    If (_Column.DataType Is System.Type.GetType("System.DateTime")) Then
        _Column.DateTimeMode = DataSetDateTime.Unspecified
    End If
Next

ds.WriteXml(FilePathToXmlFile, XmlWriteMode.WriteSchema)
myComm.Dispose()

We must use the option XmlWriteMode.WriteSchema to the ds.WriteXml() function so that the schema is also written to the xml file. If we don’t use this option, we will not be able to use the xml file to restore its data back to the table. Also, the fix for the DateTime columns is also necessary is we intend to restore the data in a sql server in a different timezone from the server we extracted the data. By default the DateTimeMode in a DateTime field is set to Local. So, when we restore the data, all dates will be shifted to the local time of the target databse server. Actually, the value that is stored is the UTC time with an hour offset. Setting the DateTimeMode to Unspecified, the offset is ignored. I met this odd behavior when I moved some data from a database server in Greece to a Database in UK. The time offset between the two countries is 2 hours. When I loaded the data to the English sql server all datetime values shifted 2 hours backwards. I was saving all my datetime fields in UTC time, but the backup process put the hour offset in the xml file. With this approach you avoid that. The backup process is really easy, not intended though for large databases. But a typical CMS database does not contain that much data so is a good solution.

Let’s see the restore function now.

Run your tests in a database with dummy data, or backup your db before trying some code

Let’s assume that all xml files are kept in the same folder. For each file we will delete the existing data and then load the rows from the xml file. This process works only for MS Sql Server. For MySQL we need to follow a different approach, which we will see later.

For Each _File As String In IO.Directory.GetFiles(RootFolder & "Tmp/", "*.xml")
    Dim TableName As String = _File.Substring(_File.LastIndexOf("-") + 1, _File.Substring(_File.LastIndexOf("-") + 1).LastIndexOf("."))
 
    Dim myComm As New SqlCommand("DELETE FROM " & TableName, myConn)
    myComm.ExecuteNonQuery()
    myComm.Dispose()
 
    Dim dsXml As New DataSet
    dsXml.ReadXml(_File)
    If (dsXml.Tables.Count = 0) Then Continue For 
 
    Dim BCP As New SqlBulkCopy(myConn, SqlBulkCopyOptions.KeepIdentity, myComm.Transaction)
 
    BCP.DestinationTableName = TableName
    BCP.WriteToServer(dsXml.Tables(0).CreateDataReader)
Next

We use the SqlBulkCopy (which is Sql Server specific) function to load all rows from the xml file to the table. With the SqlBulkCopyOptions.KeepIdentity option we can load the same id values for all the identity seed fields. For MySQL we have a different approach. At first, we delete the existing rows and then we load the (empty) table to a dataset. This way we load the schema of the table to the dataset. To this empty dataset we load the data from the xml file (row by row). Finally we load the changed dataset back to the database. There is a BIG catch here. The auto increment fields have new values and not the values from the xml file. I couldn’t find a way to address this. I ended up in calling the ALTER statement for each table that had auto increment fields to remove this property. After the restore, I assigned the auto increment property back to the fields. Not very neat approach, since dropping and re-assigning the auto-increment property is done hard coded.

Dim myComm As New MySqlCommand("DELETE FROM " & TableName, myConn)
myComm.ExecuteNonQuery()
myComm.Dispose()
 
myComm = New MySqlCommand("SELECT * FROM " & TableName, myConn)
Dim myAdapter As New MySqlDataAdapter(myComm)
Dim myBuilder As New MySqlCommandBuilder(myAdapter)
Dim dsSchema As New DataSet
myAdapter.Fill(dsSchema, TableName)
dsSchema.Tables(0).Rows.Clear()
 
Dim dsXml As New DataSet
dsXml.ReadXml(XmlFile.FullName)
If (dsXml.Tables.Count = 0) Then Exit Sub
 
For Each _row As DataRow In dsXml.Tables(0).Rows()
    dsSchema.Tables(0).NewRow()
    dsSchema.Tables(0).Rows.Add(_row.ItemArray)
Next
 
myAdapter.Update(dsSchema, TableName)
myAdapter.Dispose()

I hope you found this article useful. If someone has a different or better approach I would be glad to share it. My main goal was to give the admin of my CMS a one-click backup solution.