Yedekleme ve geri yükleme veritabanları ve hareket günlükleri

smo, de Backup sınıfı ve Restore sınıfı olan yedekleme ve geri yükleme belirli görevleri yerine getirmek için gereken araçları sağlar yardımcı sınıflar.A Backup object represents a specific backup task that is required instead of a Microsoft SQL Server object on the server instance.

Veri kaybı veya bozulması meydana gelirse, yedek, tamamen veya kısmen geri yüklenmesi gerekir.Kısmi geri yükleme kullanır FileGroupCollection koleksiyon geri yüklenmesi veri segmentlere ayırmak için.İşlem günlüğünü yedek ise, veri belirli bir noktaya kadar geri yüklenebilir saat kullanarak ToPointInTime özellik Restore nesne.Verileri kullanarak aynı zamanda doğrulanabilir SqlVerify yöntem.Önerilen yedek yordamını gözden geçirmektir bütünlük yedek geri yükleme işlemi yapmak ve verileri veritabanında düzenli olarak denetleniyor.

Gibi Backup nesnesi, Restore nesne ihtiyaç kullanılarak oluşturulacak bir Create yöntem herhangi bir nesne örnek gösterdiği için SQL Server.The Restore object is a set of properties and methods used to restore a database.

Örnekler

Sunulan kod örneklerinden herhangi birini kullanmak için, programlama ortamını, programlama şablonunu ve uygulamanızı oluşturacağınız programlama dilini seçmeniz gerekecektir.Daha fazla bilgi için bkz: Nasıl yapılır: Visual Studio'da Visual Basic smo proje oluşturun.NET veya Nasıl yapılır: Bir Visual C# smo Project Visual Studio'da oluşturun.NET.

Visual Basic'te veritabanları ve hareket yedekleme günlükleri

Bu kod örneği nasıl varolan bir veritabanını bir dosyaya yedeklemek ve geri yükleme yüklemek nasıl gösterir.

'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")

Veritabanları ve hareket yedekleme Visual C# [NULL]'ta oturum

Bu kod örneği nasıl varolan bir veritabanını bir dosyaya yedeklemek ve geri yükleme yüklemek nasıl gösterir.

{
            //Connect to the local, default instance of SQL Server. 
            Server srv = new Server();
            //Reference the AdventureWorks2008R2 database. 
            Database db = default(Database);
            db = srv.Databases["AdventureWorks2008R2"];
          
            //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 Adventureworks2008R2";
            bk.BackupSetName = "AdventureWorks2008R2 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. 
            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 AdventureWorks2008R2 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 = "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.");

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

            //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. 
            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
            File.Delete("C:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\Test_Full_Backup1");
        File.Delete("C:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\Test_Differential_Backup1");
        
        } 

PowerShell içinde veritabanları ve hareket yedekleme günlükleri

Bu kod örneği nasıl varolan bir veritabanını bir dosyaya yedeklemek ve geri yükleme yüklemek nasıl gösterir.

#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\MSSQL10.MSSQLSERVER\MSSQL\Backup\Test_FullBackup1"
del "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Test_DifferentialBackup1"

Visual Basic'te çalıştıran veritabanı bütünlüğünü denetler

SQL Server veri bütünlük denetimi sağlar.Bu kod örneği, bir veritabanı türü tutarlılık belirtilen veritabanı üzerinde çalışır.Bu örnekte, CheckTables kullanılır, ancak CheckAllocations, CheckCatalog, veya CheckIdentityValues benzer şekilde kullanılabilecek.

Not

The StringCollection object requires a reference to the namespace using the imports System.Collections.Specialized statement.

'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

Veritabanı bütünlüğü çalışan Visual C# [NULL]'ta denetler

SQL Server veri bütünlük denetimi sağlar.Bu kod örneği, bir veritabanı türü tutarlılık belirtilen veritabanı üzerinde çalışır.Bu örnekte, CheckTables kullanılır, ancak CheckAllocations, CheckCatalog, veya CheckIdentityValues benzer şekilde kullanılabilecek.

Not

The StringCollection object requires a reference to the namespace using the imports System.Collections.Specialized statement.

{
            //Connect to the local, default instance of SQL Server. 
            Server srv = new Server();

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

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

PowerShell içinde çalışan veritabanı bütünlüğünü denetler

SQL Server veri bütünlük denetimi sağlar.Bu kod örneği, bir veritabanı türü tutarlılık belirtilen veritabanı üzerinde çalışır.Bu örnekte, CheckTables kullanılır, ancak CheckAllocations, CheckCatalog, veya CheckIdentityValues benzer şekilde kullanılabilecek.

Not

The StringCollection object requires a reference to the namespace using the imports System.Collections.Specialized statement.

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

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