Monday, March 11, 2013

Restore SQL Server Database Using C#

In previous post SQL Server Database BackUp using C#, I explained how to take backup of SQL Server  database using C#.  Today I am going to describe how to restore SQL Server database backup programatically using C# and SQL Server Management Objects (SMO).

You can also check more articles related to C#, ASP.Net , SQL Server and others.

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
For restoring the backup using  C#, you have to add the following references in your application-

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended
Microsoft.SqlServer.SqlEnum

In your .CS file you will have to use the following namespaces-

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

After using above namespaces, write the following code to take the database backup-

public void RestoreDatabase(string databaseName, string userName, string password, string serverName, string sourcePath)
        {
            try
            {
                //Define a Backup object variable.
                Restore sqlRestore = new Restore();

                //Specify the type of backup, the description, the name, and the database to be backed up.
                sqlRestore.Action = RestoreActionType.Database;
                sqlRestore.NoRecovery = false;
                sqlRestore.Database = databaseName;
                sqlRestore.ReplaceDatabase = true;
                sqlRestore.PercentCompleteNotification = 10;

                //Declare a BackupDeviceItem
                BackupDeviceItem deviceItem = new BackupDeviceItem(sourcePath, DeviceType.File);
                //Define Server connection
                ServerConnection connection = new ServerConnection(serverName, userName, password);
                //To Avoid TimeOut Exception
                Server sqlServer = new Server(connection);
                sqlServer.ConnectionContext.StatementTimeout = 60 * 60;               
                sqlRestore.Checksum = true;
                sqlRestore.ContinueAfterError = true;
                //Add the device to the Restore object.
                sqlRestore.Devices.Add(deviceItem);

                //Run SqlRestore to perform the database restore on the instance of SQL Server.
                sqlRestore.SqlRestore(sqlServer);
                //Remove the restore device from the restore object.
                sqlRestore.Devices.Remove(deviceItem);
            }
            catch(Exception ex) {
                Response.Write(ex.Message);
            }
        }

Happy coding!!

2 comments:

^ Scroll to Top