I want to backup my database everyday, so for that I am trying to create one script which executes everyday and get my database's backup on my hosting server file manager folder.
for that I have written following code:
var backupFolderAbsPath = Request.MapPath("~/Backup/sql/");
var connectionString = ConfigurationManager.ConnectionStrings["enwonConnection"].ConnectionString;
var backupFolder = ConfigurationManager.AppSettings["BackupFolder"].ToString();
var sqlConStrBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
var backupFileName = String.Format("{0}{1}-{2}.bak", backupFolderAbsPath, sqlConStrBuilder.InitialCatalog, DateTime.Now.ToString("yyyy-MM-dd"));
using (var connection = new System.Data.SqlClient.SqlConnection(sqlConStrBuilder.ConnectionString))
{
var query = String.Format("BACKUP DATABASE [{0}] TO DISK='{1}'",
sqlConStrBuilder.InitialCatalog, backupFileName);
using (var command = new System.Data.SqlClient.SqlCommand(query, connection))
{
connection.Open();
command.ExecuteNonQuery();
}
}
But while it will goes to command.ExecuteNonQuery() there is an error of
Cannot open backup device 'E:\HostingSpaces\seorank\enwon.com.au\wwwroot\Backup\sql\enwon-2016-04-27.bak'. Operating system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.
Please let me know the solution for this issue. How does I do this with MVC4?
What I have tried:
var backupFolderAbsPath = Request.MapPath("~/Backup/sql/");
// read connectionstring from config file
var connectionString = ConfigurationManager.ConnectionStrings["enwonConnection"].ConnectionString;
// read backup folder from config file ("C:/temp/")
var backupFolder = ConfigurationManager.AppSettings["BackupFolder"].ToString();
var sqlConStrBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
// set backupfilename (you will get something like: "C:/temp/MyDatabase-2013-12-07.bak")
//var backupFileName = String.Format("{0}{1}-{2}.bak", backupFolder, sqlConStrBuilder.InitialCatalog, DateTime.Now.ToString("yyyy-MM-dd"));
var backupFileName = String.Format("{0}{1}-{2}.bak", backupFolderAbsPath, sqlConStrBuilder.InitialCatalog, DateTime.Now.ToString("yyyy-MM-dd"));
using (var connection = new System.Data.SqlClient.SqlConnection(sqlConStrBuilder.ConnectionString))
{
var query = String.Format("BACKUP DATABASE [{0}] TO DISK='{1}'",
sqlConStrBuilder.InitialCatalog, backupFileName);
using (var command = new System.Data.SqlClient.SqlCommand(query, connection))
{
connection.Open();
command.ExecuteNonQuery();
}
}