Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to Restore SQL Server Database in ASP.NET

Author: Alanna Kremer
Download Source Code : 1302_RestoreSQLDatabase.zip

When you backup your Database, you might need to restore it. You can programmatically restore your Database in ASP.NET.

 

As you can write a Stored Procedure to backup your database and can call it from your application to backup your database, same is true to restore your database. Restoring your database means you can replace existing database in your SQL Server or you can create a new database.

In a previous article, we have learned how to Backup Database. I will show how to restore database programmatically in this article. Follow these steps to restore “MyDatabase”.

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Drag and Drop a Button and a Label in Default.aspx page

    <asp:Button ID="Button1" runat="server" Text="Restore Database"
                onclick="Button1_Click" />
    <br />
    <asp:Label ID="Label1" runat="server"></asp:Label>
     
  6. Now add references.
    1. Website > Add Reference > Browse
    2. Go to the Assemblies folder of SQL Server
    3. For SQL Server 2008 in Windows 7:
      C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies
    4. Select “Microsoft.SqlServer.ConnectionInfo.dll” file and Click Ok
    5. Add references for following dll files also
      Microsoft.SqlServer.Management.Sdk.Sfc.dll
      Microsoft.SqlServer.Smo.dll
      Microsoft.SqlServer.SmoExtended.dll
       
  7. Include following namespaces in your code behind file

    C#
     
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Common;
     
    VB.NET
     
    Imports Microsoft.SqlServer.Management.Smo
    Imports Microsoft.SqlServer.Management.Common
     
  8. Now Write code below in button Click event

    C#
     
    protected void Button1_Click(object sender, EventArgs e) {
        Restore restore = new Restore();
        restore.Database = "MyDatabase_Restore";
        restore.Action = RestoreActionType.Database;
        string name = "D:\\Backup\\MyDatabase.Bak";
     
        restore.Devices.AddDevice(name, DeviceType.File);
        restore.ReplaceDatabase = false;
     
        RelocateFile file1 = new RelocateFile("MyDatabase", "C:\\Databases\\MyDatabase.mdf");
        RelocateFile file2 = new RelocateFile("MyDatabase_Log", "C:\\Databases\\MyDatabase_Log.ldf");
        restore.RelocateFiles.Add(file1);
        restore.RelocateFiles.Add(file2);
     
        ServerConnection serConn = new ServerConnection("Your Server");
        serConn.LoginSecure = true;
        Server sqlServer = new Server(serConn);
        restore.SqlRestore(sqlServer);
     
        Label1.Text = "Database is restored successfully";
    }
     
    VB.NET
     
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim restore As New Restore()
        restore.Database = "MyDatabase_Restore"
        restore.Action = RestoreActionType.Database
        Dim name As String = "D:\Backup\MyDatabase.Bak"
     
        restore.Devices.AddDevice(name, DeviceType.File)
        restore.ReplaceDatabase = False
     
        Dim file1 As New RelocateFile("MyDatabase", "C:\Databases\MyDatabase.mdf")
        Dim file2 As New RelocateFile("MyDatabase_Log", "C:\Databases\MyDatabase_Log.ldf")
        restore.RelocateFiles.Add(file1)
        restore.RelocateFiles.Add(file2)
     
        Dim serConn As New ServerConnection("Your Server")
        serConn.LoginSecure = True
        Dim sqlServer As New Server(serConn)
        restore.SqlRestore(sqlServer)
     
        Label1.Text = "Database is restored successfully"
    End Sub
     
    Create an instance of “Restore” class, provide RestoreActionType as “Database” and then provide the database name. Add file devise to instance as we are restoring a file. Now make connection to the server and pass this connection to Server class constructor. At the end call SqlRestore() method by passing server instance as parameter.
     
  9.  Press Ctrl+F5 and see result in browser. Click on Button to Restore Database

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to call a Stored Procedure in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net