Introduction
There
are situations, in web applications, when is need to create a backup and/or
restore for a database. For example, application administrator wants to “split”
the application, keeping in “main” application only stocks for last 5 years,
and to configure a new web application for period older than 5 years. It’s annoying
to create manually a backup for this period, so, we want to do a backup and a
restore programmatically, and, for “old” database, call some stored procedures
to delete the data older than 5 years, and, for “new” database, to delete data
more recent than 5 years.
How
to do that?
Let’s
implement this functionality in following example. We create a website application
based on framework 2.0, called BackupRestore, and add to solution a project of
type ClassLibrary, called Utils.
Add
class BackupRestore, and references Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo
to Utils project. These libraries come with classes used to implement this
functionality.
This
mechanism will function as follow: user can provide database name in interface;
the rest of the settings:
- name
of SQL Servers (source and destinations) which will host the source and destination
databases, with user and passwords
- folder
where database backup will be created
will
be loaded from a xml file, and the application will be created.
Let’s take a look to the listing of BackupRestore
class code:
using System;
using
System.Collections.Generic;
using
System.Collections;
using
System.Text;
using System.IO;
using
System.Xml;
using
System.Data;
using
System.Data.SqlClient;
using
Microsoft.SqlServer.Management.Smo;
using
Microsoft.SqlServer.Server;
using
Microsoft.SqlServer.Management.Common;
using
System.Configuration;
namespace Utils
{
public class BackupRestore
{
public
BackupRestore()
{
}
/// Load XML file with archive settings
private
static XmlDocument
LoadXMLSettings()
{
XmlDocument
xmlSettings = new XmlDocument();
string
xmlSettingsPath = ConfigurationManager.AppSettings["SettingsPath"];
try
{
xmlSettings.Load(xmlSettingsPath
+ "DBSettings.xml");
}
catch
{
throw
new ApplicationException("Settings file could not be opened");
}
return
xmlSettings;
}
/// read a key from archive configuration file
private
static string
GetXmlSetting(XmlDocument xmlSettings, string Key)
{
try
{
return
xmlSettings.DocumentElement.SelectSingleNode("archiveSetting[@key='"
+ Key + "']").Attributes["value"].Value;
}
catch
{
throw
new ApplicationException("Setting " + Key + " ndoes not exists in settings file");
}
}
/// Performs a backup for a database
public
static string
BackupDatabase(string DBName)
{
XmlDocument
xmlSettings = LoadXMLSettings();
string
serverName = GetXmlSetting(xmlSettings, "DbSourceServer");
string
databaseName = DBName;
string
loginName = GetXmlSetting(xmlSettings, "DbSourceServerSQLLogin");
string
password = GetXmlSetting(xmlSettings, "DbSourceServerSQLPassword");
string
destinationPath = GetXmlSetting(xmlSettings, "DbBackupPath");
destinationPath +=
databaseName + "_xxxxx" + ".bak";
Backup
sqlBackup = new Backup();
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription
= "ArchiveDataBase:" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "Archive";
sqlBackup.Database =
databaseName;
BackupDeviceItem
deviceItem = new BackupDeviceItem(destinationPath,
DeviceType.File);
ServerConnection
connection = new ServerConnection(serverName,
loginName, password);
Server
sqlServer = new Server(connection);
Database
db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError =
true;
sqlBackup.Devices.Add(deviceItem);
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
try
{
sqlBackup.SqlBackup(sqlServer);
}
catch(Exception ex)
{
throw
new ApplicationException("Error creating backup for database " +
databaseName + ": " + ex.Message);
}
return
"Database backup created successfully";
}
/// Performs a database restore
public
static string
RestoreDatabase(string DBName)
{
XmlDocument
xmlSettings = LoadXMLSettings();
string
serverName = GetXmlSetting(xmlSettings, "DbDestinationServer");
string
loginName = GetXmlSetting(xmlSettings, "DbDestinationServerSQLLogin");
string
password = GetXmlSetting(xmlSettings, "DbDestinationServerSQLPassword");
string
destinationPath = GetXmlSetting(xmlSettings, "DbRestorePath");
string
sourceDatabaseName = DBName;
string
sourcePath = GetXmlSetting(xmlSettings, "DbBackupPath");
//string
databaseName = sourceDatabaseName + "_xxxxx";
sourcePath +=
sourceDatabaseName + "_xxxxx" + ".bak";
Restore
sqlRestore = new Restore();
BackupDeviceItem
deviceItem = new BackupDeviceItem(sourcePath,
DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = sourceDatabaseName;
ServerConnection
connection = new ServerConnection(serverName,
loginName, password);
Server
sqlServer = new Server(connection);
Database
db = sqlServer.Databases[sourceDatabaseName];
sqlRestore.Action = RestoreActionType.Database;
String
DataFileLocation = destinationPath + sourceDatabaseName + ".mdf";
String
LogFileLocation = destinationPath + sourceDatabaseName + "_Log.ldf";
db =
sqlServer.Databases[sourceDatabaseName];
RelocateFile
rf = new RelocateFile(sourceDatabaseName,
DataFileLocation);
sqlRestore.RelocateFiles.Add(new RelocateFile(sourceDatabaseName,
DataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(sourceDatabaseName
+ "_log", LogFileLocation));
sqlRestore.ReplaceDatabase = true;
try
{
sqlRestore.SqlRestore(sqlServer);
db =
sqlServer.Databases[sourceDatabaseName];
db.SetOnline();
File.Delete(sourcePath);
}
catch(Exception ex)
{
return
"Error restoring database " +
sourceDatabaseName + ": " + ex.Message;
}
sqlServer.Refresh();
return
"Database restored successfully";
}
}
}
The
classes which do the job are Backup and Restore, from namespace Microsoft.SqlServer.Management,
which get the necessary information to get the job (backup or restore) as
properties, and then perform the specific operation.
In
interface, we have a textbox where we introduce name of virtual directory, and buttons
‘Backup database’ and ‘Restore database’. These buttons have associated the
code:
private void BackupDatabase()
{
string
DBName = txtDBName.Text;
if
(DBName != "")
{
string
Status = Utils.BackupRestore.BackupDatabase(DBName);
lblStatus.Text = Status;
}
}
private void RestoreDatabase()
{
string
DBName = txtDBName.Text;
if
(DBName != "")
{
string
Status = Utils.BackupRestore.RestoreDatabase(DBName);
lblStatus.Text = Status;
}
}
BackupRestoreSQLDB.rar (544.71 kb)
Subscribe