C #: backing up and recovering SQL Server databases

Backup (SQL-DMO)

BackupThe object defines the database or log backup operations of Microsoft SQL Server.

Remarks

UseBackupObject, you can:

• Back up an SQL Server database or database transaction log.
• Generate a statement that defines backup in transact-SQL backup.
• Listen to a backup operation and report the status to the user.

For SQL Server, a database delimits the largest backup unit. though cannot different database backup images can be maintained on any single medium, a backup cannot span more than a single database. by default, backup operations performed Med withBackupObject back up a complete database.

SQL Server can write a backup to one of four media types: disk, tape, named pipe, or a proprietary media called a backup device. SQL Server supports backup striping. a striped backup is one directed to more than a single device. when striped, a backup is written seconds ss the devices in equal chunks. striping is supported to a single media type only. that is, a backup can be written to two tape devices. A backup cannot be written half to a tape device and the other half to a disk.

At a minimum, you must supply values for a backup source and a backup target when usingBackupObject.DatabaseProperty specifies the backup operation source. SQL-DMO implements supported media types inBackupObject PropertiesFiles,Devices,Pipes, AndTapes. Use one media type property to specify the backup operation target.

To perform a complete database backup

1. Create a newBackupObject.
2. SetDatabaseProperty, naming the database backed up.
3. Set a media property to name the target device (s ).
4. CallSqlbackupMethod.

In specified installations, complete database backup is not a viable option.BackupObject offers access to a number of strategies that ensure data integrity by capturing a subset of the database image.

To back up a database transaction log

1. Create a newBackupObject
2. SetDatabaseProperty, naming the database backed up.
3. SetActionProperty to sqldmobackup_log.
4. Set a media property to name the target device (s ).
5. CallSqlbackupMethod.

To perform a differential backup

1. Create a newBackupObject
2. SetDatabaseProperty, naming the database backed up.
3. SetActionProperty to sqldmobackup_incremental.
4. Set a media property to name the target device (s ).
5. CallSqlbackupMethod.

To back up specific filegroups

1. Create a newBackupObject
2. SetDatabaseProperty, naming the database backed up.
3. SetDatabasefilegroupsProperty, naming the filegroup (s) providing backup source data.
4. Set a media property to name the target device (s ).
5. CallSqlbackupMethod.

To back up specific files

1. Create a newBackupObject
2. SetDatabaseProperty, naming the database backed up.
3. SetActionProperty to sqldmobackup_files.
4. SetDatabasefilesProperty, naming the file (s) providing backup source data.
5. Set a media property to name the target device (s ).
6. CallSqlbackupMethod.

Settings for any otherBackupObject properties are optional. Use the optional settings when conditions require extraordinary processing. For example,MedianameAndMediadegionProperties provide, primarily, data used to ensure media availability for tape devices and are applicable when the backup operation defined will initialize the media. for more information about property applicability and use, see individual property documentation.

 

/**/ ///   <Summary>
/// Back up MSSQL Server databases
///   </Summary>
///   <Param name = "DSN"> Server Name </Param>
///   <Param name = "uid"> User Name </Param>
///   <Param name = "PWD"> Password </Param>
///   <Param name = "DB"> Database Name </Param>
///   <Param name = "filepath"> Backup File Name </Param>
Public   Static   Void Mssql_backupdatabase ( String DSN, String UID, String PWD, String DB, String Filepath)
{
Sqldmo. Backup obackup =   New Sqldmo. backupclass ();
Sqldmo. sqlserver osqlserver =   New Sqldmo. sqlserverclass ();
Try
{
Osqlserver. loginsecure =   False ;
Osqlserver. Connect (DSN, uid, PWD );
Obackup. Action = Sqldmo. sqldmo_backup_type.sqldmobackup_database;
Obackup. Database = DB;
Obackup. Files = Filepath;
Obackup. backupsetname = DB;
Obackup. backupsetdescription =   String . Format ( " {0} backup " , DB );
Obackup. initialize =   True ;
Obackup. sqlbackup (osqlserver );
}
Catch
{
Throw;
}
Finally
{
Osqlserver. Disconnect ();
}
}

/**/ ///   <Summary>
/// Restore an MSSQL Server Database
///   </Summary>
///   <Param name = "DSN"> Server Name </Param>
///   <Param name = "uid"> User Name </Param>
///   <Param name = "PWD"> Password </Param>
///   <Param name = "DB"> Database Name </Param>
///   <Param name = "filepath"> Backup File Name </Param>
Public   Static   Void Mssql_restoredatabase ( String DSN, String UID, String PWD, String DB, String Filepath)
{
Sqldmo. Restore orestore =   New Sqldmo. restoreclass ();
Sqldmo. sqlserver osqlserver =   New Sqldmo. sqlserverclass ();
Try
{
Osqlserver. loginsecure =   False ;
Osqlserver. Connect (DSN, uid, PWD );
Orestore. Action = Sqldmo. sqldmo_restore_type.sqldmorestore_database;
Orestore. Database = DB;
Orestore. Files = Filepath;
Orestore. filenumber =   1 ;
Orestore. replacedatabase =   True ;
Orestore. sqlrestore (osqlserver );
}
Catch
{
Throw;
}
Finally
{
Osqlserver. Disconnect ();
}
}