Verification of backup file .bak using Microsoft.Management.Smo

Question

I backup a database using Microsoft.SqlServer.Management.Smo. My question is how to verify that the backup file is restorable and will work properly. After spending some time on the internet, I found that the following T-SQL verifies but is there a way in Microsoft.Management.Smo assembly. I am expecting some code sample.

RESTORE VERIFYONLY
FROM DISK = 'C:\Test\Test.bak'
WITH CHECKSUM

Below is my code samples.

using System;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace BackupApplication
{
    class DatabaseBackup
    {    
        public void DoDataBaseBackup()
        {    
            string connectionString = ConfigurationManager.ConnectionStrings["Test"].ToString();
            SqlConnection connecton = new SqlConnection(connectionString);
            ServerConnection serverConnection = new ServerConnection(connecton);
            Server myServer = new Server(serverConnection);

            //Using windows authentication                
            myServer.ConnectionContext.Connect();

            Database myDatabase = myServer.Databases[ConfigurationManager.AppSettings["DatabaseNameToBackup"]];

            //Backup operation
            Backup bkpDBFull = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBFull.Action = BackupActionType.Database;

            /* Specify the name of the database to back up */
            bkpDBFull.Database = myDatabase.Name;

            /* You can take backup on several media type (disk or tape), here I am
             * using File type and storing backup on the file system */
            string destinationFolderForDatabase = ConfigurationManager.AppSettings["DestinationFolderForDatabase"];
            bkpDBFull.Devices.AddDevice(destinationFolderForDatabase, DeviceType.File);
            bkpDBFull.BackupSetName = "Test database Backup";
            bkpDBFull.BackupSetDescription = "test database - Full Backup";


            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);

            /* You can specify Initialize = false (default) to create a new 
             * backup set which will be appended as last backup set on the media. You
             * can specify Initialize = true to make the backup as first set on the
             * medium and to overwrite any other existing backup sets if the all the
             * backup sets have expired and specified backup set name matches with
             * the name on the medium */
            bkpDBFull.Initialize = false;

            /* You can specify Incremental = false (default) to perform full backup or Incremental = true to perform differential backup since most recent full backup */
            bkpDBFull.Incremental = false;

            /* Wiring up events for progress monitoring */
            bkpDBFull.PercentComplete += CompletionStatusInPercent;
            bkpDBFull.Complete += Backup_Completed;

            /* SqlBackup method starts to take back up
             * You can also use SqlBackupAsync method to perform the backup 
             * operation asynchronously */
            bkpDBFull.SqlBackup(myServer);

            if (myServer.ConnectionContext.IsOpen)
            {
                myServer.ConnectionContext.Disconnect();
            }
        }

        private void Backup_Completed(object sender, ServerMessageEventArgs e)
        {
            Console.WriteLine("TestDatabase Backup Completed.");
            Console.WriteLine(e.Error.Message);            
        }

        private void CompletionStatusInPercent(object sender, PercentCompleteEventArgs e)
        {
            Console.Clear();
            Console.WriteLine("Percent completed: {0}%.", e.Percent);
        }
    }
}

Program.CS

using System;   

namespace BackupApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            DatabaseBackup databaseBackup = new DatabaseBackup();
            databaseBackup.DoDataBaseBackup();
            Console.ReadLine();
        }
    }
}

App.Config

<connectionStrings>     
    <add name="TestDatabase" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=SSPI"/>
</connectionStrings>
<appSettings>
    <add key="DestinationFolderForDatabase" value="C:\Test.bak"/>
    <add key="DatabaseNameToBackup" value="Test"/>
</appSettings>

_"I am expecting some code sample."_ - well, here's some documentation with a sample. [Restore.SqlVerify](https://msdn.microsoft.com/en-us/library/ms209758.aspx) has a sample of _"how to create a backup and verify that it is readable and complete."_, though you'd probably want to use [the overload that returns error messages](https://msdn.microsoft.com/en-us/library/ms209837.aspx) — stuartd, Apr 12 '17 at 14:55
Once the back up complete create a `Restore` and verify the same `BackupDeviceItem` used to create the backup. — Nkosi, Apr 12 '17 at 15:07

score 2 · Answer 1 · edited May 23 '17 at 12:17

2

Verifying can mean a variety of things; your particular answer and code samples are dependent on what way you want to go. Here are a few methods:

Use System.IO and check if file exists.
SMO can be utilized with SqlVerify
Sql Client can be used with Restore Verify return value from Sql Server system message

edited May 23 '17 at 12:17

Community

1
1

answered Apr 12 '17 at 15:12

Mad Myche

1,075
1
7
15

Simant · Accepted Answer · 2017-04-12T15:21:38.283

Following code helps to verify the backup file is restorable.

using System;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace BackupApplication
{
    class DatabaseBackup
    {    
        public void DoDataBaseBackup()
        {    
            string connectionString = ConfigurationManager.ConnectionStrings["Test"].ToString();
            SqlConnection connecton = new SqlConnection(connectionString);
            ServerConnection serverConnection = new ServerConnection(connecton);
            Server myServer = new Server(serverConnection);

            //Using windows authentication                
            myServer.ConnectionContext.Connect();

            Database myDatabase = myServer.Databases[ConfigurationManager.AppSettings["DatabaseNameToBackup"]];

            //Backup operation
            Backup bkpDBFull = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBFull.Action = BackupActionType.Database;

            /* Specify the name of the database to back up */
            bkpDBFull.Database = myDatabase.Name;

            /* You can take backup on several media type (disk or tape), here I am
             * using File type and storing backup on the file system */
            string destinationFolderForDatabase = ConfigurationManager.AppSettings["DestinationFolderForDatabase"];
            bkpDBFull.Devices.AddDevice(destinationFolderForDatabase, DeviceType.File);
            bkpDBFull.BackupSetName = "Test database Backup";
            bkpDBFull.BackupSetDescription = "test database - Full Backup";


            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);

            /* You can specify Initialize = false (default) to create a new 
             * backup set which will be appended as last backup set on the media. You
             * can specify Initialize = true to make the backup as first set on the
             * medium and to overwrite any other existing backup sets if the all the
             * backup sets have expired and specified backup set name matches with
             * the name on the medium */
            bkpDBFull.Initialize = false;

            /* You can specify Incremental = false (default) to perform full backup or Incremental = true to perform differential backup since most recent full backup */
            bkpDBFull.Incremental = false;

            /* Wiring up events for progress monitoring */
            bkpDBFull.PercentComplete += CompletionStatusInPercent;
            bkpDBFull.Complete += Backup_Completed;

            /* SqlBackup method starts to take back up
             * You can also use SqlBackupAsync method to perform the backup 
             * operation asynchronously */
            bkpDBFull.SqlBackup(myServer);

    Restore restore = new Restore();
    restore.Devices.AddDevice(@"C:\Test.bak", DeviceType.File);
    restore.Database = "Test2012";
    Console.WriteLine(restore.SqlVerify(myServer).ToString());
            if (myServer.ConnectionContext.IsOpen)
            {
                myServer.ConnectionContext.Disconnect();
            }
        }

        private void Backup_Completed(object sender, ServerMessageEventArgs e)
        {
            Console.WriteLine("TestDatabase Backup Completed.");
            Console.WriteLine(e.Error.Message);            
        }

        private void CompletionStatusInPercent(object sender, PercentCompleteEventArgs e)
        {
            Console.Clear();
            Console.WriteLine("Percent completed: {0}%.", e.Percent);
        }
    }
}

Verification of backup file .bak using Microsoft.Management.Smo

2 Answers2