You must Sign In to post a response.
  • Category: .NET

    How to take sql server database backup and restore it through vb.net code?

    i developed my project in vb.net 2008 with sql server express edition 2005. i want to take backup of my database and restore it through vb.net code. how to take sql server database backup and restore it through vb.net code?
  • #658331
    You can do it using SQLDMO class.
    here is good example

    Dim oDevice As New SQLDMO.BackupDevice
    Dim BACKUP As New SQLDMO.BACKUP
    Dim SERVER As New SQLServer

    Private Sub Form_Load()
    On Error Resume Next 'If the device already exists an error will result if you try to add it again so just resume next cos its already there

    With oDevice
    .Type = SQLDMODevice_DiskDump
    .Name = "MyDBBackup"
    .PhysicalLocation = "C:\\BACKUP.bak"
    End With

    SERVER.Connect "sa", ""
    SERVER.BackupDevices.Add oDevice
    BACKUP.Action = SQLDMOBackup_Database
    BACKUP.Database = "MYDB"
    BACKUP.Devices ="MyDBBackup"
    BACKUP.BackupSetDescription = "Full BackUp"
    BACKUP.BackupSetName = "testbackup"

    BACKUP.SQLBackup SERVER

    End Sub

    or
    You can checkout following link
    http://www.codeproject.com/Articles/21403/SQL-Server-Database-Backup-Utility-using-VB-NET-an

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #658398
    You can create a back up of the entire database (.bak) on button click by providing the database credentials and path to store the database backup file.

    Please check the following code:


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

    public partial class BackupDatabase : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
    BackupSqlDatabase("YourDataBaseName", "sa", "password", "ServerName", "C:\\YourDataBaseName.bak");
    }

    public void BackupSqlDatabase(String databaseName, String userName, String password, String serverName, String destinationPath)
    {
    Backup sqlBackup = new Backup();

    sqlBackup.Action = BackupActionType.Database;
    sqlBackup.BackupSetDescription = "ArchiveDataBase:" +
    DateTime.Now.ToShortDateString();
    sqlBackup.BackupSetName = "Archive";

    sqlBackup.Database = databaseName;

    BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);

    Database db = sqlServer.Databases[databaseName];

    sqlBackup.Initialize = true;
    sqlBackup.Checksum = true;
    sqlBackup.ContinueAfterError = true;

    sqlBackup.Devices.Add(deviceItem);
    sqlBackup.Incremental = false;

    sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
    sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

    sqlBackup.FormatMedia = false;

    sqlBackup.SqlBackup(sqlServer);
    }
    }


    Similarly you can restore the database using the following code:


    public void RestoreSqlDatabase(String databaseName, String filePath, String serverName, String userName, String password, String dataFilePath, String logFilePath)
    {
    try
    {
    Microsoft.SqlServer.Management.Smo.Restore sqlRestore = new Microsoft.SqlServer.Management.Smo.Restore();

    Microsoft.SqlServer.Management.Smo.BackupDeviceItem deviceItem = new Microsoft.SqlServer.Management.Smo.BackupDeviceItem(filePath, Microsoft.SqlServer.Management.Smo.DeviceType.File);
    sqlRestore.Devices.Add(deviceItem);
    sqlRestore.Database = databaseName;

    Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection(serverName, userName, password);
    Microsoft.SqlServer.Management.Smo.Server sqlServer = new Microsoft.SqlServer.Management.Smo.Server(connection);

    Microsoft.SqlServer.Management.Smo.Database db = sqlServer.Databases[databaseName];
    sqlRestore.Action = Microsoft.SqlServer.Management.Smo.RestoreActionType.Database;
    String dataFileLocation = dataFilePath + databaseName + ".mdf";
    String logFileLocation = logFilePath + databaseName + "_Log.ldf";
    db = sqlServer.Databases[databaseName];
    Microsoft.SqlServer.Management.Smo.RelocateFile rf = new Microsoft.SqlServer.Management.Smo.RelocateFile(databaseName, dataFileLocation);

    sqlRestore.RelocateFiles.Add(new Microsoft.SqlServer.Management.Smo.RelocateFile(databaseName, dataFileLocation));
    sqlRestore.RelocateFiles.Add(new Microsoft.SqlServer.Management.Smo.RelocateFile(databaseName + "_log", logFileLocation));
    sqlRestore.ReplaceDatabase = true;
    sqlRestore.Complete += new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler(sqlRestore_Complete);
    sqlRestore.PercentCompleteNotification = 10;
    sqlRestore.PercentComplete += new Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler(sqlRestore_PercentComplete);

    sqlRestore.SqlRestore(sqlServer);

    db = sqlServer.Databases[databaseName];

    db.SetOnline();

    sqlServer.Refresh();
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message);
    }
    }


    Note: Please add a reference of Microsoft.SqlServer.Smo in your project.


    Thanks & Regards
    Paritosh Mohapatra
    Microsoft MVP (ASP.Net/IIS)
    DotNetSpider MVM

  • #658717
    Dear friend,
    try like this

    using System;
    using System.Configuration;
    using System.Data;

    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Data.SqlClient;

    public partial class _Default : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    SqlConnection con = new SqlConnection(@"server=Servername;uid=sa;password=sasa;database=databasename");
    con.Open();
    string strpath="backup database databasename to disk='D:\\Reminders\\'" + DateTime.Now.Date.ToString() + "'.bak'";

    SqlCommand cmd = new SqlCommand(strpath, con);
    cmd.ExecuteNonQuery();


    }
    }



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.