문서를 영문으로 보려면 영문 확인란을 선택하세요. 마우스 포인터를 텍스트 위로 이동시켜 팝업 창에서 영문 텍스트를 표시할 수도 있습니다.
번역
영문

데이터베이스 및 트랜잭션 로그 백업 및 복원

SMO에서 Backup 클래스와 Restore 클래스는 특정 백업 및 복원 태스크를 수행하는 도구를 제공하는 유틸리티 클래스입니다. Backup 개체는 서버 인스턴스의 Microsoft SQL Server 개체 대신에 필요한 특정 백업 태스크를 나타냅니다.

데이터 손실이나 손상이 발생하면 완전히 또는 부분적으로 백업을 복원해야 합니다. 부분 복원에서는 복원할 데이터를 분할하기 위해 FileGroupCollection 컬렉션을 사용합니다. 백업에 트랜잭션 로그가 있으면 Restore 개체의 ToPointInTime 속성을 사용하여 특정 시점까지 데이터를 복원할 수 있습니다. 또한 SqlVerify 메서드를 사용하여 데이터의 유효성을 확인할 수 있습니다. 권장되는 백업 절차는, 복원 작업을 수행하고 정기적으로 데이터베이스의 데이터를 검사하여 백업 무결성을 확인하는 것입니다.

Backup 개체와 마찬가지로 Restore 개체도 SQL Server 인스턴스에 개체를 표시하지 않기 때문에 Create 메서드를 사용하여 만들 필요가 없습니다. Restore 개체는 데이터베이스 복원에 사용되는 일련의 속성과 메서드입니다.

제공된 코드 예제를 사용하려면 응용 프로그램을 만들 프로그래밍 환경, 프로그래밍 템플릿 및 프로그래밍 언어를 선택해야 합니다. 자세한 내용은 Visual Studio .NET에서 Visual Basic SMO 프로젝트 만들기 또는 Visual Studio .NET에서 Visual C# SMO 프로젝트 만들기를 참조하십시오.

이 코드 예제는 기존 데이터베이스를 파일로 백업한 다음 복원하는 방법을 보여 줍니다.

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

이 코드 예제는 기존 데이터베이스를 파일로 백업한 다음 복원하는 방법을 보여 줍니다.

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");
   }
}

이 코드 예제는 기존 데이터베이스를 파일로 백업한 다음 복원하는 방법을 보여 줍니다.

#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"

SQL Server는 데이터 무결성 검사를 제공합니다. 이 코드 예제는 지정된 데이터베이스에서 데이터베이스 일관성 형식 검사를 실행합니다. 이 예에서는 CheckTables가 사용되지만 CheckAllocations, CheckCatalog 또는 CheckIdentityValues를 같은 방식으로 사용할 수 있습니다.

참고 참고

StringCollection 개체에는 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

SQL Server는 데이터 무결성 검사를 제공합니다. 이 코드 예제는 지정된 데이터베이스에서 데이터베이스 일관성 형식 검사를 실행합니다. 이 예에서는 CheckTables가 사용되지만 CheckAllocations, CheckCatalog 또는 CheckIdentityValues를 같은 방식으로 사용할 수 있습니다.

참고 참고

StringCollection 개체에는 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);
      }
   }
}

SQL Server는 데이터 무결성 검사를 제공합니다. 이 코드 예제는 지정된 데이터베이스에서 데이터베이스 일관성 형식 검사를 실행합니다. 이 예에서는 CheckTables가 사용되지만 CheckAllocations, CheckCatalog 또는 CheckIdentityValues를 같은 방식으로 사용할 수 있습니다.

참고 참고

StringCollection 개체에는 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
 }

커뮤니티 추가 항목

추가
표시: