locked
Backing up DataBase using VB.net RRS feed

  • Pytanie

  • I have an application and Database that I have created in Visual Express 2015.

    I'm able to connect and query the Data Base, but when I try to Backup the data base, I get the following error.

    "Database 'TestDataBase' does not exist. I Made sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally."

    I can connect and query the Database, so I'm sure connection string is OK.

    Public SQLCon As New SqlConnection With {.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\jeff\documents\visual studio 2015\Projects\Database Backup\Database Backup\TestDataBase.mdf;Integrated Security=True"}
    
    
    
    Public Sub Backup()
    
            Dim stgBackupQuery As String = "BACKUP DATABASE TestDataBase TO DISK='C:\TestDataBase.bak'"
    
            Try
    
                SQLCon.Open()
    
                SQLCmd = New SqlCommand(stgBackupQuery, SQLCon)
    
                SQLCmd.ExecuteNonQuery()
    
                SQLCon.Close()
    
            Catch ex As Exception
    
                MsgBox(ex.Message)
    
                If SQLCon.State = ConnectionState.Open Then
    
                    SQLCon.Close()
    
                End If
    
            End Try
    
        End Sub

    I tried to add a path to the Database within the stgBackupQuery string, but I got syntax errors. 

    Not sure what the problem is. I wonder if it could be due the program being run in debug mode in Visual Studio. But I'm not sure.

    Thanks


    • Zmodyfikowany przez 1JRoberts niedziela, 6 marca 2016 07:06 Grammer
    niedziela, 6 marca 2016 04:23

Odpowiedzi

  • Okay I did research on this topic and found that the backup process works (as mentioned before) on databases that are attached to your SQL-Server and not a physically un-attached database such as in your connection string e.g.

    TestDataBase.mdf

    And set via AttachDbFilename in the connection string.

    My database is attached to the server which is why the backup works unlike an attached database

    Data Source=KARENS-PC;Initial Catalog=CustomerDatabase;Integrated Security=True


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Oznaczony jako odpowiedź przez 1JRoberts środa, 9 marca 2016 03:37
    poniedziałek, 7 marca 2016 14:33

Wszystkie odpowiedzi

  • Hello,

    I did a search, found code in C#, tried it and worked fine. Here is the vb.net version

    Imports System.Data.SqlClient
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim connectionString = My.Settings.ConnectionString
    
            ' read backup folder from config file ("C:MyBackUps")
            Dim backupFolder = My.Settings.BackupFolder ' ConfigurationManager.AppSettings["BackupFolder"];
    
            Dim Builder = New SqlConnectionStringBuilder(connectionString)
    
            ' set backupfilename (you will get something like: "C:\temp\MyDatabase-2013-12-07.bak")
            Dim backupFileName = String.Format("{0}{1}_KP1.bak", backupFolder, Builder.InitialCatalog)
    
            Console.WriteLine(backupFileName)
    
            Using connection = New SqlConnection(Builder.ConnectionString)
                Dim query = String.Format("BACKUP DATABASE {0} TO DISK='{1}'", Builder.InitialCatalog, backupFileName)
    
                Using command = New SqlCommand(query, connection)
                    connection.Open()
                    command.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class

    I setup connection and backup folder as follows and note I am using a full version of SQL-Server so my data source is KARENS-PC, not (local) or .\SQLEXPRESS. Backup folder must exists.

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <configSections>
            <sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
                <section name="WindowsFormsApplication1.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />
            </sectionGroup>
        </configSections>
        <connectionStrings>
            <add name="WindowsFormsApplication1.Properties.Settings.ConnectionString"
                connectionString="Data Source=KARENS-PC;Initial Catalog=CustomerDatabase;Integrated Security=True"
                providerName="System.Data.SqlClient" />
        </connectionStrings>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
        </startup>
        <userSettings>
            <WindowsFormsApplication1.Properties.Settings>
                <setting name="BackupFolder" serializeAs="String">
                    <value>C:\Data\Backup\</value>
                </setting>
            </WindowsFormsApplication1.Properties.Settings>
        </userSettings>
    </configuration>
    Note to makes many copies you could manipulate the file name format rather than hard code it as shown here.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    niedziela, 6 marca 2016 11:35
  • Karen,

    Thank you for your reply.

    I'm trying to understand your code and I have a few questions.

    First, The second bit of code that starts with <?xml. Where does that come from?

    I see code that kinda looks like that in the Solution Explorer in a file called "Application.myapp". Do I paste that portion of the code into that file? (Customizing it to my configuration from the machine.config file?). When I tried typing "My.Settings.ConnectionString", IntelliSense didn't like the word ".ConnectionString". My guess it's looking for that xml code.

    Second, The first part of code looks fairly similar to the code that I'm using, except the Connection string, Query string and Backup file name are "Built", for lack of a better word, at run time, rather than hard coded at design time.

    I tried a different connection string that I got from the machine.config file.

    <connectionStrings>
            <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
        </connectionStrings>
    
    Which produced a connection string of:
    
    Public SQLCon As New SqlConnection With {.ConnectionString = "Data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"}

    Which didn't work at all. I couldn't even query the Database. It just locked up the program.

    You stated that the code worked fine. Was this in Visual Studio's debug mode?

    The reason I ask is because I read that Visual studios makes a copy of the DataBase in Debug mode, and works off the copy. I was thinking maybe that is why it's not working.

    Everything else in the program associated with Database works, just not the backup.

    Thanks for your help.

    https://visualstudiomagazine.com/Blogs/Tool-Tracker/2012/05/Dealing-with-Local-Databases-or-Why-Your-Updates-Dont-Stick.aspx

    poniedziałek, 7 marca 2016 02:55
  • To obtain that which I presented (in app.config file in the project), using the data source window in the ide, select a database, select a table in the add new data source. Drag the table onto a form, many components and controls are added to the form. Once completed remove all those components and controls.

    What this gave you (I do the above process by hand coding but note this is done from experience) are the settings I showed in app.config.

    Now all the above is against a database on SQL-Server, not an attached database where an attached database is more likely not going to work as I have not seen any indication this procedure will work unless the database is attached to sql server and not local e.g. resides in your application folder.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Zaproponowany jako odpowiedź przez elisch123 niedziela, 27 września 2020 16:48
    poniedziałek, 7 marca 2016 03:17
  • Now all the above is against a database on SQL-Server, not an attached database where an attached database is more likely not going to work as I have not seen any indication this procedure will work unless the database is attached to sql server and not local e.g. resides in your application folder.

    I created the database within Visual Studios.

    I looked at installed programs on my computer and it has "Microsoft SQL Server 2014 Express Local DB". So I'm assuming that SQL server was installed with Visual Studios.

    Do I need to download SQL Server Express with tools or advanced services. Do I also need Server Management Studio?

    Sorry if these are dumb questions. This is my first Database application.

    Thanks.

    poniedziałek, 7 marca 2016 04:30
  • As per the screenshot below via SQL-Server Management Studio AdventureWorks can be backed up but not NORTHWND.MDB unless it is "attached" inside of SQL-Server Management Studio but not when not attached.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    poniedziałek, 7 marca 2016 10:05
  • Robert,

    I've never seen this going. For a backup of most databases, the database has to be disconnected. 

    But as soon that you do it from a program while there is a connection that is not the case. 

    Of course can you send a sql message to the server using a Batch file and start that with command


    Success
    Cor

    poniedziałek, 7 marca 2016 10:38
  • Cor this is possible while being connected where the connection is in app.config in this case shown in a prior reply.

    Imports System.Data.SqlClient
    ''' <summary>
    ''' Requires VS2015 or higher for string interpolation, otherwise VS2010 or higher
    ''' </summary>
    ''' <remarks>
    '''  Plenty of assertion done, feel free to use less if so desired
    ''' </remarks>
    Public Class SqlServerOperations
        Private BackupFileName As String
        Public ReadOnly Property FileName As String
            Get
                Return BackupFileName
            End Get
        End Property
        ''' <summary>
        ''' Location for creating backup of database
        ''' </summary>
        ''' <returns></returns>
        Public Property FolderName As String
        ''' <summary>
        ''' Connection string to work with our database
        ''' </summary>
        ''' <returns></returns>
        Public Property ConnectionString As String
        ''' <summary>
        ''' Make an exception available outside this class
        ''' </summary>
        ''' <returns></returns>
        Public Property Exception As Exception
        ''' <summary>
        ''' Determine if an exception has been recorded
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasError As Boolean
            Get
                Return Exception IsNot Nothing
            End Get
        End Property
        ''' <summary>
        ''' Provides next increment
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property NextIncrementValue As Integer
            Get
                If String.IsNullOrWhiteSpace(FolderName) Then
                    Throw New Exception("Missing FolderName")
                End If
    
                If String.IsNullOrWhiteSpace(ConnectionString) Then
                    Throw New Exception("Missing connection string")
                End If
    
                If Not IO.Directory.Exists(FolderName) Then
                    Throw New Exception($"'{FolderName}' not located")
                End If
    
                Dim nextValue As Integer = 0
    
                Try
                    Dim connectionString = Me.ConnectionString
                    Dim Builder = New SqlConnectionStringBuilder(connectionString)
    
                    '
                    ' Assumes extension is .bak and the file name convention is
                    ' FileNameN.bak
                    ' where N is a numeric value e.g. 1,2,3 etc
                    '
                    Return CInt(IO.Directory.GetFiles(FolderName, "*.bak") _
                        .Select(Function(file) IO.Path.GetFileName(file)) _
                        .Where(Function(file) file.StartsWith(Builder.InitialCatalog)) _
                        .Select(Function(file) file.Replace(Builder.InitialCatalog, "").Replace(".bak", "")).Last) + 1
    
                Catch ex As Exception
                    Throw New Exception("Failed to get increment for backup database")
                End Try
    
                Return nextValue
    
            End Get
        End Property
        ''' <summary>
        ''' Do the actual backup
        ''' </summary>
        ''' <returns></returns>
        Public Function Backup() As Boolean
            Dim Success As Boolean = False
            Dim Builder = New SqlConnectionStringBuilder(ConnectionString)
    
            Try
                BackupFileName = $"{FolderName}{Builder.InitialCatalog & NextIncrementValue.ToString}.bak"
            Catch ex As Exception
                Exception = ex
                Return False
            End Try
    
            Try
    
                Using connection = New SqlConnection(Builder.ConnectionString)
                    Dim query = String.Format("BACKUP DATABASE {0} TO DISK='{1}'", Builder.InitialCatalog, backupFileName)
    
                    Using command = New SqlCommand(query, connection)
                        connection.Open()
                        command.ExecuteNonQuery()
                    End Using
                End Using
    
                Success = True
    
            Catch ex As Exception
                Exception = ex
                Success = False
            End Try
    
            If Success Then
                If IO.File.Exists(backupFileName) Then
                    Return True
                Else
                    Return False
                End If
            Else
                Return False
            End If
    
        End Function
    End Class
    

    Demo

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim operations As New SqlServerOperations With
                {
                    .FolderName = My.Settings.BackupFolder,
                    .ConnectionString = My.Settings.ConnectionString
                }
    
    
            If operations.Backup() Then
                MessageBox.Show($"Created {operations.FileName}")
            Else
                If operations.HasError Then
                    MessageBox.Show($"Failed: {operations.Exception}")
                Else
                    MessageBox.Show("Failed to create backup")
                End If
            End If
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    poniedziałek, 7 marca 2016 10:56
  • Okay I did research on this topic and found that the backup process works (as mentioned before) on databases that are attached to your SQL-Server and not a physically un-attached database such as in your connection string e.g.

    TestDataBase.mdf

    And set via AttachDbFilename in the connection string.

    My database is attached to the server which is why the backup works unlike an attached database

    Data Source=KARENS-PC;Initial Catalog=CustomerDatabase;Integrated Security=True


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Oznaczony jako odpowiedź przez 1JRoberts środa, 9 marca 2016 03:37
    poniedziałek, 7 marca 2016 14:33
  • https://visualstudiomagazine.com/Blogs/Tool-Tracker/2012/05/Dealing-with-Local-Databases-or-Why-Your-Updates-Dont-Stick.aspx

    This link deals with something else and to see more

    https://msdn.microsoft.com/en-us/library/ms246989.aspx?f=255&MSPPError=-2147217396

    https://code.msdn.microsoft.com/Working-with-Copy-to-11fbc251


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    poniedziałek, 7 marca 2016 14:35
  • I installed SQL Server 2014 Express and Management Studio, and now I can  backup the Database thru my application. After working with Management Studio, however, I discovered I could just use that to backup the Database, instead of through the application. However, I worked hard on the code so I'm using it.  :)

    One problem that I did run into, that might help someone else that might come across this, is that the backup file referenced in the Query string

    Dim stgBackupQuery As String = "BACKUP DATABASE TestDataBase TO DISK='C:\TestDataBase.bak'"

    needs to already exist, or you will get an error. So you will need to backup the DB first in Management Studio, and move the backup file into the desired location. (I also tried just renaming a text file to "TestDataBase.bak" and then letting BACKUP overwrite the file, and the seemed to work also.) Since the file (name) needs to already exist, it appears that concatenating a date onto the backup file's name won't work. Unfortunate, but I can live with that.

    Thanks again for your help.

    Much appreciated.


    środa, 9 marca 2016 04:45
  • Karen,

    I just stumbled on this as a possible solution to what I am trying to accomplish.  I am seeking a backup to my existing database in VS express.  I need this backup so that I can restore on my remote server to initialize the DB.  I have installed SSMS and figured out how to connect to the engine, and I thought it would be simple from here on, but its not for me.  Would appreciate your input.

    niedziela, 27 września 2020 16:18
  • Karen,

    I just stumbled on this as a possible solution to what I am trying to accomplish.  I am seeking a backup to my existing database in VS express.  I need this backup so that I can restore on my remote server to initialize the DB.  I have installed SSMS and figured out how to connect to the engine, and I thought it would be simple from here on, but its not for me.  Would appreciate your input.

    Generally one backs up via SSMS as per below, once the dialog appears click OK.

    Code wise there is SMO for full editions of SQL-Server, unsure if it will work with SQLEXPRESS (I use the full edition of SQL-Server) as per Microsoft docs.

    See also my Microsoft TechNet article on SMO (with source). The only thing I really do with SMO is copy an existing database with tables, data, indices etc.

    In closing

    • SMO if a consideration will take time and may be frustrating at first.
    • SMO is dependent on the version of SSMS so if you can SSMS edition and uninstall a earlier version this will break code but if you leave the earlier version zero issues. I have two versions of SSMS installed.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    niedziela, 27 września 2020 16:51
  • Thank you Karen, but a few of the statements are too advanced for me.  "using the data source window in the ide, select a database, select a table in the add new data source".  The configuration statements go where?  I'm committed to getting this done in this way but can you just tell me if the codeless way that I used 4 years ago, went away with Windows 10.  It seems that I was able to get a satisfactory backup on windows 7 back then but I cant replicate that now.

    Thanks again.

    poniedziałek, 28 września 2020 19:47