dcsimg

Backing Up and Restoring Your SQL Databases Through .NET

WEBINAR:
On-Demand

Desktop-as-a-Service Designed for Any Cloud ? Nutanix Frame


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).

References
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.



About the Author

Hannes DuPreez

Hannes du Preez is a self-taught developer who started learning to program in the days of QBasic. He has written several articles over the years detailing his programming quests and adventures. .NET is his second love, just after his wife and kid. He has always been an avid supporter of .NET since the beginning and is an expert in VB and C#. He was given the Microsoft Most Valuable Professional Award for .NET (2008–2017). He has worked as a moderator and an article reviewer on online forums and currently works as a C# developer and writes articles for CodeGuru.com, Developer.com, DevX.com, and the Database journal.
His first book Visual Studio 2019 In Depth is currently on sale on Amazon and Bpb Publications.

You could reach him at: ojdupreez1978[at]gmail[dot]com

Related Articles

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date