locked
how to download file.bak as a zip file RRS feed

  • Question

  • User-1634604574 posted

    i have this code to create backup database and save it in folder a which is that folder a is a sub-folder inside temp folder and folder temp is inside local 

    C which is virtual directory on iis 

    controller

      public JsonResult get_backup_db(String Series)
            {
                String Result = "";
                using (con)
                {
                    try
                    {
                        SqlCommand com = new SqlCommand("get_backup_db", con);
                        com.CommandType = CommandType.StoredProcedure;
                        con.Open();
                        com.ExecuteNonQuery();
                        con.Close();
                    }
                    catch (SqlException Ex)
                    {
                        Result += "Error:\n" + Ex.Message;
                    }
                }
                return Json(Result, JsonRequestBehavior.AllowGet);
            }

    view

       <script>
    
            $("#save").click(function () {
    
                $.ajax({
                    type: 'GET',
                    url: '/Main/get_backup_db',
                    data: {},
                    dataType: "json"
                })
    
            });
    
    </script>

    procedure

    create proc [dbo].[get_backup_db]
    as begin
    declare @file_name varchar(250)
    declare @db_name varchar(250)
    declare @file_date varchar(250)
    declare @db_name1 varchar(200);
    set @db_name1=(SELECT DB_NAME() AS [Current Database])
    
    set @file_name='C:\temp\a\'
    set @db_name=@db_name1
    set @file_date =convert (varchar(20),GETDATE(),112)
    
    set @file_name=@file_name+@db_name+'-'+@file_date+'.bak'
    
    backup database @db_name to disk=@file_name
    end

    now i want to download that backup file db as a zip file by clicking on the same button (save button)

    Saturday, May 23, 2020 5:56 PM

