Coding Still

  • Home
  • About

Backup the data from your Sql Server

January 5, 2011 By _tasos Leave a Comment

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.

Filed Under: .NET Development, ASP.NET, Databases Tagged With: Data backup, MySQL, SQL, SQL Server

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

  • .NET Development
  • ASP.NET
  • Databases
  • Fun
  • IIS
  • JavaScript
  • Web Development

Tags

.NET Core ANTLR ASP.NET Ajax ASP.NET Core ASP.NET MVC ASP.NET Web Forms AWS Bouncy Castle Chartjs Client info detection Comic Continuous integration CSS Data backup Date handling Firefox addons Github HigLabo HTML5 Image manipulation jQuery JWT MySQL Nuget OAuth OOP Oracle PHP Regular expressions SEO Social media SQL SQL Server UI/UX Url rewriting Videos Visual Studio Web design

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Archives

  • December 2018
  • November 2018
  • February 2018
  • August 2016
  • June 2016
  • May 2016
  • February 2016
  • January 2016
  • August 2015
  • July 2015
  • October 2014
  • July 2014
  • November 2013
  • April 2013
  • February 2013
  • January 2013
  • December 2012
  • November 2012
  • August 2012
  • May 2012
  • February 2012
  • December 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • October 2010
  • September 2010
  • August 2010
  • July 2010

Recent Posts

  • Jwt Manager
  • Things around the web #5
  • Query JSON data as relational in MySQL
  • Create and sign JWT token with RS256 using the private key
  • Drop all database objects in Oracle
  • Create and deploy a Nuget package
  • Shneiderman’s “Eight Golden Rules of Interface Design”
  • Convert Long raw fields to varchar2 in Oracle with plain SQL
  • Exporting data to Excel
  • Things around the web #4

Latest tweets

  • React v16.8: The One With Hooks https://t.co/ZhKLo79Xfs #javascript #reactjs February 13, 2019 19:26
  • How Many .com Domain Names Are Unused? https://t.co/cWcjhrKoDg #domainnames #internet February 13, 2019 19:15
  • The TypeScript Tax - A Cost vs Benefit Analysis https://t.co/nnrT6gMEtZ #javascript #typescript #webdevelopment January 26, 2019 07:20
  • It is really important to be able to disconnect; IMHO a key aspect to really achieve productivity https://t.co/Z5AHTmjmDl December 24, 2018 16:23
  • Things around the web #5 https://t.co/qJKwcD1IH6 December 24, 2018 15:02
  • http://twitter.com/codingstill

Misc Links

  • George Liatsos Blog
  • Plethora Themes
  • C# / VB Converter
  • Higlabo: .NET library for mail, DropBox, Twitter & more

Connect with me

  • GitHub
  • LinkedIn
  • RSS
  • Twitter
  • Stack Overflow

Powered by Wordpress.