How to backup and restore local database in asp.net application using entity framework?

Question

I want to add a simple backup and restore option to my ASP.Net WebApplication. I created the procedure below in my database:

CREATE PROCEDURE dbo.[BackUp]
    @path NVARCHAR(MAX)
AS 
    DECLARE @DataBaseName NVARCHAR(MAX)  = DB_NAME()

    BACKUP DATABASE @DataBaseName
    TO DISK = @path
    WITH FORMAT,
    MEDIANAME = 'Backup'
GO

I created my model using entity framework. the procedure is:

public virtual int BackUp(string path)
{
    var pathParameter = path != null ?
    new ObjectParameter("path", path) :
    new ObjectParameter("path", typeof(string));
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("BackUp", pathParameter);
}

But when I call Backup, I see this error:

Cannot perform a backup or restore operation within a transaction. BACKUP DATABASE is terminating abnormally.

enter image description here

I don't know that what is the meaning of that error message and how can i solve that!! — Rasool Ghafari
– Rasool Ghafari, Commented Jun 27, 2014 at 5:15
Seems kind of clear. You have a transaction open and the BACKUP statement cannot be run under a transaction. Find out, who opened that transaction. — usr
– usr, Commented Jun 27, 2014 at 9:29

Rasool Ghafari · Accepted Answer · 2015-04-06 16:53:26Z

I solved that problem by changing Backup function to this:

public void BackUp()
    {

        Kimiakesht entity = new Kimiakesht();
        string dataTime = DateTime.Now.ToString("yyyy-MM-dd") + "-" + DateTime.Now.ToString("HH-mm");
        string directory = HttpContext.Current.Server.MapPath("~/") + "/backups/" + dataTime + "/";
        string fileName = directory + dataTime + ".bak";

        #region Response
        HttpResponse Response = HttpContext.Current.Response;
        Response.Clear();
        Response.BufferOutput = false;
        Response.ContentType = "application/zip";
        Response.AddHeader("content-disposition", "inline; filename=\"" + dataTime + "\".zip");
        #endregion

        if (!Directory.Exists(directory))
            Directory.CreateDirectory(directory);


        // Here the procedure is called and executes successfully
        entity.Database.ExecuteSqlCommand(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction, "EXEC [dbo].[BackUp] @path = N'" + fileName + "'");

        #region Compress
        using (var memoryStream = new System.IO.MemoryStream())
        {
            using (ZipFile zip = new ZipFile())
            {
                zip.CompressionLevel = Ionic.Zlib.CompressionLevel.BestCompression;
                zip.ParallelDeflateThreshold = -1;
                zip.AddDirectory(directory);
                zip.Save(memoryStream);
            }

            memoryStream.Position = 0;
            var b = new byte[1024];
            int n;
            while ((n = memoryStream.Read(b, 0, b.Length)) > 0)
                Response.OutputStream.Write(b, 0, n);
        }
        #endregion

        Directory.Delete(directory, true);

        Response.Close();
    }

This function create backup from database, compress it and then return it to download as a HttpResponse. Finally deletes the temp directory

UPDATE

this is stored procedure content:

ALTER PROCEDURE [dbo].[BackUp]
    @path NVARCHAR(MAX)
AS 
    DECLARE @DataBaseName NVARCHAR(MAX)  = DB_NAME()

    BACKUP DATABASE @DataBaseName
    TO DISK = @path
    WITH FORMAT,
    MEDIANAME = 'Z_SQLServerBackups'

Nice solution. could you explain how you made stored procedure on SQL and sample code.thanks

Collectives™ on Stack Overflow

How to backup and restore local database in asp.net application using entity framework?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related