Click here to Skip to main content
Click here to Skip to main content

Using SMO for Backup, Restore and Security Purposes

, 29 Aug 2007
Rate this:
Please Sign up or sign in to vote.
This article shows some samples using SMO with SQL Server 2005 to perform some administrative tasks.

Introduction

Finally, I have decided to post my first article here. So I expect some feedback from you.

Thank you in advance!

I will try to explain the ways in which you can use Server Management Objects to perform some administrative tasks with SQL Server 2005 using C#.

I use some of this code for automation of the deployment of our projects. And may be I will post another article on this subject (Deployment Automation).

The Code

I will list the methods with some brief explanation. I am not pretending about Best Practices in Code Design.

To use this code you must have references to the following assemblies:

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.SqlEnum

I use a method GetServer(). This method creates an instance of SMO.Server object so I can use it to perform some operations on that SQL Server 2005.

private static Server GetServer()
{
ServerConnection conn = new ServerConnection(serverName, userName, password);
Server myServer = new Server(conn);
return myServer;
}

BackupDatabase method with the relevant event handlers:

static void BackupDataBase(string databaseName, string destinationPath)
{
WriteToLogAndConsole("Backup the {0} database!", databaseName);
Server myServer = GetServer();
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = databaseName;
destinationPath = System.IO.Path.Combine(destinationPath, databaseName + ".bak");
backup.Devices.Add(new BackupDeviceItem(destinationPath, DeviceType.File));
backup.Initialize = true;
backup.Checksum = true;
backup.ContinueAfterError = true;
backup.Incremental = false;
backup.LogTruncation = BackupTruncateLogType.Truncate;
backup.PercentComplete += new PercentCompleteEventHandler(backup_PercentComplete);
backup.Complete += 
    new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler
    (backup_Complete);
// Perform backup.
backup.SqlBackup(myServer);
}

//The event handlers
static void backup_Complete
    (object sender, Microsoft.SqlServer.Management.Common.ServerMessageEventArgs e)
{
WriteToLogAndConsole(e.ToString() + "% Complete");
}
static void backup_PercentComplete(object sender, PercentCompleteEventArgs e)
{
WriteToLogAndConsole(e.Percent.ToString() + "% Complete");
}
//NOTE: WriteToLogAndCosole is some method for logging I have implemented 

RestoreDatabase() method with the relevant event handlers:

static void RestoreDataBase(string BackupFilePath, 
            string destinationDatabaseName, 
            string DatabaseFolder, 
            string DatabaseFileName,
            string DatabaseLogFileName)
{
Server myServer = GetServer();
Restore myRestore = new Restore();
myRestore.Database = destinationDatabaseName;
Database currentDb = myServer.Databases[destinationDatabaseName];
if (currentDb != null)
    myServer.KillAllProcesses(destinationDatabaseName);
myRestore.Devices.AddDevice(BackupFilePath, DeviceType.File);
string DataFileLocation = DatabaseFolder + "\\" + destinationDatabaseName + ".mdf";
string LogFileLocation = DatabaseFolder + "\\" + destinationDatabaseName + "_log.ldf";
myRestore.RelocateFiles.Add(new RelocateFile(DatabaseFileName, DataFileLocation));
myRestore.RelocateFiles.Add(new RelocateFile(DatabaseLogFileName, LogFileLocation));
myRestore.ReplaceDatabase = true;
myRestore.PercentCompleteNotification = 10;
myRestore.PercentComplete += 
    new PercentCompleteEventHandler(myRestore_PercentComplete);
myRestore.Complete += new ServerMessageEventHandler(myRestore_Complete);
WriteToLogAndConsole("Restoring:{0}", destinationDatabaseName);
myRestore.SqlRestore(myServer);
currentDb = myServer.Databases[destinationDatabaseName];
currentDb.SetOnline();
}
static void myRestore_Complete
    (object sender, Microsoft.SqlServer.Management.Common.ServerMessageEventArgs e)
{
WriteToLogAndConsole(e.ToString() + " Complete");
}
static void myRestore_PercentComplete(object sender, PercentCompleteEventArgs e)
{
WriteToLogAndConsole(e.Percent.ToString() + "% Complete");
} 

CreateLogin() Method

static void CreateLogin
    (string sqlLoginName,string sqlLoginPassword,string databaseName)
{
myServer=GetServer()
Login newLogin = myServer.Logins[sqlLogin];
if (newLogin != null)
    newLogin.Drop();
newLogin = new Login(myServer, sqlLogin);
newLogin.PasswordPolicyEnforced = false;
newLogin.LoginType = LoginType.SqlLogin;
newLogin.Create(sqlLoginPassword);
//Create DatabaseUser
DatabaseMapping mapping = 
    new DatabaseMapping(newLogin.Name, MainDbName, newLogin.Name);
Database currentDb = myServer.Databases[databaseName];
User dbUser = new User(currentDb, newLogin.Name);
dbUser.Login = sqlLogin;
dbUser.Create();
dbUser.AddToRole("db_owner");
}

Conclusion

Again, this is my first article. So I am sorry for the poor formatting and explanations. I just wanted to show you some of the code you can use with SMO and that's it. I will appreciate any feedback.

Expect more from me SOON!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

niki_mbr
Web Developer
Bulgaria Bulgaria
4+ Years in Software Design and Development.

Comments and Discussions

 
QuestionGetting error in login creation Pinmembermaushmi15-May-13 22:40 
AnswerRe: Getting error in login creation Pinmemberniki_mbr15-May-13 22:58 
QuestionBackup Progress Problem Pinmemberjia yu20-Aug-09 21:25 
AnswerRe: Backup Progress Problem Pinmemberniki_mbr20-Aug-09 22:03 
GeneralRe: Backup Progress Problem Pinmemberjia yu21-Aug-09 4:30 
GeneralRe: Backup Progress Problem Pinmemberniki_mbr21-Aug-09 18:10 
AnswerRe: Backup Progress Problem Pinmemberpfxp23-Aug-09 3:14 
GeneralTanks Pinmemberh_r_sh6-Apr-09 2:48 
Generalsmo Pinmemberppro24-Nov-08 7:21 
GeneralThanks! PinmemberChris Khoo23-Mar-07 18:11 
QuestionProposed addition to Article Pinmemberyanocano20067-Feb-07 5:32 
AnswerRe: Proposed addition to Article Pinmemberyanocano20067-Feb-07 5:36 
GeneralThanks Man! Pinmemberryanturner.com6-Dec-06 7:02 
GeneralHi Niki, I am also finding the similar solution for my requirement. (please reply) Pinmembersharad1615-Sep-06 7:32 
GeneralRe: Hi Niki, I am also finding the similar solution for my requirement. (please reply) Pinmemberniki_mbr17-Sep-06 18:47 
GeneralKool stuff... Pinmemberdirn.intg28-Aug-06 23:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 29 Aug 2007
Article Copyright 2006 by niki_mbr
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid