Backing Up and Restoring Your SQL Databases Through .NET

Introduction

A very important part of every developer’s daily routine is to ensure that the SQL Server’s backups were done properly. In a previous article, I mentioned that data is the most important commodity. What happens when you lose crucial data? I am sure many of you know. Sometimes, it happens that a backup does not run. Sometimes, it happens that you lose important information. Your job as a developer is to ensure that that doesn’t happen, but when it does, your data is as safe as possible.

In this article, you will learn how to back up and restore SQL databases with your .NET code.

Practical

Open Visual Studio and create a Windows Forms project in either C# or VB.NET. Add two buttons to your Form. Label one button ‘Backup’ and the other button ‘Restore’.

Before you start coding, you need to set references to some SQL Server libraries. These libraries make it easy for us to do any SQL object manipulation. Add References by following these steps:

1. Click Project.
2. Click Add Reference.
3. Click Browse.
4. Add a Reference to the following files:
• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.SmoExtended.dll
  These files are usually located in C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies and are installed with SQL Server (see Figure 1).

Figure 1: References

Code

Add the following namespaces to your project:

C#

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Windows.Forms;

VB.NET

Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo

The Microsoft.SqlServer.Management.Smo namespace contains classes that represent SQL instances, databases, tables, stored procedures, and views. Microsoft.SqlServer.Management.Common contains the classes which enable you to make a connection to a SQL Server instance and execute SQL statements. Add the following code to back up a database.

C#

   private void button1_Click(object sender, EventArgs e)
   {

      ServerConnection conBackup = new
         ServerConnection(@"Connection");

      Server ServerBackup = new Server(conBackup);
      Backup BackupObject = new Backup();

      BackupObject.Action = BackupActionType.Database;
      BackupObject.Database = "DatabaseName";

      BackupDeviceItem destination = new BackupDeviceItem("Path",
         DeviceType.File);

      BackupObject.Devices.Add(destination);
      BackupObject.SqlBackup(ServerBackup);

      conBackup.Disconnect();

}

VB.NET

   Private Sub button1_Click(sender As Object, e As EventArgs) _
         Handles button1.Click

      Dim conBackup As ServerConnection = New _
         ServerConnection("Connection")
      Dim ServerBackup As Server = New Server(conBackup)
      Dim BackupObject As Backup = New Backup()

      BackupObject.Action = BackupActionType.Database
      BackupObject.Database = "DatabaseName"

      Dim destination As BackupDeviceItem = New _
         BackupDeviceItem("Path", DeviceType.File)

      BackupObject.Devices.Add(destination)
      BackupObject.SqlBackup(ServerBackup)

      conBackup.Disconnect()

   End Sub

Quite simple. A Connection object gets created, and then a Backup object. You specify the Backup object’s settings, such as Database name and what you would like to back up. You specify where you want the Backup file to be stored. Remember that the Backup file must have a .Bak extension. Finally, you make use of SqlBackup to do the physical backup of your database.

Add the following code to Restore a database.

C#

   private void button2_Click(object sender, EventArgs e)
   {

      ServerConnection conRestore = new
         ServerConnection(@"Connection");

      Server ServerRestore = new Server(conRestore);
      Restore RestoreObject = new Restore();

      RestoreObject.Action = RestoreActionType.Database;
      RestoreObject.Database = "DataBaseName";

      BackupDeviceItem source = new BackupDeviceItem("Path",
         DeviceType.File);

      RestoreObject.Devices.Add(source);
      RestoreObject.ReplaceDatabase = true;

      RestoreObject.SqlRestore(ServerRestore);

   }

VB.NET

   Private Sub button2_Click(sender As Object, e As EventArgs) _
         Handles button2.Click

      Dim conRestore As ServerConnection = New _
         ServerConnection("Connection")

      Dim ServerRestore As Server = New Server(conRestore)
      Dim RestoreObject As Restore = New Restore()

      RestoreObject.Action = RestoreActionType.Database
      RestoreObject.Database = "DataBaseName"

      Dim source As BackupDeviceItem = New _
         BackupDeviceItem("Path", DeviceType.File)

      RestoreObject.Devices.Add(source)
      RestoreObject.ReplaceDatabase = True

      RestoreObject.SqlRestore(ServerRestore)

   End Sub

This is the same principle as the Backup procedure earlier. You create a Connection object, and then a Restore object. You specify what you want to restore, and then use SqlRestore to Restore the database.

Conclusion

This article has shown you how simple it is to Back up and Restore databases using SQL libraries. The onus is now on you to explore the libraries further.