Restore 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).

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, stringserverName, 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!!