You must Sign In to post a response.
Category: .NET
#658331
You can do it using SQLDMO class.
here is good example
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]
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:
Similarly you can restore the database using the following code:
Note: Please add a reference of Microsoft.SqlServer.Smo in your project.
Thanks & Regards
Paritosh Mohapatra
Microsoft MVP (ASP.Net/IIS)
DotNetSpider MVM
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
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!!!
Return to Return to Discussion Forum