Fazendo backup e restaurando bancos de dados e logs de transações

Artigo
03/20/2013

No SMO, as classes Backup e Restore são classes de utilitário que fornecem as ferramentas para a execução de tarefas específicas de backup e restauração. Um objeto Backup representa uma tarefa específica de backup obrigatória, e não um objeto MicrosoftSQL Server na instância do servidor.

Em caso de perda ou dano de dados, o backup deve ser restaurado, parcial ou totalmente. A restauração parcial usa a coleção FileGroupCollection para segmentar os dados a serem restaurados. Se o backup for de um log de transações, os dados poderão ser restaurados até um momento determinado através da propriedade ToPointInTime do objeto Restore. Os dados também podem ser validados usando o método SqlVerify. O procedimento de backup recomendado é verificar a integridade do backup executando uma operação de restauração e verificando os dados no banco de dados regularmente.

Assim como o objeto Backup, o objeto Restore não precisa ser criado através de um método Create pois ele não representa objetos na instância do SQL Server. O objeto Restore é um conjunto de propriedades e métodos usados para restaurar um banco de dados.

Exemplos

Para usar qualquer exemplo de código fornecido, será necessário escolher o ambiente de programação, o modelo de programação e a linguagem de programação para criar o aplicativo. Para obter mais informações, consulte Criar um projeto SMO do Visual Basic no Visual Studio .NET ou Criar um projeto SMO do Visual C# no Visual Studio .NET.

Fazendo backup de bancos de dados e de logs de transações no Visual Basic

Este exemplo de código mostra como fazer backup de um banco de dados existente em um arquivo e como restaurá-lo.

Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.VisualBasic.MyServices

Module SMO_VBBackup3

    Sub Main()
        'Connect to the local, default instance of SQL Server.
        Dim srv As Server
        srv = New Server

        'Reference the AdventureWorks2008R2 database.
        Dim db As Database
        db = srv.Databases("AdventureWorks2008R2")

        '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 AdventureWorks2008R2"
        bk.BackupSetName = "AdventureWorks 2008R2 Backup"
        bk.Database = "AdventureWorks2008R2"

        '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 AdventureWorks2008R2 database before restoring it.
        srv.Databases("AdventureWorks2008R2").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 = "AdventureWorks2008R2"

        '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("AdventureWorks2008R2").DatabaseOptions.RecoveryModel = recoverymod

        'Drop the table that was added.
        srv.Databases("AdventureWorks2008R2").Tables("test_table").Drop()
        srv.Databases("AdventureWorks2008R2").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")
    End Sub
End Module

Fazendo backup de bancos de dados e de logs de transações no Visual C#

Este exemplo de código mostra como fazer backup de um banco de dados existente em um arquivo e como restaurá-lo.

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

class A {
   public static void Main() {
      // Connect to the local, default instance of SQL Server. 
      Server srv = new Server();
      // Reference the AdventureWorks2012 database. 
      Database db = default(Database);
      db = srv.Databases["AdventureWorks2012"];

      // Store the current recovery model in a variable. 
      int recoverymod;
      recoverymod = (int)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 Adventureworks2012";
      bk.BackupSetName = "AdventureWorks2012 Backup";
      bk.Database = "AdventureWorks2012";

      // 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. 
      System.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. 
      System.Console.WriteLine("Differential Backup complete.");

      // Remove the device from the Backup object. 
      bk.Devices.Remove(bdid);

      // Delete the AdventureWorks2012 database before restoring it
      // db.Drop();

      // Define a Restore object variable.
      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 = "AdventureWorks2012";

      // 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.");

      // reacquire a reference to the database
      db = srv.Databases["AdventureWorks2012"];

      // Remove the device from the Restore object.
      rs.Devices.Remove(bdi);

      // Set the 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. 
      System.Console.WriteLine("Differential Database Restore complete.");

      // Remove the device. 
      rs.Devices.Remove(bdid);

      // Set the database recovery mode back to its original value.
      db.RecoveryModel = (RecoveryModel)recoverymod;

      // Drop the table that was added. 
      db.Tables["test_table"].Drop();
      db.Alter();

      // Remove the backup files from the hard disk.
      // This location is dependent on the installation of SQL Server
      System.IO.File.Delete("C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Backup\\Test_Full_Backup1");
      System.IO.File.Delete("C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Backup\\Test_Differential_Backup1");
   }
}

Fazendo backup de bancos de dados e de logs de transações no PowerShell

Este exemplo de código mostra como fazer backup de um banco de dados existente em um arquivo e como restaurá-lo.

#Backing up and restoring a Database from PowerShell

#Connect to the local, default instance of SQL Server.

