Backing up DataBase using VB.net

All replies

• 

  

  2

  Sign in to vote

  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
  

  
  

  • Edited by KareninstructorMVP Sunday, March 6, 2016 11:37 AM

  Sunday, March 6, 2016 11:35 AM
• 

  

  0

  Sign in to vote

  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

  Monday, March 7, 2016 2:55 AM
• 

  

  0

  Sign in to vote

  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
  

  • Proposed as answer by elisch123 Sunday, September 27, 2020 4:48 PM

  Monday, March 7, 2016 3:17 AM
• 

  

  0

  Sign in to vote

  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.

  Monday, March 7, 2016 4:30 AM
• 

  

  0

  Sign in to vote

  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
  

  Monday, March 7, 2016 10:05 AM
• 

  

  0

  Sign in to vote

  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

  Monday, March 7, 2016 10:38 AM
• 

  

  1

  Sign in to vote

  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
  

  Monday, March 7, 2016 10:56 AM
• 

  

  0

  Sign in to vote

  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
  

  • Marked as answer by 1JRoberts Wednesday, March 9, 2016 3:37 AM

  Monday, March 7, 2016 2:33 PM
• 

  

  0

  Sign in to vote

  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
  

  Monday, March 7, 2016 2:35 PM
• 

  

  0

  Sign in to vote

  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.
  

  
  

  Wednesday, March 9, 2016 4:45 AM
• 

  

  0

  Sign in to vote

  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.

  Sunday, September 27, 2020 4:18 PM
• 

  

  0

  Sign in to vote

  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
  

  Sunday, September 27, 2020 4:51 PM
• 

  

  0

  Sign in to vote

  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.

  Monday, September 28, 2020 7:47 PM