Problem
In my last set of tips, I discussed SMO at a basic level. In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO. Solution
As I discussed in my last tip, SMO provides utility classes for specific tasks. For backup and restore, it provides two main utility classes (Backup and Restore) which are available in Microsoft.SqlServer.Management.Smo namespace.
Before you start writing SMO code, you need to reference several assemblies which contain the SMO namespaces. For more details on these assemblies and how properly to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO). ExamplesC# Code Block 1 - Full Backups - This example shows how to issue full database backups with SMO. First, create an instance of the Backup class and set the associated properties. With the Action property you can specify the type of backup such as full, files or log backup. With the Database property specify the name of the database being backed up. The device is the backup media type such as disk or tape, so you need to add a device (one or more) to the Devices collection of backup instance. With the BackupSetName and BackupSetDescription properties you can specify the name and description for the backup set. Read more: MSSQLTips
In my last set of tips, I discussed SMO at a basic level. In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO. Solution
As I discussed in my last tip, SMO provides utility classes for specific tasks. For backup and restore, it provides two main utility classes (Backup and Restore) which are available in Microsoft.SqlServer.Management.Smo namespace.
Before you start writing SMO code, you need to reference several assemblies which contain the SMO namespaces. For more details on these assemblies and how properly to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO). ExamplesC# Code Block 1 - Full Backups - This example shows how to issue full database backups with SMO. First, create an instance of the Backup class and set the associated properties. With the Action property you can specify the type of backup such as full, files or log backup. With the Database property specify the name of the database being backed up. The device is the backup media type such as disk or tape, so you need to add a device (one or more) to the Devices collection of backup instance. With the BackupSetName and BackupSetDescription properties you can specify the name and description for the backup set. Read more: MSSQLTips