Realizar de copias de seguridad y restaurar bases de datos y registros de transacciones
En SMO, la clase Backup y la clase Restore son clases de utilidad que proporcionan las herramientas para realizar las tareas concretas de copias de seguridad y restauración. Un objeto Backup representa una tarea de copia de seguridad concreta necesaria en lugar de un objeto MicrosoftSQL Server en la instancia del servidor.
Si se produce una pérdida de datos o se dañan, se debe restaurar la copia de seguridad, total o parcialmente. La restauración parcial utiliza la recopilación FileGroupCollection para segmentar los datos que se van a restaurar. Si la copia de seguridad es de un registro de transacciones, los datos se pueden desde un momento determinado utilizando la propiedad ToPointInTime del objeto Restore. Los datos también se pueden validar utilizando el método SqlVerify. El procedimiento de copia de seguridad recomendado es comprobar la integridad de la copia de seguridad haciendo una operación de restauración y comprobando los datos en la base de datos periódicamente.
Al igual que el objeto Backup, el objeto Restore no necesita crearse utilizando un método Create porque no representa ningún objeto en la instancia de SQL Server. El objeto Restore es un conjunto de propiedades y métodos que se utilizan para restaurar una base de datos.
Ejemplos
Para utilizar cualquier ejemplo de código que se proporcione, deberá elegir el entorno de programación, la plantilla de programación y el lenguaje de programación en los que crear su aplicación. Para obtener más información, vea Cómo crear un proyecto de Visual Basic SMO en Visual Studio .NET o Cómo crear un proyecto de Visual C# SMO en Visual Studio .NET.
Realizar copias de seguridad de bases de datos y registros de transacciones en Visual Basic
En este ejemplo de código se muestra cómo realizar una copia de seguridad de una base de datos existente en un archivo y cómo restaurarla.
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Store the current recovery model in a variable.
Dim recoverymod As Integer
recoverymod = db.DatabaseOptions.RecoveryModel
'Define a Backup object variable.
Dim bk As New Backup
'Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database
bk.BackupSetDescription = "Full backup of Adventureworks"
bk.BackupSetName = "AdventureWorks Backup"
bk.Database = "AdventureWorks"
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem("Test_Full_Backup1", DeviceType.File)
'Add the device to the Backup object.
bk.Devices.Add(bdi)
'Set the Incremental property to False to specify that this is a full database backup.
bk.Incremental = False
'Set the expiration date.
Dim backupdate As New Date
backupdate = New Date(2006, 10, 5)
bk.ExpirationDate = backupdate
'Specify that the log must be truncated after the backup is complete.
bk.LogTruncation = BackupTruncateLogType.Truncate
'Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv)
'Inform the user that the backup has been completed.
Console.WriteLine("Full Backup complete.")
'Remove the backup device from the Backup object.
bk.Devices.Remove(bdi)
'Make a change to the database, in this case, add a table called test_table.
Dim t As Table
t = New Table(db, "test_table")
Dim c As Column
c = New Column(t, "col", DataType.Int)
t.Columns.Add(c)
t.Create()
'Create another file device for the differential backup and add the Backup object.
Dim bdid As BackupDeviceItem
bdid = New BackupDeviceItem("Test_Differential_Backup1", DeviceType.File)
'Add the device to the Backup object.
bk.Devices.Add(bdid)
'Set the Incremental property to True for a differential backup.
bk.Incremental = True
'Run SqlBackup to perform the incremental database backup on the instance of SQL Server.
bk.SqlBackup(srv)
'Inform the user that the differential backup is complete.
Console.WriteLine("Differential Backup complete.")
'Remove the device from the Backup object.
bk.Devices.Remove(bdid)
'Delete the AdventureWorks database before restoring it.
srv.Databases("AdventureWorks").Drop()
'Define a Restore object variable.
Dim rs As Restore
rs = New Restore
'Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = True
'Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi)
'Specify the database name.
rs.Database = "AdventureWorks"
'Restore the full database backup with no recovery.
rs.SqlRestore(srv)
'Inform the user that the Full Database Restore is complete.
Console.WriteLine("Full Database Restore complete.")
'Remove the device from the Restore object.
rs.Devices.Remove(bdi)
'Set te NoRecovery property to False.
rs.NoRecovery = False
'Add the device that contains the differential backup to the Restore object.
rs.Devices.Add(bdid)
'Restore the differential database backup with recovery.
rs.SqlRestore(srv)
'Inform the user that the differential database restore is complete.
Console.WriteLine("Differential Database Restore complete.")
'Remove the device.
rs.Devices.Remove(bdid)
'Set the database recovery mode back to its original value.
srv.Databases("AdventureWorks").DatabaseOptions.RecoveryModel = recoverymod
'Drop the table that was added.
srv.Databases("AdventureWorks").Tables("test_table").Drop()
srv.Databases("AdventureWorks").Alter()
'Remove the backup files from the hard disk.
My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test_Full_Backup1")
My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test_Differential_Backup1")
Realizar copias de seguridad de bases de datos y registros de transacciones en Visual C#
En este ejemplo de código se muestra cómo realizar una copia de seguridad de una base de datos existente en un archivo y cómo restaurarla.
//Connect to the local, default instance of SQL Server.
{
Server srv = default(Server);
srv = new Server();
//Reference the AdventureWorks database.
Database db = default(Database);
db = srv.Databases("AdventureWorks");
//Store the current recovery model in a variable.
int recoverymod = 0;
recoverymod = db.DatabaseOptions.RecoveryModel;
//Define a Backup object variable.
Backup bk = new Backup();
//Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database;
bk.BackupSetDescription = "Full backup of Adventureworks";
bk.BackupSetName = "AdventureWorks Backup";
bk.Database = "AdventureWorks";
//Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem("Test_Full_Backup1", DeviceType.File);
//Add the device to the Backup object.
bk.Devices.Add(bdi);
//Set the Incremental property to False to specify that this is a full database backup.
bk.Incremental = false;
//Set the expiration date.
System.DateTime backupdate = new System.DateTime();
backupdate = new System.DateTime(2006, 10, 5);
bk.ExpirationDate = backupdate;
//Specify that the log must be truncated after the backup is complete.
bk.LogTruncation = BackupTruncateLogType.Truncate;
//Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv);
//Inform the user that the backup has been completed.
Console.WriteLine("Full Backup complete.");
//Remove the backup device from the Backup object.
bk.Devices.Remove(bdi);
//Make a change to the database, in this case, add a table called test_table.
Table t = default(Table);
t = new Table(db, "test_table");
Column c = default(Column);
c = new Column(t, "col", DataType.Int);
t.Columns.Add(c);
t.Create();
//Create another file device for the differential backup and add the Backup object.
BackupDeviceItem bdid = default(BackupDeviceItem);
bdid = new BackupDeviceItem("Test_Differential_Backup1", DeviceType.File);
//Add the device to the Backup object.
bk.Devices.Add(bdid);
//Set the Incremental property to True for a differential backup.
bk.Incremental = true;
//Run SqlBackup to perform the incremental database backup on the instance of SQL Server.
bk.SqlBackup(srv);
//Inform the user that the differential backup is complete.
Console.WriteLine("Differential Backup complete.");
//Remove the device from the Backup object.
bk.Devices.Remove(bdid);
//Delete the AdventureWorks database before restoring it.
srv.Databases("AdventureWorks").Drop();
//Define a Restore object variable.
Restore rs = default(Restore);
rs = new Restore();
//Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = true;
//Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi);
//Specify the database name.
rs.Database = "AdventureWorks";
//Restore the full database backup with no recovery.
rs.SqlRestore(srv);
//Inform the user that the Full Database Restore is complete.
Console.WriteLine("Full Database Restore complete.");
//Remove the device from the Restore object.
rs.Devices.Remove(bdi);
//Set te NoRecovery property to False.
rs.NoRecovery = false;
//Add the device that contains the differential backup to the Restore object.
rs.Devices.Add(bdid);
//Restore the differential database backup with recovery.
rs.SqlRestore(srv);
//Inform the user that the differential database restore is complete.
Console.WriteLine("Differential Database Restore complete.");
//Remove the device.
rs.Devices.Remove(bdid);
//Set the database recovery mode back to its original value.
srv.Databases("AdventureWorks").DatabaseOptions.RecoveryModel = recoverymod;
//Drop the table that was added.
srv.Databases("AdventureWorks").Tables("test_table").Drop();
srv.Databases("AdventureWorks").Alter();
//Remove the backup files from the hard disk.
My.Computer.FileSystem.DeleteFile("C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\Test_Full_Backup1");
My.Computer.FileSystem.DeleteFile("C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\Test_Differential_Backup1");
}
Ejecutar comprobaciones de integridad de base de datos en Visual Basic
SQL Server proporciona una comprobación de integridad de los datos. En este ejemplo de código se ejecuta una comprobación de coherencia en la base de datos especificada. En este ejemplo, CheckTables se utiliza, pero se pueden utilizar CheckAllocations, CheckCatalog o CheckIdentityValues de igual forma.
[!NOTA]
El objeto StringCollection requiere una referencia al espacio de nombres mediante el uso de la instrucción imports System.Collections.Specialized.
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Note, to use the StringCollection type the System.Collections.Specialized system namespace must be included in the imports statements.
Dim sc As StringCollection
'Run the CheckTables method and display the results from the returned StringCollection variable.
sc = db.CheckTables(RepairType.None)
Dim c As Integer
For c = 0 To sc.Count - 1
Console.WriteLine(sc.Item(c))
Next
Ejecutar comprobaciones de integridad de base de datos en Visual C#
SQL Server proporciona una comprobación de integridad de los datos. En este ejemplo de código se realiza una comprobación de coherencia en la base de datos especificada. En este ejemplo, CheckTables se utiliza, pero se pueden utilizar CheckAllocations, CheckCatalog o CheckIdentityValues de igual forma.
[!NOTA]
El objeto StringCollection requiere una referencia al espacio de nombres utilizando la instrucción imports System.Collections.Specialized.
//Connect to the local, default instance of SQL Server.
{
Server srv = default(Server);
srv = new Server();
//Reference the AdventureWorks database.
Database db = default(Database);
db = srv.Databases("AdventureWorks");
//Note, to use the StringCollection type the System.Collections.Specialized system namespace must be included in the imports statements.
StringCollection sc = default(StringCollection);
//Run the CheckTables method and display the results from the returned StringCollection variable.
sc = db.CheckTables(RepairType.None);
int c = 0;
for (c = 0; c <= sc.Count - 1; c++) {
Console.WriteLine(sc.Item(c));
}
}