Using SMO for Backup, Restore and Security Purposes

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!