CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2021
    Posts
    2

    SQL Server database backup problem

    I am using SQL Server 2014 Express with Visual Basic 2013 (Professional edition)

    I can backup a database but have a problem that I cannot solve. If I keep clicking on the button to save the database, it does not add a new file based on the time of saving nor does it overwrite to old file. It keeps appending data to the existing file. The file size just gets bigger and bigger.

    Here is my save code:

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Try
                Dim connection As String = ("Data Source=Apollo\sqlexpress;Initial Catalog=Calendar;Integrated Security=True")
    
                Dim conn As New SqlConnection(connection)
                conn.Open()
    
                Dim cmd As New SqlCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "BACKUP DATABASE Calendar TO DISK='D:\Temp\Calendar.BAK  '"
                cmd.Connection = conn
                cmd.ExecuteNonQuery()
    
                conn.Close()
    
                MsgBox("Data saved")
    
            Catch ex As Exception
    
                MsgBox(ex.Message)
    
            End Try
    I hope that I have inserted the code correctly. I have not used this site for many years and then under another user name and email address

    Can someone help please?
    Last edited by VictorN; September 13th, 2021 at 07:05 AM. Reason: replace the QUOTE tags with the CODE ones

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,100

    Re: SQL Server database backup problem

    Quote Originally Posted by Michael Newman View Post
    Here is my save code:

    Code:
    ...
                Dim cmd As New SqlCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "BACKUP DATABASE Calendar TO DISK='D:\Temp\Calendar.BAK  '"
                cmd.Connection = conn
                cmd.ExecuteNonQuery()
                ...
    By default the existing backup file will not overwritten, the new data will be appended to the existing one.
    Try to backup with INIT option:
    Code:
    BACKUP DATABASE Calendar TO DISK='D:\Temp\Calendar.BAK'
    WITH INIT
    See the BACKUP (Transact-SQL) in Microsoft.doc
    Victor Nijegorodov

  3. #3
    Join Date
    Sep 2021
    Posts
    2

    Re: SQL Server database backup problem

    Quote Originally Posted by VictorN View Post
    By default the existing backup file will not overwritten, the new data will be appended to the existing one.
    Try to backup with INIT option:
    Code:
    BACKUP DATABASE Calendar TO DISK='D:\Temp\Calendar.BAK'
    WITH INIT
    See the BACKUP (Transact-SQL) in Microsoft.doc
    Thanks Victor. I added the "WITH INIT" and it appears to overwrite the file as the file size no longer increases each time the save button is clicked. I will do further experiments with a dummy database so that if I mess it up, very little will be lost.

    One further question. The current code shows the file name with date and time and the file size in Windows Explorer, Is there a way of saving the backup as a new file based on the time that it was saved? For example, I would like to give the end user the ability to restore a backup from a selection of those saved based on date and /or time.

    I did look at the link that you provided but it was as clear as mud to me. That appears to be for DBAs dealing with the database only and provided no examples of SQL queries or T-SQL that could be adapted for use in VB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured