Introduction
The following article describes accessing a SQL Server 2005 database backup and restoring it programmatically using C#.NET 2.0 and SMO. This article provides coding samples to perform the task.
SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
The following namespaces can be used to access SQL Server 2005 programmatically:
Microsoft.SqlServer.management
Microsoft.SqlServer.Management.NotificationServices
Microsoft.SqlServer.Management.Smo
Microsoft.SqlServer.Management.Smo.Agent
Microsoft.SqlServer.Management.Smo.Broker
Microsoft.SqlServer.Management.Smo.Mail
Microsoft.SqlServer.Management.Smo.RegisteredServers
Microsoft.SqlServer.Management.Smo.Wmi
Microsoft.SqlServer.Management.Trace
Pre-Requisite
You need to reference the following namespaces before using this code:
- Microsoft.SqlServer.Management.Smo;
- Microsoft.SqlServer.Management.Common;
I used these two class to perform the backup and restore operations:
- Microsoft.SqlServer.Management.Smo.Backup
- Microsoft.SqlServer.Management.Smo.Restore
For more information, regarding these two class, check MSDN:
Backup database
public void BackupDatabase(String databaseName, String userName,
String password, String serverName, String destinationPath)
{
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, userName, 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;
sqlBackup.SqlBackup(sqlServer);
}
Restore Database
public void RestoreDatabase(String databaseName, String filePath,
String serverName, String userName, String password,
String dataFilePath, String logFilePath)
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = databaseName;
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = dataFilePath + databaseName + ".mdf";
String logFileLocation = logFilePath + databaseName + "_Log.ldf";
db = sqlServer.Databases[databaseName];
RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName+"_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.PercentCompleteNotification = 10;
sqlRestore.PercentComplete +=
new PercentCompleteEventHandler(sqlRestore_PercentComplete);
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
}
The portion of code uses full backup features. If you want, you can perform incremental and differential backup as well.
Updates: June 8, 2008
In order to use this code, your SQL Server authentication mode needs to be configured as Mixed Mode authentication. If you use Windows Authentication, then you need to modify the ServerConnection
:
SqlConnection sqlCon = new SqlConnection ("Data Source=Bappi; Integrated Security=True;");
ServerConnection connection = new ServerConnection(sqlCon);
Modify the ServerConnection
portion of both code samples using this code in order to use Windows Security.
Conclusion
As this code uses the SQL Server 2005 backup/restore facility, the code follows the rules of SQL Server 2005 while backing up or restoring databases.