#Get a server object which corresponds to the default instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server

#Reference the AdventureWorks database.
$db = $srv.Databases["AdventureWorks"]

#Store the current recovery model in a variable.
$recoverymod = $db.DatabaseOptions.RecoveryModel

#Create a Backup object
$bk = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Backup

#set to backup the database
$bk.Action = [Microsoft.SqlServer.Management.SMO.BackupActionType]::Database

#Set back up properties
$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.
$dt = [Microsoft.SqlServer.Management.SMO.DeviceType]::File
$bdi = New-Object -TypeName Microsoft.SqlServer.Management.SMO.BackupDeviceItem `
-argumentlist "Test_FullBackup1", $dt

#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.
$bk.ExpirationDate = get-date "10/05/2006"

#Specify that the log must be truncated after the backup is complete.
$bk.LogTruncation = [Microsoft.SqlServer.Management.SMO.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.
"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.
$t = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table -argumentlist $db, "test_table"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::int
$c = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $t, "col", $type     
$t.Columns.Add($c)
$t.Create()

#Create another file device for the differential backup and add the Backup object.
# $dt is file backup device
$bdid = New-Object -TypeName Microsoft.SqlServer.Management.SMO.BackupDeviceItem `
-argumentlist "Test_DifferentialBackup1", $dt
#Add this device to the backup set
$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.
"Differential Backup complete."

#Remove the device from the Backup object.
$bk.Devices.Remove($bdid)

#Delete the AdventureWorks database before restoring it.
$db.Drop()

#Define a Restore object variable.
$rs = New-Object -TypeName Microsoft.SqlServer.Management.SMO.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.
"Full Database Restore complete."

#Remove the device from the Restore object.
$rs.Devices.Remove($bdi)

#Set the 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.
"Differential Database Restore complete."
       
#Remove the device.
$rs.Devices.Remove($bdid)

#Set the database recovery mode back to its original value.
$db = $srv.Databases["AdventureWorks"]
$db.DatabaseOptions.RecoveryModel = $recoverymod

#Drop the table that was added.
$db.Tables["test_table"].Drop()
$db.Alter()

#Delete the backup files - the exact location depends on your installation
del "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Test_FullBackup1"
del "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Test_DifferentialBackup1"

Executando verificações da integridade do banco de dados no Visual Basic

O SQL Server fornece verificação de integridade de dados. Este exemplo de código executa uma verificação do tipo de consistência no banco de dados especificado. Nesse exemplo, CheckTables é usado, mas CheckAllocations, CheckCatalog ou CheckIdentityValues pode ser usado da mesma forma.

Observação
O objeto StringCollection exige uma referência ao namespace usando a instrução imports System.Collections.Specialized.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Specialized
Module S
   Sub Main()
      'Connect to the local, default instance of SQL Server.
      Dim srv As Server
      srv = New Server
      'Reference the AdventureWorks2008R2 database.
      Dim db As Database
      db = srv.Databases("AdventureWorks2008R2")
      '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
   End Sub
End Module

Executando verificações da integridade do banco de dados no Visual C#

Observação
O objeto StringCollection exige uma referência ao namespace usando a instrução imports System.Collections.Specialized.

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;

class A {
   public static void Main() {
      // Connect to the local, default instance of SQL Server. 
      Server srv = new Server();

      // Reference the AdventureWorks2012 database.           
      Database db = srv.Databases["AdventureWorks2012"];

      // Note, to use the StringCollection type the System.Collections.Specialized system namespace must be included in the imports statements. 
      System.Collections.Specialized.StringCollection sc;

      // Run the CheckTables method and display the results from the returned StringCollection variable. 
      sc = db.CheckTables(RepairType.None);

      foreach (string c in sc) {
         Console.WriteLine(c);
      }
   }
}

Executando verificações da integridade do banco de dados no PowerShell

Observação
O objeto StringCollection exige uma referência ao namespace usando a instrução imports System.Collections.Specialized.

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2012
CD \sql\localhost\default\databases
$db = get-item Adventureworks2012

$sc = $db.CheckTables([Microsoft.SqlServer.Management.SMO.RepairType]::None)
foreach ($c in $sc)
{
    $c
 }

Partilhar via

Fazendo backup e restaurando bancos de dados e logs de transações

Exemplos

Fazendo backup de bancos de dados e de logs de transações no Visual Basic

Fazendo backup de bancos de dados e de logs de transações no Visual C#

Fazendo backup de bancos de dados e de logs de transações no PowerShell

Executando verificações da integridade do banco de dados no Visual Basic

Executando verificações da integridade do banco de dados no Visual C#

Executando verificações da integridade do banco de dados no PowerShell

Recursos adicionais