Backup and Restore operation on any database is a common operation. Some times it becomes very tedious when not handled properly.
There are some points that needs to be kept in mind while restoring a database. Here i am providing the code for restoring SQL Server Database from C# Code.
While restoring Database use should keep in mind that, only one user is using the Database, what i mean to say is Database should be in SINGLE_USER Mode.
you need to fire this SQL Command before Restre
ALTER DATABAES DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
this will make the Database in SINGLE_USER Mode and as soon as the Restore process is complete, it will automatically set back to MULTI_USER Mode.
Below is the Code For restoring the Database.
Make following declerations
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
if Required make reference to
Microsoft.SqlServer.Smo
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem("BackupFile", DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = "DatabaseName";
//ConnectionString Just make the connection with the Server
//I.E. without providing Initial Catalog Value
SqlConnection sqlCon = new SqlConnection(ConnectionString);
ServerConnection connection = new ServerConnection(sqlCon);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases["DatabaseName"];
sqlRestore.Action = RestoreActionType.Database;
sqlRestore.ReplaceDatabase = true;
sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
sqlRestore.PercentCompleteNotification = 1;
sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);
sqlRestore.Information += new ServerMessageEventHandler(sqlRestore_Information);
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases["MEDICALDATABASE"];
db.SetOnline();
sqlServer.Refresh();
this Code Restore backup database.
also one thing that needs to remember is that, the Backup file must be on the Server machine where you have Installed the SQL Server.
Check yourself....
