home articles news code blog links search  
 
         
  Code Samples

Articles and Presentations

sqldbatips.com blog

  
Getting Started with SMO in SQL 2005 - Backups

Author Jasper Smith Hits 8654
Create Date 22-08-2004 Last Updated 08-08-2007
Versions SQL2005    

Overview

In this series of articles, I'll demonstrate how to use SMO (SQL Management Objects) to do a variety of common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more. These are some of the operations available in the SMO database maintenance utility available on this site - ExpressMaint. In this article we will concentrate on how to perform backups using SMO in SQL2005. For information on how to build and compile a SMO application please review Getting Started with SMO in SQL 2005.

Database Backups

In order to perform a backup using SMO we require two objects, a Server object and a Backup object. In its simplest form, a Backup object requires only a few properties to be set before calling the SqlBackup method and passing in the Server object as can be seen in the following example that does a Full database backup of the SMO database to the file c:\SMOTest.bak

[Visual Basic]

Imports Microsoft.SqlServer.Management.Smo

Module SMOTest

    Sub Main()
        Dim svr As Server = New Server()
        Dim bkp As Backup = New Backup()
        bkp.Devices.AddDevice("C:\SMOTest.bak", DeviceType.File)
        bkp.Database = "SMO"
        bkp.Action = BackupActionType.Database
        bkp.Initialize = True
        bkp.PercentCompleteNotification = 10
        AddHandler bkp.PercentComplete, AddressOf ProgressEventHandler
        bkp.SqlBackup(svr)

    End Sub

    Private Sub ProgressEventHandler(ByVal sender As Object, _
                                     ByVal e As PercentCompleteEventArgs)
        Console.WriteLine(e.Percent.ToString + "% backed up")
    End Sub

End Module

[C#]

using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
        static void Main()
        {
            Server svr = new Server();
            Backup bkp = new Backup();
            bkp.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
            bkp.Database = "SMO";
            bkp.Action = BackupActionType.Database;
            bkp.Initialize = true;
            bkp.PercentCompleteNotification = 10;
            bkp.PercentComplete += new PercentCompleteEventHandler(bkp_PercentComplete);
            bkp.SqlBackup(svr);
        }

        static void bkp_PercentComplete(object sender, PercentCompleteEventArgs e)
        {
            Console.WriteLine(e.Percent.ToString() + "% backed up");
        }       
    }
}

There are many other properties that can be set for the Backup object. For the full list see the Backup Object in Books On Line (if you have SQL2005 Books On Line installed click this link to be taken to the correct page).

Filegroup Backups

In the example below, just the WorkOrderGroup filegroup of the Adventureworks database is backed up (the script to convert the AdventureWorks OLTP demo database into a multi filegroup database can be found in the SQL Server Engine samples and by default is located in C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\FileGroups\Scripts). Filegroups to be backed up are specified by adding them to the Backup objects DatabaseFileGroups collection. In order to backup specific files, they can be added to the DatabaseFiles collection.

[Visual Basic]

Imports Microsoft.SqlServer.Management.Smo

Module SMOTest
	Sub Main()
        Dim svr As Server = New Server()
        Dim bkp As Backup = New Backup()
        bkp.Database = "AdventureWorks"
        bkp.Action = BackupActionType.Files
        bkp.DatabaseFileGroups.Add("WorkOrderGroup")
        bkp.Devices.AddDevice("C:\AWFGTest.bak", DeviceType.File)
        bkp.SqlBackup(svr)
	End Sub
End Module

[C#]

using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
     static void Main()
     {
      Server svr = new Server();
      Backup bkp = new Backup();
      bkp.Database = "AdventureWorks";
      bkp.Action = BackupActionType.Files;
      bkp.DatabaseFileGroups.Add("WorkOrderGroup");
      bkp.Devices.AddDevice(@"C:\AWFGTest.bak", DeviceType.File);
      bkp.SqlBackup(svr);
     }
   }
}

Differential Backups

Performing a differential backup is very straightforward, simply set the Incremental property of the Backup object to True

bkp.Incremental = True

Log Backups

Log backups are equally straightforward, simply set the Action property of the Backup object to BackupActionType.Log

[Visual Basic]

Imports Microsoft.SqlServer.Management.Smo

Module SMOTest
	Sub Main()
		Dim svr As Server = New Server()
		Dim bkp As Backup = New Backup()
		bkp.Action = BackupActionType.Log
		bkp.Database = "SMO"
		bkp.Devices.AddDevice("c:\SMOTest.trn", DeviceType.File)
		bkp.SqlBackup(svr)
	End Sub
End Module

[C#]

using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
     static void Main()
     {
      Server svr = new Server();
      Backup bkp = new Backup();
      bkp.Action = BackupActionType.Log;
      bkp.Database = "SMO";
      bkp.Devices.AddDevice(@"c:\SMOTest.trn", DeviceType.File);
      bkp.SqlBackup(svr);
     }
   }
}

Related Articles

   Getting Started with SMO in SQL 2005
   Getting Started with SMO in SQL 2005 - Restores
   Getting Started with SMO in SQL 2005 - Integrity Checks
   Getting Started with SMO in SQL 2005 - Verifying Backups

Print this page  Print this page Email this article  Email this article Add to Favourites  Add to Favourites
 
         
© Copyright sqldbatips Ltd 2008. Contact webmaster home | news | articles | code | blog | links | search  

free tracking