All replies

  • User-821857111 posted

    You can use the ZipFile.CreateFromDirectory method to create a zip from your .bak file: https://docs.microsoft.com/en-us/dotnet/standard/io/how-to-compress-and-extract-files

    Sunday, May 24, 2020 5:46 AM
  • User-1634604574 posted

    is there another way?

    Sunday, May 24, 2020 5:55 AM
  • User-1634604574 posted

    i have this procedure to get backup of db 

    ALTER proc [dbo].[get_backup_db]
    as begin
    declare @file_name varchar(250)
    declare @db_name varchar(250)
    declare @file_date varchar(250)
    declare @db_name1 varchar(200);
    set @db_name1=(SELECT DB_NAME() AS [Current Database])
    
    set @file_name='C:\temp\a\'
    set @db_name=@db_name1
    set @file_date =convert (varchar(20),GETDATE(),112)
    
    set @file_name=@file_name+@db_name+'-'+@file_date+'.bak'
    
    backup database @db_name to disk=@file_name
    end



    but i want to save that backup as a zip file how can i do it?

    Sunday, May 24, 2020 6:13 AM
  • User-1634604574 posted

    i wrote this code to download file .bak as a zip but it is not downloaded it

       public ActionResult DownloadFile()
            {
                string FileName = "CF_Last2-20200516";//Request.Params["IMS_FILE_NAME"];
                string FullFileLogicalPath = Path.Combine(ConfigurationManager.AppSettings["DocumentationLocation"], FileName);
                string FullfilePhysicalPath = Path.Combine(ConfigurationManager.AppSettings["DocumentationLocation"], FileName);
                if (System.IO.File.Exists(FullfilePhysicalPath))
                {
                    return File(FullfilePhysicalPath, "Application/zip", DateTime.Now.ToLongTimeString() + ".bak");
                }
                else
                {
                    return Json(new { Success = "false" });
                }
            }

    Sunday, May 24, 2020 6:33 AM
  • User753101303 posted

    Hi,

    I would try:

    backup database @db_name to disk=@file_name WITH COPY_ONLY,COMPRESSION -- if I remember compression is available for SQL Server 2008 or later

    copy_only is to tell SQL Server this is an unscheduled backup (ie if you have log backups in place, they will be unaffected by this backup).

    Edit: else you would need to use https://docs.microsoft.com/en-us/dotnet/api/system.io.compression?view=netframework-4.8 but I doubt it's worth (or test a compressed backup vs an uncompressed zipped backup).

    Also the purpose of this kind of backup should be to get production data for testing. "True backups" needs to be done without any user action, else you won't have them when needed (and preferably someone should check periodically the automated backup can be restored). 

    Sunday, May 24, 2020 9:20 AM
  • User-821857111 posted

    i wrote this code to download file .bak as a zip but it is not downloaded it

       public ActionResult DownloadFile()
            {
                string FileName = "CF_Last2-20200516";//Request.Params["IMS_FILE_NAME"];
                string FullFileLogicalPath = Path.Combine(ConfigurationManager.AppSettings["DocumentationLocation"], FileName);
                string FullfilePhysicalPath = Path.Combine(ConfigurationManager.AppSettings["DocumentationLocation"], FileName);
                if (System.IO.File.Exists(FullfilePhysicalPath))
                {
                    return File(FullfilePhysicalPath, "Application/zip", DateTime.Now.ToLongTimeString() + ".bak");
                }
                else
                {
                    return Json(new { Success = "false" });
                }
            }

    It's not a zip file. You can't just change the content type of the response. That doesn't change a .bak file to a .zip file.

    Sunday, May 24, 2020 9:33 AM
  • User-821857111 posted

    is there another way?

    What's wrong with using the Zipfile method?

    Sunday, May 24, 2020 9:34 AM
  • User753101303 posted

    Hi,

    Edited: mistakenly thought you posted the same question twice,, my bad.

    Basic debugging ie if you return the "else" response this is because System.IO.File.Exists returns false . I suspect it happens because the FullPhysicalPath doesn't include a file extension. I assume it does have a .bak extension ?

    Sunday, May 24, 2020 9:38 AM
  • User-1634604574 posted

    i don't know how to use it in mvc can you write my sample as a zipfile?

    Sunday, May 24, 2020 12:02 PM
  • User753101303 posted

    What is the current problem?

    Once again using

    backup database @db_name to disk=@file_name WITH FORMAT,COPY_ONLY,COMPRESSION

    will just tell SQL Server to compress the backup file. Or be explicit if you have a problem such as using an old SQL Server version that doesn't have this.
    If this is a download problem it seems to me that System.IO.File.Exists returns false because the file name is missing the .bak extension.

    To me you have all the information you need to make this work. What if you try 

    string FileName = "CF_Last2-20200516.bak"; // Triple check, for now I believe your code doesn't find the backup file ?

    Edit: added the FORMAT option to DELETE all previous backups done to the same target file which is likely what you want anyway ? SQL Server can store multiple backups in the same bak file (if they are all done with compatible options such as compresssed/uncompressed).

    Sunday, May 24, 2020 12:15 PM
  • User-1634604574 posted

    i get error

    declare @file_name varchar(250)
    declare @db_name varchar(250)
    declare @file_date varchar(250)
    declare @db_name1 varchar(200);
    set @db_name1=(SELECT DB_NAME() AS [Current Database])
    
    set @file_name='C:\temp\a\'
    set @db_name=@db_name1
    set @file_date =convert (varchar(20),GETDATE(),112)
    
    set @file_name=@file_name+@db_name+'-'+@file_date+'.bak'
    
    backup database @db_name to disk=@file_name WITH FORMAT,COPY_ONLY,COMPRESSION
    

    error

    BACKUP DATABASE WITH COMPRESSION is not supported on Express Edition (64-bit).
    Msg 3013, Level 16, State 1, Line 13
    BACKUP DATABASE is terminating abnormally.

    Sunday, May 24, 2020 12:48 PM
  • User753101303 posted

    Ah I didn't caugth you were using the SQL Server Express edition. This is for development only or you are using it in production as well?

    If your edition doesn't support compressed backup then you could use https://docs.microsoft.com/en-us/dotnet/standard/io/how-to-compress-and-extract-files#example-1-create-and-extract-a-zip-file to compress the bak file.

    Sunday, May 24, 2020 1:19 PM
  • User1686398519 posted

    Hi,  zhyanadil.it

    According to your needs, I made a simple example. You can refer to it.

    Note: This example uses SharpZipLib, you can install it through Nuget.

    //Download the backup file as a zip
            public FileResult BackupTestDownloadZipFile(string backupDIR,string fileName)
            {
                var tempOutPutPath = Path.Combine(backupDIR, fileName+".zip");
                var filePath = Path.Combine(backupDIR, fileName + ".Bak");
                using (ZipOutputStream s = new ZipOutputStream(System.IO.File.Create(tempOutPutPath)))
                {
                    s.SetLevel(9); 
                    byte[] buffer = new byte[4096];
                        ZipEntry entry = new ZipEntry(filePath);
                        entry.DateTime = DateTime.Now;
                        entry.IsUnicodeText = true;
                        s.PutNextEntry(entry);
                        using (FileStream fs = System.IO.File.OpenRead(filePath))
                        {
                            int sourceBytes;
                            do
                            {
                                sourceBytes = fs.Read(buffer, 0, buffer.Length);
                                s.Write(buffer, 0, sourceBytes);
                            } while (sourceBytes > 0);
                        }
                    s.Finish();
                    s.Flush();
                    s.Close();
                }
    
                byte[] finalResult = System.IO.File.ReadAllBytes(tempOutPutPath);
                if (finalResult == null || !finalResult.Any())
                    throw new Exception(String.Format("No Files found"));
                return File(finalResult, "application/zip", fileName + ".zip");
            }
    
            public ActionResult BackupTest()
            {
                //This is how I create the database backup.You can replace this with your own method
                string connectionString = ConfigurationManager.ConnectionStrings["Model1"].ConnectionString;
                string backupDIR = Server.MapPath("~/BackupDB/");
                var fileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
                if (!System.IO.Directory.Exists(backupDIR))
                {
                    System.IO.Directory.CreateDirectory(backupDIR);
                }
                try
                {
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        string queryString = "backup database test to disk='" + backupDIR + "\\" + fileName + ".Bak'";
                        SqlCommand command = new SqlCommand(queryString, connection);
                        connection.Open();
                        command.ExecuteNonQuery();
                        connection.Close();
                    }
                }
                catch (Exception ex)
                {
                    var result = ex.Message;
                }
                return BackupTestDownloadZipFile(backupDIR, fileName);
            }

    Here is the result.

     
    Best Regards,

    YihuiSun

    Wednesday, May 27, 2020 8:40 AM