Click here to Skip to main content
12,247,341 members (55,434 online)
Click here to Skip to main content
Add your own
alternative version

Stats

61.3K views
35 bookmarked
Posted

Using SMO for Backup, Restore and Security Purposes

, 29 Aug 2007 CPOL
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)

Share

About the Author

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

You may also be interested in...

Comments and Discussions

 
QuestionGetting error in login creation Pin
maushmi15-May-13 23:40
membermaushmi15-May-13 23:40 
AnswerRe: Getting error in login creation Pin
niki_mbr15-May-13 23:58
memberniki_mbr15-May-13 23:58 
QuestionBackup Progress Problem Pin
jia yu20-Aug-09 22:25
memberjia yu20-Aug-09 22:25 
AnswerRe: Backup Progress Problem Pin
niki_mbr20-Aug-09 23:03
memberniki_mbr20-Aug-09 23:03 
GeneralRe: Backup Progress Problem Pin
jia yu21-Aug-09 5:30
memberjia yu21-Aug-09 5:30 
GeneralRe: Backup Progress Problem Pin
niki_mbr21-Aug-09 19:10
memberniki_mbr21-Aug-09 19:10 
AnswerRe: Backup Progress Problem Pin
pfxp23-Aug-09 4:14
memberpfxp23-Aug-09 4:14 
GeneralTanks Pin
h_r_sh6-Apr-09 3:48
memberh_r_sh6-Apr-09 3:48 
Generalsmo Pin
ppro24-Nov-08 8:21
memberppro24-Nov-08 8:21 
GeneralThanks! Pin
Chris Khoo23-Mar-07 19:11
memberChris Khoo23-Mar-07 19:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 29 Aug 2007
Article Copyright 2006 by niki_mbr
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid